steampipe plugin install awssteampipe plugin install aws

Table: aws_cost_by_account_monthly

Amazon Cost Explorer helps you visualize, understand, and manage your AWS costs and usage. The aws_cost_by_account_monthly table provides a simplified view of cost for 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
  linked_account_id,
  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_account_monthly
order by
  linked_account_id,
  period_start;

Min, Max, and average monthly unblended_cost_amount by account

select
  linked_account_id,
  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_account_monthly
group by
  linked_account_id
order by
  linked_account_id;

Ranked - Most expensive months (unblended_cost_amount) by account

select
  linked_account_id,
  period_start,
  unblended_cost_amount :: numeric :: money,
  rank() over(
    partition by linked_account_id
    order by
      unblended_cost_amount desc
  )
from
  aws_cost_by_account_monthly;

Month on month growth (unblended_cost_amount) by account

with cost_data as (
  select
    linked_account_id,
    period_start,
    unblended_cost_amount as this_month,
    lag(unblended_cost_amount, 1) over(
      partition by linked_account_id
      order by
        period_start desc
    ) as previous_month
  from
    aws_cost_by_account_monthly
)
select
  linked_account_id,
  period_start,
  this_month :: numeric :: money,
  previous_month :: numeric :: money,
  round(
    (100 * ((this_month - previous_month) / previous_month)) :: numeric,
    2
  ) as percent_change
from
  cost_data
order by
  linked_account_id,
  period_start;

.inspect aws_cost_by_account_monthly

AWS Cost Explorer - Cost by Linked Account (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.
linked_account_id	text	The AWS Account ID.
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.
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