AWS Athena Query to get EC2 instance Price from CUR

Question

I'm trying to get the EC2 instance price - for a specific EC2 instance - using Amazon Athena + CUR

The pipeline is already setup to run the SQL queries against CUR, but I'm having hardtime running a query which returns the actual cost for a specific EC2 instance - considering if it's on-demand/using savingPlans/ etc.

I'm looking at the Data Dictionary doc, but that's not necessarily mapped to all the column names I retrieved from CUR/Athena query.

Specifically I'm having a hard time to map the result to a unique ID (e.g. EC2 instance ID, any tags, etc.) of the running instance. (Found some examples here, but not for individual EC2 instances)

Any help on how to achieve this is greatly appreciated

Answer 1

Answering my question, this is the query I came up with:

  line_item_resource_id,
  bill_payer_account_id,
  bill_billing_period_start_date,
  line_item_usage_account_id,
  DATE_FORMAT(line_item_usage_start_date,'%Y-%m') AS month_line_item_usage_start_date, 
  savings_plan_savings_plan_a_r_n,
  line_item_product_code,
  line_item_usage_type,
  SUM(line_item_usage_amount) AS sum_line_item_usage_amount,
  line_item_line_item_description,
  pricing_public_on_demand_rate,
  SUM(pricing_public_on_demand_cost) AS sum_pricing_public_on_demand_cost,
  savings_plan_savings_plan_rate,
  SUM(savings_plan_savings_plan_effective_cost) AS sum_savings_plan_savings_plan_effective_cost
FROM 
  "<CUR_DB>"
WHERE 
  year='<YEAR>' AND month='<MONTH>' 
  AND line_item_resource_id='<Instance-Id>'
  AND line_item_line_item_type LIKE 'SavingsPlanCoveredUsage'
GROUP BY 
  line_item_resource_id,
  bill_payer_account_id, 
  bill_billing_period_start_date, 
  line_item_usage_account_id, 
  DATE_FORMAT(line_item_usage_start_date,'%Y-%m'), 
  savings_plan_savings_plan_a_r_n, 
  line_item_product_code, 
  line_item_usage_type, 
  line_item_unblended_rate, 
  line_item_line_item_description, 
  pricing_public_on_demand_rate, 
  savings_plan_savings_plan_rate
ORDER BY 
  sum_pricing_public_on_demand_cost DESC;