v0.17.0: Snapshots, share with cloud, workspace profiles →
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 - September 7, 2022

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!