These are queries for AWS Services under the Security, Identity, & Compliance product family.
Use the clipboard in the top right of the text boxes below to copy all of the text to be pasted.
CUR Query Library uses placeholder variables, indicated by a dollar sign and curly braces (${ }). ${table_name} and ${date_filter} are common placeholder variables used throughout CUR Query Library, which must be replaced before a query will run. For example, if your CUR table is called cur_table and is in a database called cur_db, you would replace ${table_name} with cur_db.cur_table. For ${date_filter}, you have multiple options. See Filtering by Date in the CUR Query Library Help section for additional details.
This query provides daily unblended cost and usage information about Amazon GuardDuty Usage. The usage amount and cost will be summed.
Please refer to the Amazon GuardDuty pricing page for more details.
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,
line_item_usage_type,
TRIM(REPLACE(product_group, 'Security Services - Amazon GuardDuty ', '')) AS trim_product_group,
pricing_unit,
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
FROM
${tableName}
WHERE
${date_filter}
AND product_product_name = 'Amazon GuardDuty'
AND line_item_line_item_type IN ('DiscountedUsage', 'Usage', 'SavingsPlanCoveredUsage')
GROUP BY
bill_payer_account_id,
line_item_usage_account_id,
DATE_FORMAT((line_item_usage_start_date),'%Y-%m-%d'),
line_item_usage_type,
product_group,
pricing_unit
ORDER BY
day_line_item_usage_start_date,
sum_line_item_usage_amount,
sum_line_item_unblended_cost,
trim_product_group;
This query provides daily unblended cost and usage information about Amazon Cognito Usage. The usage amount and cost will be summed.
Please refer to the Amazon Cognito pricing page for more details.
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,
product_product_name,
line_item_operation,
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
FROM
${tableName}
WHERE
${date_filter}
AND product_product_name = 'Amazon Cognito'
AND line_item_line_item_type IN ('DiscountedUsage', 'Usage', 'SavingsPlanCoveredUsage')
GROUP BY
bill_payer_account_id,
line_item_usage_account_id,
DATE_FORMAT((line_item_usage_start_date),'%Y-%m-%d'),
product_product_name,
line_item_operation
ORDER BY
day_line_item_usage_start_date,
sum_line_item_usage_amount,
sum_line_item_unblended_cost,
line_item_operation;
AWS Shield Advanced is a paid service that provides additional protections for internet-facing applications. See AWS Shield Pricing Page for details. AWS Shield Advanced charges a monthly fee, plus a usage fee based on data transfer out. The result generated by the query below will contain the resource’s ID and protected resource type. This information enables customers to identify the primary account and resource responsible for the majority of their AWS Shield cost.
Please refer to the AWS Shield pricing page for more details.
SELECT
bill_payer_account_id,
line_item_usage_account_id,
line_item_resource_id,
line_item_operation,
SUM(CAST(line_item_unblended_cost AS DECIMAL(16,8))) AS sum_line_item_unblended_cost
FROM
${table_name}
WHERE
line_item_product_code = 'AWSShield'
AND product_product_family IN ('Shield-DataTransfer', 'Shield Advanced Monthly Fee')
AND line_item_line_item_type IN ('DiscountedUsage', 'Usage', 'SavingsPlanCoveredUsage')
${date_filter}
GROUP BY
bill_payer_account_id,
line_item_usage_account_id,
line_item_resource_id,
line_item_operation
ORDER BY
sum_line_item_unblended_cost DESC,
line_item_resource_id
;
This query provides daily unblended cost and usage information about AWS WAF Usage including web acl, rule id, and region. The usage amount and cost will be summed and the cost will be in descending order.
Please refer to the WAF pricing page for more details.
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(SPLIT_PART(line_item_resource_id,'/',2),'+',1) AS split_webaclid_line_item_resource_id,
SPLIT_PART(SPLIT_PART(line_item_resource_id,'/',2),'+',2) AS split_ruleid_line_item_resource_id,
line_item_usage_type,
product_group,
product_group_description,
product_location,
product_location_type,
line_item_line_item_description,
pricing_unit,
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
FROM
${tableName}
WHERE
${date_filter}
AND product_product_name = 'AWS WAF'
AND line_item_line_item_type IN ('DiscountedUsage', 'Usage', 'SavingsPlanCoveredUsage')
GROUP BY
bill_payer_account_id,
line_item_usage_account_id,
DATE_FORMAT(line_item_usage_start_date,'%Y-%m-%d'),
line_item_resource_id,
line_item_usage_type,
product_group,
product_group_description,
product_location,
product_location_type,
line_item_line_item_description,
pricing_unit
ORDER BY
day_line_item_usage_start_date,
sum_line_item_usage_amount,
sum_line_item_unblended_cost,
product_group;
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