Tip #36: AWS cost monitoring: best AWS CUR queries

The AWS cost and usage reports feed in any given AWS account includes only the columns relevant for its current AWS architecture. Thus, if you do not use, for example, Reserved Instances, all columns relevant for Reserved Instances will not be included.

The actual CUR table name, aws_billing_cost_cut_team in the below examples, depends on your particular CUR set up.

The primary uses of AWS cost and usage reports are AWS cost monitoring and cost optimization, so we included the top 5 cost optimization queries.

#1 – Query for net unblended costs and net amortized costs

If you do not use Reserved Instances or Savings Plans, the associated columns will be missing from your CUR feed, and in that scenario your net unblended costs and net amortized costs are identical.

SELECT
      DATE_FORMAT((bill_billing_period_start_date),'%Y-%m-01') AS month_bill_billing_period_start_date, 
      round(SUM(COALESCE(line_item_net_unblended_cost, line_item_unblended_cost)),2) AS sum_line_item_net_unblended_cost,
      round(SUM(CASE
        WHEN (line_item_line_item_type = 'SavingsPlanCoveredUsage') THEN COALESCE(savings_plan_net_savings_plan_effective_cost,savings_plan_savings_plan_effective_cost)
        WHEN (line_item_line_item_type = 'SavingsPlanRecurringFee') THEN (savings_plan_total_commitment_to_date - savings_plan_used_commitment) * (1-discount_total_discount/line_item_unblended_cost)
        WHEN (line_item_line_item_type = 'SavingsPlanNegation') THEN 0
        WHEN (line_item_line_item_type = 'SavingsPlanUpfrontFee') THEN 0
        WHEN (line_item_line_item_type = 'DiscountedUsage') THEN COALESCE(reservation_net_effective_cost,reservation_effective_cost)
        WHEN (line_item_line_item_type = 'RIFee') THEN COALESCE(reservation_net_unused_amortized_upfront_fee_for_billing_period,reservation_unused_amortized_upfront_fee_for_billing_period) + COALESCE(reservation_net_unused_recurring_fee, reservation_unused_recurring_fee)
        WHEN ((line_item_line_item_type = 'Fee') AND (reservation_reservation_a_r_n <> '')) THEN 0
        ELSE COALESCE(line_item_net_unblended_cost,line_item_unblended_cost)
      END),2) AS amortized_cost
FROM "aws_cur".aws_billing_cost_cut_team
WHERE date_format (bill_billing_period_start_date, '%Y-%m' ) > date_format( date_add('month', -24, current_date), '%Y-%m')
GROUP BY 1 ORDER BY 1 desc;

#2 – Query for Top 10 AWS products in terms of aggregate costs

You can add a WHERE clause to restrict the time window.

SELECT "line_item_product_code", round(sum("line_item_unblended_cost"),2) as cost 
FROM "aws_cur".aws_billing_cost_cut_team
GROUP BY "line_item_product_code"
ORDER BY cost desc
LIMIT 10;

#3 – Details of active EC2 RIs

Change the time window as it fits your purpose.

SELECT  bill_billing_period_start_date, 
        product_region, 
        line_item_usage_type, 
        pricing_lease_contract_length, 
        line_item_line_item_description, 
        Reservation_units_per_reservation, 
        reservation_number_of_reservations,   
        pricing_offering_class,
        pricing_purchase_option,
        reservation_start_time,
        reservation_end_time,
        reservation_recurring_fee_for_usage,
        reservation_reservation_a_r_n, 
        reservation_subscription_id, 
        reservation_unused_quantity, 
        reservation_unused_recurring_fee 
FROM "aws_cur".aws_billing_cost_cut_team
where product_region='us-east-1' and line_item_product_code='AmazonEC2' and  length(reservation_reservation_a_r_n) > 0 and line_item_line_item_type='RIFee' and bill_billing_period_start_date=CAST('2021-09-01' as DATE) and  bill_billing_period_end_date=CAST('2021-10-01' as DATE)

#4 – Details of EC2 reservation hours purchased, amortized upfront fee, reservation utilization and reservation hours unused for a particular RI subscription ID

Change the time window and the reservation subscription ID as it fits your purpose.

SELECT  reservation_subscription_id, 
        reservation_reservation_a_r_n, 
        reservation_units_per_reservation, 
        reservation_number_of_reservations as "Number of RIs", 
        reservation_total_reserved_units as "Reservation Hours Purchased", 
        reservation_amortized_upfront_cost_for_usage as "Amortized Upfront Fee", 
        round(100 * (1 - (reservation_unused_quantity / cast(reservation_total_reserved_units as double)))) as "Reservation Utilization", 
        reservation_unused_quantity as "Reservation Hours Unused"
FROM "aws_cur".aws_billing_cost_cut_team
where product_region='us-east-1' and bill_billing_period_start_date=CAST('2021-09-01' as DATE) and  bill_billing_period_end_date=CAST('2021-10-01' as DATE) and reservation_subscription_id = '69136XXXXX' and line_item_line_item_type='RIFee'

#5 – EC2 spot usage hours and cost per account ID and instance type

select "line_item_usage_account_id", "line_item_usage_type", count(*) as hours, sum ("line_item_blended_cost") as cost
FROM "aws_cur".aws_billing_cost_cut_team
where "line_item_usage_type" like '%Spot%'
group by "line_item_usage_account_id", "line_item_usage_type"
order by "line_item_usage_account_id" asc, "line_item_usage_type" desc