Exploring AWS IoT costs with AWS Cost and Usage Reports

In this article we present sample Amazon Athena SQL queries that you may use to query AWS Cost and Usage Reports (AWS CUR) for granular billing details on your AWS IoT workloads. For detailed instructions on configuring AWS CUR and integrating with Amazon Athena, please review the online documentation.

The queries below use placeholder variables indicated by a dollar sign and curly braces (${}). 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. Similarly, you would replace the ${date_filter} variable with values similar to the below date filter examples.

Date filter examples

The table below has several date filter examples that may be modified to fit your query needs.

Sample Query 1

This most basic sample query will select all fields within the date filter range and where the line_item_product_code contains ‘IoT’. The query is also limited to return the first 20 records. This is a good practice while exploring the dataset to get an understanding of what will be returned by your queries.

SELECT *
FROM ${table_name} 
WHERE 
    ${date_filter}
    AND line_item_product_code LIKE '%IoT%'
LIMIT 20

Sample Query 2

This sample selects a more limited set of fields, modifies the cost values to user a friendly format, groups the results, and then orders by descending costs.

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,
  line_item_operation,
  product_region,
  line_item_product_code,
  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 
  ${table_name}
WHERE 
  ${date_filter}
  AND line_item_product_code LIKE '%IoT%'
GROUP BY 
  bill_payer_account_id,
  line_item_usage_account_id,
  line_item_usage_start_date,
  line_item_usage_type,
  product_region,
  line_item_product_code,
  line_item_operation
ORDER BY 
  sum_line_item_unblended_cost DESC
LIMIT 20

Sample Output

Below is a sample of data exported using Sample Query 1 and aggregated with an Excel pivot table.