Global Queries

These are queries which return information about global usage.

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.

Table of Contents

Account

Query Description

This query will provide monthly unblended and amortized costs per linked account for all services. The query also includes ri_sp_trueup and ri_sip_upfront_fees columns to allow you to visualize the calculated difference between unblended and amortized costs. Unblended = Amortized + True-up + Upfront Fees, the +/- logic has already been included for you in the columns. We are showing in our example the exclusion of Route 53 Domains, as the monthly charges for these do not match between CUR and Cost Explorer. Finally we are excluding discounts, credits, refunds and taxes.

Pricing

Please refer to the AWS pricing page .

These links are provided as an example to compare CUR report output to Cost Explorer output.

Unblended Cost Link

Amortized Cost Link

Sample Output:

Images/spendaccount.png

Download SQL File:

Link to file

Copy Query

    SELECT
      bill_payer_account_id,
      line_item_usage_account_id,
      DATE_FORMAT((line_item_usage_start_date),'%Y-%m-01') AS month_line_item_usage_start_date, 
      SUM(CASE
        WHEN (line_item_line_item_type = 'SavingsPlanNegation') THEN 0 
        ELSE line_item_unblended_cost 
      END) 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) AS amortized_cost,
      SUM(CASE
        WHEN (line_item_line_item_type = 'SavingsPlanRecurringFee') THEN (-savings_plan_amortized_upfront_commitment_for_billing_period) 
        WHEN (line_item_line_item_type = 'RIFee') THEN (-reservation_amortized_upfront_fee_for_billing_period)
        WHEN (line_item_line_item_type = 'SavingsPlanNegation') THEN (-line_item_unblended_cost) 
        ELSE 0 
      END) AS ri_sp_trueup,
      SUM(CASE
        WHEN (line_item_line_item_type = 'SavingsPlanUpfrontFee') THEN line_item_unblended_cost
        WHEN ((line_item_line_item_type = 'Fee') AND (reservation_reservation_a_r_n <> '')) THEN line_item_unblended_cost 
        ELSE 0 
      END) AS ri_sp_upfront_fees
    FROM
      ${table_name}
    WHERE
      ${date_filter}
      AND line_item_usage_type != 'Route53-Domains'
      AND line_item_line_item_type  IN ('DiscountedUsage', 'Usage', 'SavingsPlanCoveredUsage')
    GROUP BY 
      bill_payer_account_id,
      line_item_usage_account_id,
      3
    ORDER BY
      month_line_item_usage_start_date ASC,
      sum_line_item_unblended_cost DESC;

Back to Table of Contents

Region

Query Description

This query will provide monthly unblended and amortized costs per linked account for all services by region where the service is operating. The query also includes ri_sp_trueup and ri_sip_upfront_fees columns to allow you to visualize the calculated difference between unblended and amortized costs. Unblended = Amortized + True-up + Upfront Fees, the +/- logic has already been included for you in the columns. We are showing in our example the exclusion of Route 53 Domains, as the monthly charges for these do not match between CUR and Cost Explorer. Finally we are excluding discounts, credits, refunds and taxes.

Pricing

Please refer to the AWS pricing page .

These links are provided as an example to compare CUR report output to Cost Explorer output.

Unblended Cost Link

Amortized Cost Link

Sample Output:

Images/spendregion.png

Download SQL File:

Link to file

Copy Query

    SELECT
      bill_payer_account_id,
      line_item_usage_account_id,
      DATE_FORMAT((line_item_usage_start_date),'%Y-%m-01') AS month_line_item_usage_start_date,
      CASE product_region
        WHEN NULL THEN 'Global'
        WHEN '' THEN 'Global'
        WHEN 'global' THEN 'Global'
        ELSE product_region
      END AS product_region,
      SUM(CASE
        WHEN (line_item_line_item_type = 'SavingsPlanNegation') THEN 0 
        ELSE line_item_unblended_cost 
      END) 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) AS amortized_cost,
      SUM(CASE
        WHEN (line_item_line_item_type = 'SavingsPlanRecurringFee') THEN (-savings_plan_amortized_upfront_commitment_for_billing_period) 
        WHEN (line_item_line_item_type = 'RIFee') THEN (-reservation_amortized_upfront_fee_for_billing_period)
        WHEN (line_item_line_item_type = 'SavingsPlanNegation') THEN (-line_item_unblended_cost ) 
        ELSE 0 
      END) AS ri_sp_trueup
      SUM(CASE
        WHEN (line_item_line_item_type = 'SavingsPlanUpfrontFee') THEN line_item_unblended_cost
        WHEN ((line_item_line_item_type = 'Fee') AND (reservation_reservation_a_r_n <> '')) THEN line_item_unblended_cost 
        ELSE 0 
      END) AS ri_sp_upfront_fees
    FROM
      ${table_name}
    WHERE
      ${date_filter}
      AND line_item_usage_type != 'Route53-Domains'
      AND line_item_line_item_type  IN ('DiscountedUsage', 'Usage', 'SavingsPlanCoveredUsage')
    GROUP BY
      bill_payer_account_id,
      line_item_usage_account_id,
      3,
      4;

Back to Table of Contents

Service

Query Description

This query will provide monthly unblended and amortized costs per linked account for all services by service. We have additionally broken out Data Transfer for each service. The query also includes ri_sp_trueup and ri_sip_upfront_fees columns to allow you to visualize the calculated difference between unblended and amortized costs. Unblended = Amortized + True-up + Upfront Fees, the +/- logic has already been included for you in the columns. We are showing in our example the exclusion of Route 53 Domains, as the monthly charges for these do not match between CUR and Cost Explorer. Finally we are excluding discounts, credits, refunds and taxes.

Pricing

Please refer to the AWS pricing page .

These links are provided as an example to compare CUR report output to Cost Explorer output.

Unblended Cost Link

Amortized Cost Link

Sample Output:

Images/spendservice.png

Download SQL File:

Link to file

Copy Query

    SELECT
      bill_payer_account_id,
      line_item_usage_account_id,
      DATE_FORMAT((line_item_usage_start_date),'%Y-%m-01') AS month_line_item_usage_start_date,
      CASE 
        WHEN (line_item_line_item_type = 'Usage' AND product_product_family = 'Data Transfer') THEN CONCAT('DataTransfer-',line_item_product_code) 
        ELSE line_item_product_code 
      END AS service_line_item_product_code,
      SUM(CASE
        WHEN (line_item_line_item_type = 'SavingsPlanNegation') THEN 0 
        ELSE line_item_unblended_cost 
      END) 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) AS amortized_cost,
      SUM(CASE
        WHEN (line_item_line_item_type = 'SavingsPlanRecurringFee') THEN (-savings_plan_amortized_upfront_commitment_for_billing_period) 
        WHEN (line_item_line_item_type = 'RIFee') THEN (-reservation_amortized_upfront_fee_for_billing_period)
        WHEN (line_item_line_item_type = 'SavingsPlanNegation') THEN (-line_item_unblended_cost) 
        ELSE 0 
      END) AS ri_sp_trueup,
      SUM(CASE
        WHEN (line_item_line_item_type = 'SavingsPlanUpfrontFee') THEN line_item_unblended_cost
        WHEN ((line_item_line_item_type = 'Fee') AND (reservation_reservation_a_r_n <> '')) THEN line_item_unblended_cost
        ELSE 0 
      END) AS ri_sp_upfront_fees
    FROM
      ${table_name}
    WHERE
      ${date_filter}
      AND line_item_usage_type != 'Route53-Domains' 
      AND line_item_line_item_type  IN ('DiscountedUsage', 'Usage', 'SavingsPlanCoveredUsage')
    GROUP BY
      bill_payer_account_id,
      line_item_usage_account_id,
      3,
      4
    ORDER BY
      month_line_item_usage_start_date ASC,
      sum_line_item_unblended_cost DESC;

Back to Table of Contents

Bill Details by Service

Query Description

This query will provide a monthly cost summary by AWS Service Charge which is an approximation to the monthly bill in the billing console.

Pricing

Please refer to the AWS pricing page .

Sample Output:

Images/billservice.png

Download SQL File:

Link to file

Copy Query

    SELECT 
      DATE_FORMAT((line_item_usage_start_date),'%Y-%m-01') AS month_line_item_usage_start_date,
      bill_bill_type,
      CASE
        WHEN (product_product_family = 'Data Transfer') THEN 'Data Transfer' 
        ELSE replace(replace(replace(product_product_name, 'Amazon '),'Amazon'),'AWS ') 
      END AS product_product_name,
      product_location,
      line_item_line_item_description,
      SUM(line_item_unblended_cost) AS sum_line_item_unblended_cost,
      SUM(line_item_usage_amount) AS sum_line_item_usage_amount
    FROM 
      ${table_name}
    WHERE
      ${date_filter}
    GROUP BY 
      1,
      bill_bill_type,
      3,
      product_location,
      line_item_line_item_description
    HAVING SUM(line_item_usage_amount) > 0
    ORDER BY 
      month_line_item_usage_start_date,
      bill_bill_type,
      product_product_name,
      product_location,
      line_item_line_item_description;

Back to Table of Contents

Premium Support Chargeback by Accounts

Query Description

This query will provide a monthly individual account chargeback for the premium support cost based on its contribution to overall AWS bill. This query computes the total monthly aws bill (without tax and support charges) and then calculates just the support charges. Based on the Individual accounts usage/spend percentage, its equivalent support fee is computed.

Pricing

Please refer to the AWS pricing page .

Sample Output:

Images/premiumsupport.png

Download SQL File:

Link to file

Copy Query

    SELECT bill_payer_account_id,
      line_item_usage_account_id,
      SUM(line_item_unblended_cost) AS sum_line_item_unblended_cost,
      ROUND(total_support_cost *((SUM(line_item_unblended_cost)/total_cost)),2) AS support_cost,
      ROUND(SUM(line_item_unblended_cost)/total_cost*100,
      2) AS percentage_of_total_cost,
      ${table_name}.year,
      ${table_name}.month
    FROM ${table_name}
    RIGHT JOIN -- Total AWS bill without support    
      (SELECT SUM(line_item_unblended_cost) AS total_cost,
         year,
         month
       FROM ${table_name}
       WHERE line_item_line_item_type <> 'Tax'
         AND line_item_product_code <> 'OCBPremiumSupport'
       GROUP BY
         year, 
         month) AS aws_total_without_support
    ON (${table_name}.year = aws_total_without_support.year AND ${table_name}.month = aws_total_without_support.month)
    RIGHT JOIN -- Total support    
      (SELECT SUM(line_item_unblended_cost) AS total_support_cost,
         year,
         month
       FROM ${table_name}
       WHERE line_item_product_code = 'OCBPremiumSupport'
         AND line_item_line_item_type <> 'Tax'
       GROUP BY  year, month ) AS aws_support
    ON (${table_name}.year=aws_support.year AND ${table_name}.month = aws_support.month)
    WHERE line_item_line_item_type <> 'Tax'
      AND line_item_product_code <> 'OCBPremiumSupport'
      AND ${table_name}.year = '2020' AND (${table_name}.month BETWEEN '7' AND '9' OR ${table_name}.month BETWEEN '07' AND '09') 
    GROUP BY  
      bill_payer_account_id, 
      total_support_cost, 
      total_cost, 
      ${table_name}.year, 
      ${table_name}.month, 
      line_item_usage_account_id
    ORDER BY  
      support_cost DESC;

Back to Table of Contents

Cost by Charge type

Query Description

This query will aggregate charge types for one or more payers. For more information on various charge types please reference our Cost Explorer documentation . This query will replicate Cost Explorer results when filtering by charge type in the cost explore filters as shown below.

Images/cost-explorer-charge-type-view.png

In order to obtain more granular data, try adding the column ‘line_item_line_item_description’ into the SELECT and Group By Sections (see example #2).

Note: This query expects that you have reserved instances purchased within at least one of the accounts. This query will not run correctly without reserved instances within the CUR data set.

Pricing

N/A

Sample Output:

Images/unblended_cost_by_charge_type.png

Download SQL File:

Link to file

Query Preview:

Example 1:

    SELECT bill_payer_account_id,
        CASE 
          WHEN (line_item_line_item_type = 'Fee' AND product_product_name = 'AWS Premium Support') THEN 'Support fee'
          WHEN (line_item_line_item_type = 'Fee' AND reservation_reservation_a_r_n <> '') THEN 'Upfront reservation fee'
          ELSE line_item_line_item_type 
        END charge_type,
        round(sum(line_item_unblended_cost),2) sum_unblended_cost
    FROM 
      ${table_name}
    WHERE 
      ${date_filter}
    GROUP BY 
      bill_payer_account_id,
      2 -- reference to charge_type case statement
    ORDER BY 
      sum_unblended_cost DESC
    ;

Example 2:

    SELECT bill_payer_account_id, 
        CASE 
          WHEN (line_item_line_item_type = 'Fee' AND product_product_name = 'AWS Premium Support') THEN 'Support fee'
          WHEN (line_item_line_item_type = 'Fee' AND reservation_reservation_a_r_n <> '') THEN 'Upfront reservation fee'
          ELSE line_item_line_item_type 
        END charge_type,
        line_item_line_item_description,
        round(sum(line_item_unblended_cost),2) sum_unblended_cost
    FROM 
      ${table_name}
    WHERE 
      ${date_filter}
    GROUP BY 
      bill_payer_account_id,
      2, -- reference to charge_type case statement
      line_item_line_item_description
    ORDER BY 
      sum_unblended_cost DESC
    ;

Serverless Product Spend

Query Description

This query will provide monthly unblended cost for all Serverless products in use across all regions. This query is helpful in tracking Serverless product adoption as application teams modernize their applications. You can expand the query to include line_item_usage_account_id to show individual service charges per linked account. This query helps provide a view that is difficult to achieve within Cost Explorer.

Pricing

Sample Output:

Images/serverless.png

Download SQL File:

Link to file

Query Preview:

SELECT
  bill_payer_account_id,
  -- if uncommenting, also uncomment three other occurrences of line_item_usage_account_id:
  -- two in SELECTs that are UNIONed and one in GROUP BY or ^F.
  -- line_item_usage_account_id,
  month_line_item_usage_start_date,
  line_item_product_code,
  split_line_item_usage_type,
  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 (

  SELECT
    bill_payer_account_id,
    -- line_item_usage_account_id,
    DATE_FORMAT(line_item_usage_start_date,'%Y-%m') AS month_line_item_usage_start_date,
    line_item_product_code,
    CASE REGEXP_REPLACE(SPLIT_PART(line_item_usage_type, ':', 1), '^[^-]*-')
      WHEN 'Fargate-GB-Hours' THEN 'Fargate'
      WHEN 'Fargate-vCPU-Hours' THEN 'Fargate'
      WHEN 'SpotUsage-Fargate-GB-Hours' THEN 'Fargate'
      WHEN 'SpotUsage-Fargate-vCPU-Hours' THEN 'Fargate'
      ELSE '--'    -- should not be reached!
    END AS split_line_item_usage_type,
    line_item_usage_amount,
    line_item_unblended_cost,
    year,
    month
  FROM ${table_name}
  WHERE
    (
      line_item_line_item_type IN ('DiscountedUsage',
                                   'Usage',
                                   'Credit',
                                   'RIFee',
                                   'SavingsPlanCoveredUsage',
                                   'SavingsPlanNegation')
    )
    AND
    (
      line_item_usage_type LIKE '%Fargate%' AND
      line_item_product_code IN ('AmazonECS', 'AmazonEKS')
    )

  UNION ALL

  SELECT
    bill_payer_account_id,
    -- line_item_usage_account_id,
    DATE_FORMAT(line_item_usage_start_date,'%Y-%m') AS month_line_item_usage_start_date,
    line_item_product_code,
    CASE SPLIT_PART(line_item_usage_type, ':', 2)
      WHEN 'ProxyUsage' THEN 'RDS Proxy Usage'
      WHEN 'ServerlessUsage' THEN 'Aurora Serverless'
      ELSE '--'
    END AS split_line_item_usage_type,
    line_item_usage_amount,
    line_item_unblended_cost,
    year,
    month
  FROM ${table_name}
  WHERE
    (
      line_item_line_item_type IN ('DiscountedUsage',
                                   'Usage',
                                   'Credit',
                                   'RIFee',
                                   'SavingsPlanCoveredUsage',
                                   'SavingsPlanNegation')
    )
    AND
    (
      (
        line_item_product_code = 'AmazonRDS' AND
        SPLIT_PART(line_item_usage_type, ':', 2) IN ('ServerlessUsage', 'ProxyUsage')
      )
      OR
      (
        line_item_product_code IN ('AmazonDynamoDB', 'AmazonDAX',
                                   'AmazonS3', 'AWSAppSync',
                                   'AmazonApiGateway',
                                   'Amazon Simple Notification Service',
                                   'AWSQueueService', 'AWSLambda',
                                   'AWSEvents'
                                  )
      )
    )
)

WHERE
  ${date_filter}
GROUP BY
  bill_payer_account_id,
  -- line_item_usage_account_id,
  month_line_item_usage_start_date,
  line_item_product_code,
  split_line_item_usage_type

ORDER BY
  month_line_item_usage_start_date,
  line_item_product_code,
  split_line_item_usage_type

Back to Table of Contents

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