AWS Cost Management is a service that collects usage cost metrics from AWS services. You can use the AWS Cost Explorer to filter and group these metrics. But what can you do if you want to dig deeper into these AWS Cost and Usage Reports (CUR)?
Analyze AWS Cost and Usage Reports with AWS Athena
AWS Athena is a fully managed service that allows you to query S3 objects using standard SQL. With the steps below, we will create an AWS CloudFormation stack to automatically export the CUR to S3.
Instructions
We will split the instructions into two parts, since we need to allow some time for data collection before we can complete the setup.
Instructions – part one
- Sign in to the AWS Console
- Open the Billing Management Console, and select Cost & Usage Reports
- Click on Create Report
- Insert a meaningful name, and check the option to “Include resource IDs”
- Click on Configure to select the target S3 bucket. Typically, it’s better to create a new dedicated bucket. If you use existing buckets, make sure they belong to the same account where the CUR is created.
- Confirm to apply the default policy to this bucket. This policy simply makes sure the bucket meets the security requirements.
- Enter the path prefix, and check to enable report data integration for Amazon Athena.
Tip: make note of the path prefix in a text document. We will need it later.
- Review all the settings and select Review and Complete.
- Allow up to 24 hours for data collection. We will continue with part 2 as soon as there is some data in the S3 folder we created.
Instructions – part 2
- Open the S3 console and download the CloudFormation template file called crawler-cfn.yml. You can find it in the bucket you selected for the cost report, in the following path:
You’ll need to replace the path-prefix and report-name with the path prefix and report name you entered when creating the cost report. The file should have this content in the first rows:path-prefix/report-name/crawler-cfn.ymlAWSTemplateFormatVersion: 2010-09-09
Resources:
AWSCURDatabase:
Type: 'AWS::Glue::Database'
Properties:
DatabaseInput:
Name: 'athenacurcfn_aws_made_easy'
CatalogId: !Ref AWS::AccountId - Open the CloudFormation console
- Click on Create new Stack if this is the first time using CloudFormation. Otherwise, click on Create Stack. Select the option With new resources.
- Select the option Template is ready and Upload a template file.
- Enter the stack name and click Next.
- Enter any tags and click Next
- Scroll down and acknowledge that AWS CloudFormation might create IAM resources.
- Allow a couple of minutes for the stack to be created
- Open the Athena console
- Select the database matching the AWSCURDatabase.Name property in the template file
- Enter the following query in the query editor and press Run
The Results should have one entry:select status from cost_and_usage_data_status
If the result is UPDATING, allow some time for the update to complete. Athena might return incomplete results if the status isn’t READY.
- As an example, enter the following query in the editor and press Run
select year, month, line_item_product_code,
sum(line_item_unblended_cost) from
cloudfix_cookbok_cost_and_usage_report
group by year, month, line_item_product_code
- It will list your AWS unblended costs for all AWS services grouped by month
References
- Carlson, E. (2020, June 16). Understanding your AWS Cost Datasets: A Cheat Sheet. Amazon Web Services. Retrieved March 30, 2022, from https://aws.amazon.com/blogs/aws-cloud-financial-management/understanding-your-aws-cost-datasets-a-cheat-sheet/