These are queries for AWS Services under the Compute product family .
Use the clipboard in the top right of the text boxes below to copy all of the text to be pasted.
You may need to change variables used as placeholders in your query. ${table_Name} is a common variable which needs to be replaced. Example: cur_db.cur_table
This query will display the top costs for all spend with the product code of ‘AmazonEC2’. This will include all pricing categories (i.e. OnDemand, Reserved etc..) as well as charges for storage on EC2 (i.e. gp2). The query will output the product code as well as the product description to provide context. It is ordered by largest to smallest spend.
Please refer to the EC2 pricing page .
These links are provided as an example to compare CUR report output to Cost Explorer output.
Unblended Cost Link
SELECT line_item_product_code,
line_item_line_item_description,
round(sum(line_item_unblended_cost),2) as sum_line_item_unblended_cost
FROM ${table_name}
WHERE
year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
AND line_item_product_code like '%AmazonEC2%'
AND line_item_line_item_type NOT IN ('Tax','Refund')
AND line_item_product_code like '%AmazonEC2%'
GROUP BY line_item_product_code,
line_item_line_item_description
ORDER BY sum_line_item_unblended_cost desc
This query will provide the EC2 usage quantity measured in hours for each purchase option and each instance type. The output will include detailed information about the instance type, amortized cost, purchase option, and usage quantity. The output will be ordered by usage quantity in descending order.
Please refer to the EC2 pricing page .
SELECT
year,
month,
bill_billing_period_start_date,
product_instance_type,
date_trunc('hour', line_item_usage_start_date) as hour_line_item_usage_start_date,
bill_payer_account_id,
line_item_usage_account_id,
(CASE
WHEN (savings_plan_savings_plan_a_r_n <> '') THEN
'SavingsPlan'
WHEN (reservation_reservation_a_r_n <> '') THEN
'Reserved'
WHEN (line_item_usage_type LIKE '%Spot%') THEN
'Spot'
ELSE 'OnDemand' END) as purchase_option,
sum(CASE
WHEN line_item_line_item_type = 'SavingsPlanCoveredUsage' THEN
savings_plan_savings_plan_effective_cost
WHEN line_item_line_item_type = 'DiscountedUsage' THEN
reservation_effective_cost
WHEN line_item_line_item_type = 'Usage' THEN
line_item_unblended_cost
ELSE 0 END) as amortized_cost,
round(sum(line_item_usage_amount), 2) usage_quantity
FROM ${table_name}
WHERE
year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
AND ( (line_item_product_code = 'AmazonEC2')
AND (product_servicecode <> 'AWSDataTransfer')
AND (line_item_operation LIKE '%RunInstances%')
AND (line_item_usage_type NOT LIKE '%DataXfer%')
)
AND (
(line_item_line_item_type = 'Usage')
OR (line_item_line_item_type = 'SavingsPlanCoveredUsage')
OR (line_item_line_item_type = 'DiscountedUsage')
)
GROUP BY
year,
month,
bill_billing_period_start_date,
product_instance_type,
date_trunc('hour', line_item_usage_start_date),
bill_payer_account_id,
7,
8
ORDER BY
usage_quantity DESC
This query will provide EC2 consumption of Savings Plans across Compute resources by linked accounts. It also provides you with the savings received from these Savings Plans and which Savings Plans its connected to. The output is ordered by date.
Please refer to the EC2 pricing page .
SELECT
bill_payer_account_id,
line_item_usage_account_id,
DATE_FORMAT(("line_item_usage_start_date"),'%Y-%m-%d') AS day_line_item_usage_start_date,
SPLIT_PART(savings_plan_savings_plan_a_r_n, '/', 2) AS savings_plan_savings_plan_a_r_n,
CASE
savings_plan_offering_type
WHEN 'EC2InstanceSavingsPlans' THEN 'EC2 Instance Savings Plans'
WHEN 'ComputeSavingsPlans' THEN 'Compute Savings Plans'
ELSE savings_plan_offering_type
END AS "Type",
savings_plan_region,
CASE
WHEN product_product_name = 'Amazon EC2 Container Service' THEN 'Fargate'
WHEN product_product_name = 'AWS Lambda' THEN 'Lambda'
ELSE product_instance_type_family
END AS "Instance Type Family",
SUM (TRY_CAST(line_item_unblended_cost as decimal(16, 8))) as "On Demand Cost",
SUM(TRY_CAST(savings_plan_savings_plan_effective_cost AS decimal(16, 8))) as "Effective Cost",
SUM(CAST(line_item_unblended_cost AS decimal(16,8))) AS sum_line_item_unblended_cost,
savings_plan_end_time
FROM
${table_name}
WHERE
year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
AND savings_plan_savings_plan_a_r_n <> ''
AND line_item_line_item_type = 'SavingsPlanCoveredUsage'
GROUP by
bill_payer_account_id,
line_item_usage_account_id,
DATE_FORMAT(("line_item_usage_start_date"),'%Y-%m-%d'),
savings_plan_savings_plan_a_r_n,
savings_plan_offering_type,
savings_plan_region,
product_instance_type_family,
product_product_name,
savings_plan_end_time
ORDER BY
day_line_item_usage_start_date;
This query will provide details about Compute usage that is covered by Savings Plans. The output will include detailed information about the usage type, usage amount, Savings Plans ARN, line item description, and Savings Plans effective savings as compared to On-Demand pricing. The public pricing on-demand cost will be summed and in descending order.
Please refer to the Savings Plans pricing page .
SELECT
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) 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 ${table_name}
WHERE
year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
AND line_item_line_item_type LIKE 'SavingsPlanCoveredUsage'
GROUP BY
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
This query focuses on surfacing accounts which have utilized AWS Savings Plans for which they are not a buyer.
Please refer to the Savings Plans pricing page .
SELECT year,
month,
bill_payer_account_id,
line_item_usage_account_id,
savings_plan_offering_type,
line_item_resource_id,
SUM(CAST(line_item_unblended_cost AS decimal(16, 8))) AS sum_line_item_unblended_cost,
SUM(CAST(savings_plan_savings_plan_effective_cost AS decimal(16, 8))) AS sum_savings_plan_savings_plan_effective_cost
FROM ${table_name}
WHERE
year = '2020'
AND (month BETWEEN '9' AND '12' OR month BETWEEN '09' AND '12')
AND (bill_payer_account_id = '111122223333'
AND line_item_usage_account_id = '444455556666'
AND line_item_line_item_type = 'SavingsPlanCoveredUsage'
AND savings_plan_savings_plan_a_r_n NOT LIKE '%444455556666%')
GROUP BY
year,
month,
line_item_resource_id,
line_item_usage_account_id,
bill_payer_account_id,
savings_plan_offering_type
ORDER BY sum_savings_plan_savings_plan_effective_cost DESC;
This query focuses on Lambda and the breakdown of its costs by different usage element. Split by Resource IDs you can view the usage, unblended costs and amortized cost broken down by different pricing plans. These results will be ordered by date and costs.
Please refer to the Lambda pricing page .
SELECT *
FROM
(
(
SELECT
bill_payer_account_id,
line_item_usage_account_id,
line_item_line_item_type,
DATE_FORMAT((line_item_usage_start_date),'%Y-%m-%d') AS day_line_item_usage_start_date,
product_region,
CASE
WHEN line_item_usage_type LIKE '%%Lambda-Edge-GB-Second%%' THEN 'Lambda EDGE GB x Sec.'
WHEN line_item_usage_type LIKE '%%Lambda-Edge-Request%%' THEN 'Lambda EDGE Requests'
WHEN line_item_usage_type LIKE '%%Lambda-GB-Second%%' THEN 'Lambda GB x Sec.'
WHEN line_item_usage_type LIKE '%%Request%%' THEN 'Lambda Requests'
WHEN line_item_usage_type LIKE '%%In-Bytes%%' THEN 'Data Transfer (IN)'
WHEN line_item_usage_type LIKE '%%Out-Bytes%%' THEN 'Data Transfer (Out)'
WHEN line_item_usage_type LIKE '%%Regional-Bytes%%' THEN 'Data Transfer (Regional)'
ELSE 'Other'
END as UsageType,
line_item_resource_id,
pricing_term,
SUM(CAST(line_item_usage_amount AS double)) AS sum_line_item_usage_amount,
SUM(CAST(line_item_unblended_cost AS decimal(16,8))) AS sum_line_item_unblended_cost,
sum(CASE
WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') THEN
"savings_plan_savings_plan_effective_cost"
WHEN ("line_item_line_item_type" = 'SavingsPlanRecurringFee') THEN
("savings_plan_total_commitment_to_date" - "savings_plan_used_commitment")
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
"reservation_effective_cost"
WHEN ("line_item_line_item_type" = 'RIFee') THEN
("reservation_unused_amortized_upfront_fee_for_billing_period" + "reservation_unused_recurring_fee")
WHEN (("line_item_line_item_type" = 'Fee')
AND ("reservation_reservation_a_r_n" <> '')) THEN
0
ELSE "line_item_unblended_cost" END) "amortized_cost"
FROM ${table_name}
WHERE year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
AND product_product_name = 'AWS Lambda'
AND line_item_line_item_type like '%%Usage%%'
AND product_product_family IN ('Data Transfer', 'Serverless')
AND line_item_line_item_type NOT IN ('Tax','Credit','Refund','EdpDiscount','Fee','RIFee')
GROUP BY
bill_payer_account_id,
line_item_usage_account_id,
DATE_FORMAT((line_item_usage_start_date),'%Y-%m-%d'),
product_region,
line_item_usage_type,
line_item_resource_id,
pricing_term,
line_item_line_item_type
ORDER BY
day_line_item_usage_start_date,
sum_line_item_usage_amount,
sum_line_item_unblended_cost
)
UNION
(
SELECT
bill_payer_account_id,
line_item_usage_account_id,
line_item_line_item_type,
DATE_FORMAT((line_item_usage_start_date),'%Y-%m-%d') AS day_line_item_usage_start_date,
product_region AS Region,
CASE
WHEN line_item_usage_type LIKE '%%Lambda-Edge-GB-Second%%' THEN 'Lambda EDGE GB x Sec.'
WHEN line_item_usage_type LIKE '%%Lambda-Edge-Request%%' THEN 'Lambda EDGE Requests'
WHEN line_item_usage_type LIKE '%%Lambda-GB-Second%%' THEN 'Lambda GB x Sec.'
WHEN line_item_usage_type LIKE '%%Request%%' THEN 'Lambda Requests'
WHEN line_item_usage_type LIKE '%%In-Bytes%%' THEN 'Data Transfer (IN)'
WHEN line_item_usage_type LIKE '%%Out-Bytes%%' THEN 'Data Transfer (Out)'
WHEN line_item_usage_type LIKE '%%Regional-Bytes%%' THEN 'Data Transfer (Regional)'
ELSE 'Other'
END as UsageType,
line_item_resource_id,
CASE savings_plan_offering_type
WHEN 'ComputeSavingsPlans' THEN 'Compute Savings Plans'
ELSE savings_plan_offering_type
END AS ChargeType,
SUM(CAST(line_item_usage_amount AS double)) AS sum_line_item_usage_amount,
SUM(CAST(savings_plan_savings_plan_effective_cost AS decimal(16,8))) AS sum_savings_plan_savings_plan_effective_cost,
sum(CASE
WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') THEN
"savings_plan_savings_plan_effective_cost"
WHEN ("line_item_line_item_type" = 'SavingsPlanRecurringFee') THEN
("savings_plan_total_commitment_to_date" - "savings_plan_used_commitment")
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
"reservation_effective_cost"
WHEN ("line_item_line_item_type" = 'RIFee') THEN
("reservation_unused_amortized_upfront_fee_for_billing_period" + "reservation_unused_recurring_fee")
WHEN (("line_item_line_item_type" = 'Fee')
AND ("reservation_reservation_a_r_n" <> '')) THEN
0
ELSE "line_item_unblended_cost" END) "amortized_cost"
FROM ${table_name}
WHERE year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
AND product_product_name = 'AWS Lambda'
AND product_product_family IN ('Data Transfer', 'Serverless')
AND line_item_line_item_type NOT IN ('Tax','Credit','Refund','EdpDiscount','Fee','RIFee')
GROUP BY
bill_payer_account_id,
line_item_usage_account_id,
DATE_FORMAT((line_item_usage_start_date),'%Y-%m-%d'),
product_region,
line_item_usage_type,
line_item_resource_id,
savings_plan_offering_type,
line_item_line_item_type
ORDER BY
day_line_item_usage_start_date ASC,
sum_line_item_usage_amount DESC
)
) AS aggregatedTable
ORDER BY
day_line_item_usage_start_date,
sum_line_item_usage_amount,
sum_line_item_unblended_cost;
This query will display cost and usage of Elastic Load Balancers which didn’t receive any traffic last month and ran for more than 336 hours (14 days). Resources returned by this query could be considered for deletion.
Please refer to the Elastic Load Balancing pricing page .
SELECT
bill_payer_account_id,
line_item_usage_account_id,
SPLIT_PART(line_item_resource_id, ':', 6) split_line_item_resource_id,
product_region,
pricing_unit,
sum_line_item_usage_amount,
CAST(cost_per_resource AS decimal(16, 8)) AS "sum_line_item_unblended_cost"
FROM
(
SELECT
line_item_resource_id,
product_region,
pricing_unit,
line_item_usage_account_id,
bill_payer_account_id,
SUM(line_item_usage_amount) AS sum_line_item_usage_amount,
SUM(SUM(line_item_unblended_cost)) OVER (PARTITION BY line_item_resource_id) AS cost_per_resource,
SUM(SUM(line_item_usage_amount)) OVER (PARTITION BY line_item_resource_id, pricing_unit) AS usage_per_resource_and_pricing_unit,
COUNT(pricing_unit) OVER (PARTITION BY line_item_resource_id) AS pricing_unit_per_resource
FROM
${table_name}
WHERE
line_item_product_code = 'AWSELB'
-- get previous month
AND month = cast(month(current_timestamp + -1 * interval '1' MONTH) AS VARCHAR)
-- get year for previous month
AND year = cast(year(current_timestamp + -1 * interval '1' MONTH) AS VARCHAR)
AND line_item_line_item_type = 'Usage'
GROUP BY
line_item_resource_id,
product_region,
pricing_unit,
line_item_usage_account_id,
bill_payer_account_id
)
WHERE
-- filter only resources which ran more than half month (336 hrs)
usage_per_resource_and_pricing_unit > 336
AND pricing_unit_per_resource = 1
ORDER BY
cost_per_resource DESC
CUR queries are provided as is. We recommend validating your data by comparing it against your monthly bill and Cost Explorer prior to making any financial decisions. If you wish to provide feedback on these queries, there is an error, or you want to make a suggestion, please email: curquery@amazon.com