steampipe plugin install awssteampipe plugin install aws

Table: aws_cost_by_service_monthly

Amazon Cost Explorer helps you visualize, understand, and manage your AWS costs and usage. The aws_cost_by_service_monthly table provides a simplified view of cost for services in your account (or all linked accounts when run against the organization master), summarized by month, for the last year.

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

Examples

Basic info

select
  service,
  period_start,
  blended_cost_amount :: numeric :: money,
  unblended_cost_amount :: numeric :: money,
  amortized_cost_amount :: numeric :: money,
  net_unblended_cost_amount :: numeric :: money,
  net_amortized_cost_amount :: numeric :: money
from
  aws_cost_by_service_monthly
order by
  service,
  period_start;

Min, Max, and average monthly unblended_cost_amount by service

select
  service,
  min(unblended_cost_amount) :: numeric :: money as min,
  max(unblended_cost_amount) :: numeric :: money as max,
  avg(unblended_cost_amount) :: numeric :: money as average
from
  aws_cost_by_service_monthly
group by
  service
order by
  service;

Top 10 most expensive service (by average monthly unblended_cost_amount)

select
  service,
  sum(unblended_cost_amount) :: numeric :: money as sum,
  avg(unblended_cost_amount) :: numeric :: money as average
from
  aws_cost_by_service_monthly
group by
  service
order by
  average desc
limit
  10;

Top 10 most expensive service (by total monthly unblended_cost_amount)

select
  service,
  sum(unblended_cost_amount) :: numeric :: money as sum,
  avg(unblended_cost_amount) :: numeric :: money as average
from
  aws_cost_by_service_monthly
group by
  service
order by
  sum desc
limit
  10;

Ranked - Most expensive month (unblended_cost_amount) by service

with ranked_costs as (
  select
    service,
    period_start,
    unblended_cost_amount :: numeric :: money,
    rank() over(
      partition by service
      order by
        unblended_cost_amount desc
    )
  from
    aws_cost_by_service_monthly
)
select
  *
from
  ranked_costs
where
  rank = 1

Month on month growth (unblended_cost_amount) by service

with cost_data as (
  select
    service,
    period_start,
    unblended_cost_amount as this_month,
    lag(unblended_cost_amount, 1) over(
      partition by service
      order by
        period_start desc
    ) as previous_month
  from
    aws_cost_by_service_monthly
)
select
  service,
  period_start,
  this_month :: numeric :: money,
  previous_month :: numeric :: money,
  case
    when previous_month = 0
    and this_month = 0 then 0
    when previous_month = 0 then 999
    else round(
      (100 * ((this_month - previous_month) / previous_month)) :: numeric,
      2
    )
  end as percent_change
from
  cost_data
order by
  service,
  period_start;

Query examples

.inspect aws_cost_by_service_monthly

AWS Cost Explorer - Cost by Service (Monthly)

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.
estimated	boolean	Whether the result is estimated.
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.
service	text	The name of the AWS service.
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