steampipe plugin install awssteampipe plugin install aws

Table: aws_cost_usage

Amazon Cost Explorer helps you visualize, understand, and manage your AWS costs and usage. The aws_cost_usage table provides a simplified yet flexible view of cost for your account (or all linked accounts when run against the organization master). You must specify a granularity (MONTHLY, DAILY), and 2 dimension types (AZ , INSTANCE_TYPE, LEGAL_ENTITY_NAME, LINKED_ACCOUNT, OPERATION, PLATFORM, PURCHASE_TYPE, SERVICE, TAGS, TENANCY, RECORD_TYPE, and USAGE_TYPE)

This tables requires an '=' qualifier for all of the following columns: granularity,dimension_type_1,dimension_type_2

Note that pricing for the Cost Explorer API is per API request - Each request will incur a cost of $0.01.

Examples

Monthly net unblended cost by account and service

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;

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

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

Monthly net unblended cost by account and record type

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;

List monthly discounts and credits by account

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;

.inspect aws_cost_usage

AWS Cost Explorer - Cost and Usage

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account_id	text	The AWS Account ID in which the resource is located.
amortized_cost_amount	double precision	This 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_unit	text	Unit type for amortized costs.
blended_cost_amount	double precision	This 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_unit	text	Unit type for blended costs.
dimension_1	text	Valid 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_2	text	Valid 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_1	text
dimension_type_2	text
estimated	boolean	Whether the result is estimated.
granularity	text
net_amortized_cost_amount	double precision	This cost metric amortizes the upfront and monthly reservation fees while including discounts such as RI volume discounts.
net_amortized_cost_unit	text	Unit type for net amortized costs.
net_unblended_cost_amount	double precision	This cost metric reflects the unblended cost after discounts.
net_unblended_cost_unit	text	Unit type for net unblended costs.
normalized_usage_amount	double precision	The amount of usage that you incurred, in normalized units, for size-flexible RIs. The NormalizedUsageAmount is equal to UsageAmount multiplied by NormalizationFactor.
normalized_usage_unit	text	Unit type for normalized usage.
partition	text	The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
period_end	timestamp with time zone	End timestamp for this cost metric.
period_start	timestamp with time zone	Start timestamp for this cost metric.
region	text	The AWS Region in which the resource is located.
search_end_time	timestamp with time zone
search_start_time	timestamp with time zone
unblended_cost_amount	double precision	Unblended 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_unit	text	Unit type for unblended costs.
usage_quantity_amount	double precision	The 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_unit	text	Unit type for usage quantity.

turbot/aws