steampipe plugin install aws

Table: aws_cost_usage - Query AWS Cost Explorer Service Cost and Usage using SQL

The AWS Cost Explorer Service is a tool that allows you to visualize, understand, and manage your AWS costs and usage over time. It provides detailed information about your costs and usage, including trends, cost drivers, and anomalies. With Cost Explorer, you can filter views by various dimensions such as service, linked account, and tags, and view data for up to the last 13 months.

Table Usage Guide

The aws_cost_usage table in Steampipe provides you with information about cost and usage data from AWS Cost Explorer Service. This table enables you as a financial analyst or cloud architect to query cost and usage details, including cost allocation tags, service usage, cost usage, and associated metadata. You can utilize this table to gather insights on cost and usage, such as cost per service, usage per service, verification of cost allocation tags, and more. The schema outlines the various attributes of the cost and usage data for you, including the time period, unblended cost, usage type, and associated tags.

Amazon Cost Explorer assists you in visualizing, understanding, and managing your AWS costs and usage. The aws_cost_usage table offers you a simplified yet flexible view of cost for your account (or all linked accounts when run against the organization master). You need to specify a granularity (MONTHLY, DAILY), and 2 dimension types (AZ , INSTANCE_TYPE, LEGAL_ENTITY_NAME, LINKED_ACCOUNT, OPERATION, PLATFORM, PURCHASE_TYPE, SERVICE, TENANCY, RECORD_TYPE, and USAGE_TYPE)

Important Notes

  • This table requires an '=' qualifier for all of the following columns: granularity, dimension_type_1, dimension_type_2.
  • The pricing for the Cost Explorer API is per API request - Each request will incur a cost of $0.01 for you.

Examples

Monthly net unblended cost by account and service

Explore the monthly expenditure for each linked account and service in your AWS environment. This query can help you understand your cost trends and identify areas for potential savings.

select
period_start,
dimension_1 as account_id,
dimension_2 as service_name,
net_unblended_cost_amount :: numeric :: money
from
aws_cost_usage
where
granularity = 'MONTHLY'
and dimension_type_1 = 'LINKED_ACCOUNT'
and dimension_type_2 = 'SERVICE'
order by
dimension_1,
period_start;
select
period_start,
dimension_1 as account_id,
dimension_2 as service_name,
cast(net_unblended_cost_amount as real) as net_unblended_cost_amount
from
aws_cost_usage
where
granularity = 'MONTHLY'
and dimension_type_1 = 'LINKED_ACCOUNT'
and dimension_type_2 = 'SERVICE'
order by
dimension_1,
period_start;

Top 5 most expensive services (net unblended cost) in each account

Identify the top five most costly services in each account to manage and optimize your AWS expenses effectively.

with ranked_costs as (
select
dimension_1 as account_id,
dimension_2 as service_name,
sum(net_unblended_cost_amount) :: numeric :: money as net_unblended_cost,
rank() over(
partition by dimension_1
order by
sum(net_unblended_cost_amount) desc
)
from
aws_cost_usage
where
granularity = 'MONTHLY'
and dimension_type_1 = 'LINKED_ACCOUNT'
and dimension_type_2 = 'SERVICE'
group by
dimension_1,
dimension_2
order by
dimension_1,
net_unblended_cost desc
)
select
*
from
ranked_costs
where
rank <= 5
Error: SQLite does not support rank window functions.

Monthly net unblended cost by account and record type

Analyze your monthly AWS account costs by record type to better understand your expenses. This can help you identify areas where costs may be reduced or controlled.

select
period_start,
dimension_1 as account_id,
dimension_2 as record_type,
net_unblended_cost_amount :: numeric :: money
from
aws_cost_usage
where
granularity = 'MONTHLY'
and dimension_type_1 = 'LINKED_ACCOUNT'
and dimension_type_2 = 'RECORD_TYPE'
order by
dimension_1,
period_start;
select
period_start,
dimension_1 as account_id,
dimension_2 as record_type,
CAST(net_unblended_cost_amount AS REAL) AS net_unblended_cost_amount
from
aws_cost_usage
where
granularity = 'MONTHLY'
and dimension_type_1 = 'LINKED_ACCOUNT'
and dimension_type_2 = 'RECORD_TYPE'
order by
dimension_1,
period_start;

List monthly discounts and credits by account

This query allows users to monitor their AWS account's monthly spending by tracking discounts and credits. It's beneficial for budgeting purposes and helps in optimizing cost management strategies.

select
period_start,
dimension_1 as account_id,
dimension_2 as record_type,
net_unblended_cost_amount :: numeric :: money
from
aws_cost_usage
where
granularity = 'MONTHLY'
and dimension_type_1 = 'LINKED_ACCOUNT'
and dimension_type_2 = 'RECORD_TYPE'
and dimension_2 in ('DiscountedUsage', 'Credit')
order by
dimension_1,
period_start;
select
period_start,
dimension_1 as account_id,
dimension_2 as record_type,
CAST(net_unblended_cost_amount AS REAL) as net_unblended_cost_amount
from
aws_cost_usage
where
granularity = 'MONTHLY'
and dimension_type_1 = 'LINKED_ACCOUNT'
and dimension_type_2 = 'RECORD_TYPE'
and dimension_2 in ('DiscountedUsage', 'Credit')
order by
dimension_1,
period_start;

Schema for aws_cost_usage

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
amortized_cost_amountdouble precisionThis cost metric reflects the effective cost of the upfront and monthly reservation fees spread across the billing period. By default, Cost Explorer shows the fees for Reserved Instances as a spike on the day that you're charged, but if you choose to show costs as amortized costs, the costs are amortized over the billing period. This means that the costs are broken out into the effective daily rate. AWS estimates your amortized costs by combining your unblended costs with the amortized portion of your upfront and recurring reservation fees.
amortized_cost_unittextUnit type for amortized costs.
blended_cost_amountdouble precisionThis cost metric reflects the average cost of usage across the consolidated billing family. If you use the consolidated billing feature in AWS Organizations, you can view costs using blended rates.
blended_cost_unittextUnit type for blended costs.
dimension_1textValid values are AZ, INSTANCE_TYPE, LINKED_ACCOUNT, OPERATION, PURCHASE_TYPE, SERVICE, USAGE_TYPE, PLATFORM, TENANCY, RECORD_TYPE, LEGAL_ENTITY_NAME, DEPLOYMENT_OPTION, DATABASE_ENGINE, CACHE_ENGINE, INSTANCE_TYPE_FAMILY, REGION, BILLING_ENTITY, RESERVATION_ID, SAVINGS_PLANS_TYPE, SAVINGS_PLAN_ARN, OPERATING_SYSTEM
dimension_2textValid values are AZ, INSTANCE_TYPE, LINKED_ACCOUNT, OPERATION, PURCHASE_TYPE, SERVICE, USAGE_TYPE, PLATFORM, TENANCY, RECORD_TYPE, LEGAL_ENTITY_NAME, DEPLOYMENT_OPTION, DATABASE_ENGINE, CACHE_ENGINE, INSTANCE_TYPE_FAMILY, REGION, BILLING_ENTITY, RESERVATION_ID, SAVINGS_PLANS_TYPE, SAVINGS_PLAN_ARN, OPERATING_SYSTEM
dimension_type_1text=
dimension_type_2text=
estimatedbooleanWhether the result is estimated.
granularitytext=
net_amortized_cost_amountdouble precisionThis cost metric amortizes the upfront and monthly reservation fees while including discounts such as RI volume discounts.
net_amortized_cost_unittextUnit type for net amortized costs.
net_unblended_cost_amountdouble precisionThis cost metric reflects the unblended cost after discounts.
net_unblended_cost_unittextUnit type for net unblended costs.
normalized_usage_amountdouble precisionThe amount of usage that you incurred, in normalized units, for size-flexible RIs. The NormalizedUsageAmount is equal to UsageAmount multiplied by NormalizationFactor.
normalized_usage_unittextUnit type for normalized usage.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
period_endtimestamp with time zoneEnd timestamp for this cost metric.
period_starttimestamp with time zoneStart timestamp for this cost metric.
regiontextThe AWS Region in which the resource is located.
search_end_timetimestamp with time zone
search_start_timetimestamp with time zone
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
unblended_cost_amountdouble precisionUnblended costs represent your usage costs on the day they are charged to you. In finance terms, they represent your costs on a cash basis of accounting.
unblended_cost_unittextUnit type for unblended costs.
usage_quantity_amountdouble precisionThe amount of usage that you incurred. NOTE: If you return the UsageQuantity metric, the service aggregates all usage numbers without taking into account the units. For example, if you aggregate usageQuantity across all of Amazon EC2, the results aren't meaningful because Amazon EC2 compute hours and data transfer are measured in different units (for example, hours vs. GB).
usage_quantity_unittextUnit type for usage quantity.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aws

You can pass the configuration to the command with the --config argument:

steampipe_export_aws --config '<your_config>' aws_cost_usage