☀
How to find AWS breakup cost by Resource IDs

I was having an issue seeing the billing for CloudFront distributions as per staging and production environments. We need to have terraform and steampipe ready for this.
A similar question is posted on StackOverflow, but no satisfactory answer was given. Because I am also having the same problem, I discovered the following best way to achieve the billing for individual resource IDs, hence, thought to post the detailed answer here. Basically, you need:

1. AWS CLI installed and working, my version aws-cli/2.7.31
2. Terraform version minimum v0.15.5
3. Steampipie version 0.16.3
Generate the custom billing report for an hour, day, or month. It's up to your requirements for how long period you need the data. 
The following 3 steps are in nutshell to perform:
1. Generate the custom billing report using terraform
2. Use AWS CLI to sync and download the files, extract the files 
3. Query the CSV using the steampipe tool to calculate the cost of resource 
// Definition of custom report
resource "aws_cur_report_definition" "cust_report" {
  depends_on = [
    aws_s3_bucket_policy.cust_report
  ]
  report_name                = "custom-cost-report-${var.account_name}"
  time_unit                  = "DAILY"
  format                     = "textORcsv"
  compression                = "GZIP"
  additional_schema_elements = ["RESOURCES"]
  s3_bucket                  = "custom-cost-report-${var.account_name}"
  s3_region                  = "us-east-1"
}
​
// S3 bucket where reports should be saved
resource "aws_s3_bucket" "cust_report" {
  bucket        = "custom-cost-report-${var.account_name}"
  force_destroy = true
​
  tags = {
    Name = "Custom billing report ${var.account_name}"
  }
}
​
// Policy for s3 access from billing.amazonaws.com
resource "aws_s3_bucket_policy" "cust_report" {
  bucket = aws_s3_bucket.cust_report.id
  policy = <<POLICY
  {
    "Version": "2008-10-17",
    "Statement": [
      {
        "Effect": "Allow",
        "Principal": {
          "Service": "billingreports.amazonaws.com"
        },
        "Action": [
          "s3:GetBucketAcl",
          "s3:GetBucketPolicy"
        ],
        "Resource": "arn:aws:s3:::custom-cost-report-${var.account_name}",
        "Condition": {
          "StringEquals": {
            "aws:SourceArn": "arn:aws:cur:us-east-1:${var.account_id}:definition/*",
            "aws:SourceAccount": "${var.account_id}"
          }
        }
      },
      {
        "Effect": "Allow",
        "Principal": {
          "Service": "billingreports.amazonaws.com"
        },
        "Action": [
          "s3:PutObject"
        ],
        "Resource": "arn:aws:s3:::custom-cost-report-${var.account_name}/*",
        "Condition": {
          "StringEquals": {
            "aws:SourceArn": "arn:aws:cur:us-east-1:${var.account_id}:definition/*",
            "aws:SourceAccount": "${var.account_id}"
          }
        }
      }
    ]
  }
  POLICY
}
​
// VARIABLES
variable "account_name" {}
variable "time_unit" {
  default = "DAILY"
}
variable "account_id" {}
​
We have many AWS accounts, hence, we're using the above code as a module and importing it wherever required. For example:
// Dev Account ID
data "aws_caller_identity" "current" {
  provider = aws.dev
}
​
module "custom_billing_report" {
  providers = {
    aws = aws.dev
  }
  source       = "../../modules/cust_billing/"
  account_name = "Dev"
  time_unit    = "DAILY"
  account_id   = data.aws_caller_identity.current.account_id
}
Now download the files from the s3 bucket when they're generated. You can either download them manually or using aws CLI:
aws --profile dev s3 sync s3://custom-cost-report-dev ~/aws/dev
Where dev is my aws account name and I configured it with the same name in my aws config ~/.aws/config
This command will download all the content from the s3 bucket, please explore it and extract the gzip files. You will observe the CSV files in heavy sizes. Move the file to the folder where your steampipe is configured to read, for example, my ~/.steampipe/config/csv.spc has the path:
paths = [ "/Users/XXXXXXX/Documents/csv_files/*.csv" ]
Now let's say you want to check the cost of CloudFront distribution ID E3BSIPQXFI9JW5 in your AWS account, you can make the query like this:
select sum("lineItem/NetUnblendedCost"::float) as cf_stg_cost from csv_billing_dev where "lineItem/ResourceId" = 'arn:aws:cloudfront::239477345596:distribution/E3BSIPQXFI9JW5'
​
+---------------+
| cf_stg_cost   |
+---------------+
| 176.454618147 |
+---------------+
I hope this will be helpful. 
Tajinder Wiki Space
Last modified 5mo ago