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