v1.0 for Steampipe, Powerpipe, Flowpipe, 116 plugins, and 44 mods
Steampipe logo
← Back
Shift Left Join

Consolidate your billing data across multiple AWS Organizations

Set budgets and track usage across AWS accounts using SQL.

Venugopal Rao Kumbha
5 min. read - Sep 07, 2022
Set budgets and track usage across AWS accounts using SQL.

To stay within budget you need to proactively monitor your AWS usage. That becomes a complex and tedious task when you run many AWS Organizations. At Turbot, for example, we use a number of AWS Organizations to develop features in Turbot Cloud. Steampipe helps us consolidate the billing data across them. In this post we'll show how queries enabled by Steampipe's AWS plugin can help you do the same for your AWS organizations.

Get started

Here's what you need for this exercise:

Step 1: Find AWS cost for specified record types

Let's start with a single organization. We'll use the aws_cost_by_record_type_monthly table to find AWS costs for the current month. Since we only care about actual usage, we'll ignore Record Types such as Refund, Credit, DiscountedUsage, etc..

-- query q1
select
  linked_account_id,
  period_start,
  period_end,
  sum(blended_cost_amount) 
from
  org_1.aws_cost_by_record_type_monthly 
where
  record_type in ('Usage', 'Recurring', 'Support', 'Tax')
  and period_start >= date_trunc('month', now()) 
group by
  linked_account_id,
  period_start,
  period_end
+-------------------+---------------------------+---------------------------+---------------+
| linked_account_id | period_start              | period_end                | sum           |
+-------------------+---------------------------+---------------------------+---------------+
| 111111110001      | 2022-08-01T05:30:00+05:30 | 2022-08-24T05:30:00+05:30 | 0.5140557876  |
| 111111110000      | 2022-08-01T05:30:00+05:30 | 2022-08-24T05:30:00+05:30 | 10.4237148188 |
| 111111110002      | 2022-08-01T05:30:00+05:30 | 2022-08-24T05:30:00+05:30 | 14.5075173753 |
| 111111110003      | 2022-08-01T05:30:00+05:30 | 2022-08-24T05:30:00+05:30 | 2.3648034494  |
| 111111110004      | 2022-08-01T05:30:00+05:30 | 2022-08-24T05:30:00+05:30 | 0.0000000572  |
| 111111110005      | 2022-08-01T05:30:00+05:30 | 2022-08-24T05:30:00+05:30 | 0.000025      |
+-------------------+---------------------------+---------------------------+---------------+

Step 2: Prettify accounts and organization(s) with user-friendly names

  • Format the sum of blended_cost_amount as currency type

  • Join with the table aws_organizations_account which has more details about the linked account

  • Add a user-friendly name for the Organization by sourcing the Management account id.

In this case we querying for the current month, so can omit period_start and period_end.

-- query q2
select
  case
    when a.account_id = '111111110000' then 'Infinity Gems' 
  end
  as organization,
  a.id as member_id,
  a.name as account_name,
  a.status,
  sum(m.blended_cost_amount::numeric::money) as current_month 
from
  org_1.aws_organizations_account a
join
  org_1.aws_cost_by_record_type_monthly m 
on
  a.id = m.linked_account_id 
where
  m.record_type in ('Usage', 'Recurring', 'Support', 'Tax')
  and m.period_start >= date_trunc('month', now())
group by
  a.account_id, 
  a.id,
  a.name,
  a.status
+---------------+--------------+--------------+--------+---------------+
| organization  | member_id    | account_name | status | current_month |
+---------------+--------------+--------------+--------+---------------+
| Infinity Gems | 111111110001 | Reality      | ACTIVE | $0.52         |
| Infinity Gems | 111111110000 | Soul         | ACTIVE | $10.53        |
| Infinity Gems | 111111110002 | Power        | ACTIVE | $14.68        |
| Infinity Gems | 111111110003 | Mind         | ACTIVE | $2.40         |
| Infinity Gems | 111111110004 | Space        | ACTIVE | $0.00         |
| Infinity Gems | 111111110005 | Time         | ACTIVE | $0.00         |
+---------------+--------------+--------------+--------+---------------+

STEP 3: Tag accounts with budget and owner details

Typically we have a budget for each account. It's a good practice to regularly check if the account is underutilized or overutilized. To enable that, let's add tags to each account.

owner: The person to notify of anomalies. The value can be an email, Slack, or any other unique ID. We'll use email.

budget: The number to compare against current spending.

Here is an example of the account tags on the Organizations page. 

owner: warlock@mcu.xyz
budget: 100
-- query q3
select
  case
    when a.account_id = '111111110000' then 'Infinity Gems' 
  end as organization,
  a.id as member_id,
  a.name as account_name,
  a.tags ->> 'owner' as owner,
  (a.tags ->> 'budget')::money as budget,
  sum(m.blended_cost_amount::numeric::money) as current_month 
from
  org_1.aws_organizations_account as a, -- implicit cross join again
  org_1.aws_cost_by_record_type_monthly as m 
where
  m.record_type in ('Usage', 'Recurring', 'Support', 'Tax')
  and a.status = 'ACTIVE' 
  and a.id = m.linked_account_id 
  and m.period_start >= date_trunc('month', now())
group by
  a.account_id,
  a.id,
  a.name,
  a.status,
  a.tags
+---------------+--------------+---------------+--------------------------+--------+---------------+
| organization  | member_id    | account_name  | owner                    | budget | current_month |
+---------------+--------------+---------------+--------------------------+--------+---------------+
| Infinity Gems | 111111110001 | Reality       | thanos@mcu.xyz           | $25.00 | $0.52         |
| Infinity Gems | 111111110000 | Soul          | warlock@mcu.xyz          | $25.00 | $10.53        |
| Infinity Gems | 111111110002 | Power         | draxthedestroyer@mcu.xyz | $25.00 | $14.68        |
| Infinity Gems | 111111110003 | Mind          | moondragon@mcu.xyz       | $25.00 | $2.40         |
| Infinity Gems | 111111110004 | Space         | pipthetroll@mcu.xyz      | $1.00  | $0.00         |
| Infinity Gems | 111111110005 | Time          | gamora@mcu.xyz           | $1.00  | $0.00         |
+---------------+--------------+---------------+--------------------------+--------+---------------+

Alternatively, if you have the account owner and budget details in a .csv file then can join it with the AWS tables using Steampipe's CSV plugin. Follow the example Joining CSV and API tables for more help.

Step 4: Budget Status

We run query 3 on the 7th, 14th, 21st, and 28th of the month. For us, the budget tag denotes a monthly cap, so we distribute that total across four weeks and check relative to the current week. So for example the status is "over the budget" if an account has consumed more than 50% of its allocated budget by the 14th of the month, otherwise it is "under the budget."

-- query q4
with monthly_billing_data as 
(
  select
    date_part('day', CURRENT_DATE) as today,
    case
      when a.account_id = '111111110000' then 'Infinity Gems' 
    end as organization,
    a.id as member_id,
    a.name as account_name,
    a.tags ->> 'owner' as owner, 
    (a.tags ->> 'budget')::money as budget, 
    sum(m.blended_cost_amount::numeric::money) as current_month 
  from
    org_1.aws_organizations_account as a, -- implicit cross join
    org_1.aws_cost_by_record_type_monthly as m 
  where
    m.record_type in ('Usage', 'Recurring', 'Support', 'Tax')
    and a.status = 'ACTIVE' 
    and a.id = m.linked_account_id 
    and m.period_start >= date_trunc('month', now())
  group by
    a.account_id,
    a.id,
    a.name,
    a.status,
    a.tags 
)
select
  organization,
  member_id,
  account_name,
  owner,
  budget,
  current_month,
  case
    when budget is null then 'Unknown' 
    when today = 7 and current_month < (budget*0.25) then 'Under' 
    when today = 7 and current_month >= (budget*0.25) then 'Over' 
    when today = 14 and current_month < (budget*0.5) then 'Under' 
    when today = 14 and current_month >= (budget*0.5) then 'Over' 
    when today = 21 and current_month < (budget*0.75) then 'Under' 
    when today = 21 and current_month >= (budget*0.75) then 'Over' 
    when today = 28 and current_month < (budget) then 'Under' 
    when today = 28 and current_month >= (budget) then 'Over' 
  end as budget_state 
from
  monthly_billing_data 
order by
  current_month desc
+---------------+--------------+--------------+--------------------------+--------+---------------+--------------+
| organization  | member_id    | account_name | owner                    | budget | current_month | budget_state |
+---------------+--------------+--------------+--------------------------+--------+---------------+--------------+
| Infinity Gems | 111111110002 | Power        | draxthedestroyer@mcu.xyz | $25.00 | $14.68        | Under        |
| Infinity Gems | 111111110000 | Soul         | warlock@mcu.xyz          | $25.00 | $10.53        | Under        |
| Infinity Gems | 111111110003 | Mind         | moondragon@mcu.xyz       | $25.00 | $2.40         | Under        |
| Infinity Gems | 111111110001 | Reality      | thanos@mcu.xyz           | $25.00 | $0.52         | Under        |
| Infinity Gems | 111111110004 | Space        | pipthetroll@mcu.xyz      | $1.00  | $0.00         | Under        |
| Infinity Gems | 111111110005 | Time         | gamora@mcu.xyz           | $1.00  | $0.00         | Under        |
+---------------+--------------+--------------+--------------------------+--------+---------------+--------------+

Step 5: Aggregator Connection for multi-organization

Now we're ready to apply this technique to multiple Organizations. To do that, create an aggregator connection in your aws.spc file, like so:

connection "org_1" {
  plugin  = "aws"
  profile = "infinity-gems-mgmt-account"
  regions = ["us-east-1"]
}


connection "org_2" {
  plugin  = "aws"
  profile = "mcandarins-rings-mgmt-account"
  regions = ["us-east-1"]
}


connection "org_all" {
  plugin      = "aws"
  type        = "aggregator"
  connections = ["org_1", "org_2"]
}

Then change the connection in the query from org_1 to org_all and adjust the user-friendly names. In this examle, account_id refers to the Management Account Id.

case
  when a.account_id = '111111110000' then 'Infinity Gems'
  when a.account_id = '222222220000' then 'Mandarin Rings'
end as organization

You should now have a working query that returns AWS usage for multiple Organizations!

Compare against last month

It may be helpful to capture the previous month's usage as well. 

--query q5
with monthly_billing_data as (
  select
    case
      when a.account_id = '111111110000' then 'Infinity Gems'
      when a.account_id = '222222220000' then 'Mandarin Rings'
    end as organization,
    a.id as member_id,
    a.name as account_name,
    a.tags ->> 'owner' as owner,
    (a.tags ->> 'budget')::money as budget,
    sum(m.blended_cost_amount::numeric::money) as current_month,
    m.period_start
  from 
    org_all.aws_organizations_account as a,
    org_all.aws_cost_by_record_type_monthly as m
  where
    m.record_type in  ('Usage', 'Recurring', 'Support', 'Tax')
    and a.status = 'ACTIVE'
    and a.id = m.linked_account_id
    and m.period_start >= date_trunc('month', now() - interval '1 months')
  group by
    a.account_id,
    a.id,
    a.name,
    a.tags,
    m.period_start
), monthly_aggreggrate_billing as (
  select
    organization,
    member_id, 
    account_name,
    owner,
    budget,
    string_agg(current_month::text ,'|' order by period_start) as billing
  from 
    monthly_billing_data
  group by
    organization,
    account_name,
    member_id,
    owner,
    budget
), current_and_previous_month_billing as (
select
    organization,
    member_id,
    account_name,
    owner,
    budget,
    billing,
    date_part('day', CURRENT_DATE) as today,
    -- If the AWS Account is created this month, the previous month billing should be $0.00
    case
      when (LENGTH(billing::text) - LENGTH(REPLACE(billing::text, '|', '')) + 1) = 2 then split_part(billing::text, '|', 1)
      when (LENGTH(billing::text) - LENGTH(REPLACE(billing::text, '|', '')) + 1) = 1 then '$0.00' end as previous_month,
    case
      when (LENGTH(billing::text) - LENGTH(REPLACE(billing::text, '|', '')) + 1) = 2 then split_part(billing::text, '|', 2)
      when (LENGTH(billing::text) - LENGTH(REPLACE(billing::text, '|', '')) + 1) = 1 then split_part(billing::text, '|', 1) end as current_month
    from monthly_aggreggrate_billing
)
  select organization, member_id, account_name,  owner, budget, previous_month::money, current_month::money,
    case
      when budget is null then 'Unknown'
      when today = 7  and current_month::money < (budget*0.25) then 'Under' 
      when today = 7  and current_month::money >= (budget*0.25) then 'Over'
      when today = 14 and current_month::money < (budget*0.5) then 'Under'
      when today = 14 and current_month::money >= (budget*0.5) then 'Over'
      when today = 21 and current_month::money < (budget*0.75) then 'Under'
      when today = 21 and current_month::money >= (budget*0.75) then 'Over'
      when today = 28 and current_month::money < (budget) then 'Under'
      when today = 28 and current_month::money >= (budget) then 'Over'
    end as budget_state
  from 
    current_and_previous_month_billing
  order by 
    current_month desc
+------------------+--------------+---------------+----------------------------+---------+----------------+---------------+--------------+
| organization     | member_id    | account_name  | owner                      | budget  | previous_month | current_month | budget_state |
+------------------+--------------+---------------+----------------------------+---------+----------------+---------------+--------------+
| Mandarin Rings   | 222222220001 | Incandescence | abigailburns@mcu.xyz       | $100.00 | $439.16        | $323.49       | Over         |
| Mandarin Rings   | 222222220000 | Spin          | aleceiffel@mcu.xyz         | $50.00  | $89.66         | $69.71        | Over         |
| Mandarin Rings   | 222222220002 | Lightning     | lightningconductor@mcu.xyz | $25.00  | $22.95         | $21.56        | Under        |
| Infinity Gems    | 111111110002 | Power         | draxthedestroyer@mcu.xyz   | $25.00  | $19.69         | $14.68        | Under        |
| Infinity Gems    | 111111110000 | Soul          | warlock@mcu.xyz            | $25.00  | $11.48         | $10.53        | Under        |
| Mandarin Rings   | 222222220003 | Zero          | endotherm@mcu.xyz          | $25.00  | $6.62          | $5.04         | Under        |
| Infinity Gems    | 111111110003 | Mind          | moondragon@mcu.xyz         | $25.00  | $2.96          | $2.40         | Under        |
| Mandarin Rings   | 222222220004 | Influence     | colinsixty@mcu.xyz         | $25.00  | $1.15          | $0.86         | Under        |
| Infinity Gems    | 111111110001 | Reality       | thanos@mcu.xyz             | $25.00  | $0.61          | $0.52         | Under        |
| Infinity Gems    | 111111110005 | Time          | gamora@mcu.xyz             | $1.00   | $0.00          | $0.00         | Under        |
| Infinity Gems    | 111111110004 | Space         | pipthetroll@mcu.xyz        | $1.00   | $0.00          | $0.00         | Under        |
+------------------+--------------+---------------+----------------------------+---------+----------------+---------------+--------------+

Conclusion

The techniques we've shown here work well for us, but everyone's situation is unique and you may find a solution that works better for you. If so, please let us know: we love to learn from our community!