Cost Explorer Isn't the Answer

I recently launched and started sharing the Developer's Guide to AWS. The Developer's Guide to AWS is my open-source project where I'm sharing techniques for analyzing AWS billing data using SQL.

The feedback I've gotten from the development community has been incredible. I appreciate the time people have taken to check out the code and provide some input on the roadmap!

If you're interested in following the project and providing some feedback, you can check it out on GitHub. If you like the analysis, give us a star ⭐️!

Why Not Cost Explorer?

One piece of feedback I've been getting pretty regularly is: "Why can't you just use Cost Explorer?" (check out this Reddit thread). I decided to summarize my opinions here so I can share this post instead of having to explain the reasoning every time I get this question in the future. 🤣

A little background on me: I'm a co-founder of Strake, and before this, I worked with companies helping finance and engineering teams understand their AWS billing. One of the biggest problems for AWS customers across the board is a lack of real cost visibility.

The truth is, your engineering teams can't understand and fix a cost problem if they don't have access to the right data. I've seen startups and multi-billion dollar enterprises trying to manage their costs using Cost Explorer, and here are some of the biggest problems we've encountered:

• Exports, exports, and more exports.
• What is EC2-Other?
• Where are my reservations?

  ---

Exports, exports, and more exports

The single most significant drawback of Cost Explorer is the lack of the ability to filter data & visualizations by more than one variable.

Let's say, for example, I have 10 AWS accounts, and I want to understand my on-demand costs by AWS Service for each of my 10 accounts. I would not be able to do that in a single Cost Explorer visualization. My options are:

1. Create views for each AWS Account, grouped by Service
2. Create views for each Service, grouped by AWS Account.

For this simple example, a user would have to create a minimum of 10 views, export those views to CSV, combine the data and make a visualization. This process is a massive waste of engineering time when you compare all that work to this simple query from your cost and usage report:

##On-demand costs by Account, AWS Service
SELECT
    [lineItem/UsageAccountID],
    [lineItem/ProductCode],
    sum([lineItem/UnblendedCost])
FROM cur
GROUP BY
    [lineItem/UsageAccountID],
    [lineItem/ProductCode];

Can you get to the correct answer using Cost Explorer? Yes, eventually. I will argue all day that creating these views, exporting the data, and combining the reports in Excel is not a valuable exercise for anyone in your company.

What is EC2-Other?

I love EC2-Other because we've all been in conference rooms when bad decisions were made. I like to think EC2-Other was discussed back in 2006 as a placeholder, and here we are in 2022 STILL trying to figure out what it means.

First, it's good to discuss which costs are considered EC2-Other. I go into more detail on this topic in my first EC2 code guide, but here is the summary:

1. EBS volumes and snapshots
2. Nat Gateway resources and fees
3. Data Transfer costs

So now that we know what costs we're looking for, how can we break down these cost categories in Cost Explorer? It is possible to get the correct answer in Cost Explorer if you manually filter the 'Usage Type' and 'Usage Type Group' fields for each EC2-Other cost type. Then you would repeat the process: create views for each cost category, export the data from those views to CSV, and develop visualizations as required.

To circumnavigate all this work, I've written queries for the Cost and Usage Report that break down the various categories of EC2-Other costs. This query is an example of how to isolate EBS Snapshot costs:

#Subresource Costs
##Subresource costs for EBS Volume Snapshots
SELECT DISTINCT
    [lineItem/ResourceID],
    [lineItem/LineItemType],
    [lineItem/Operation],
    round(sum([lineItem/UnblendedCost]), 4) as subresource_cost
FROM CUR
WHERE
    [lineItem/ProductCode] is 'AmazonEC2'
    and [lineItem/ResourceId] LIKE '%snapshot%'
GROUP BY
    [lineItem/ResourceID],
    [lineitem/lineitemtype],
    [lineItem/Operation]
ORDER BY
    sum([lineItem/UnblendedCost]);

Unfortunately, EC2-Other isn't going anywhere. Fortunately, using the CUR instead of Cost Explorer, we can circumnavigate the issue and get to the bottom of what we are really being charged for.

Where are my Reservations?

One of the most confusing things I've seen engineering teams try to overcome is the lack of visibility to where their reservations are being applied. Making commitments in AWS is one of the few ways to keep AWS costs manageable. These commitments come in three primary forms: EDPs, Reserved Instances, and Savings Plans.

For another example: an AWS customer with 1 management account and 9 member accounts makes reservations on their EC2 usage. This reservation is made in the management account, so the cost savings can be applied to all AWS accounts. How does this actually show up in Cost explorer?

• The monthly cost of the reservations would hit the management account, even if the reservation was used by a member account.
• Any reservation usage in the member accounts shows up as $0.

This is a massive problem for engineering cost visibility. Overnight, some engineers will see their costs drop by $0, while others will not get any benefit from the reserved instances being purchased. The only answer for this get using Cost Explorer would be pulling EC2 instance hours by instance type for every account and reverse-engineering a $-per-hour rate that can be applied to the EC2 usage. I've done this before, and it is terrible.

A much easier solution would be to use this query on your CUR. This query breaks out the usage by instance type and region for each of the EC2 pricing models (on-demand, spot, reserved instance, and savings plans).

#Total existence cost for EC2
##Total existence cost by resource ID
WITH on_demand_existence AS (
    SELECT
        [lineItem/ResourceId],
        round(sum([lineItem/UnblendedCost]), 4) as existence_cost
    FROM CUR
    WHERE
        [lineItem/ProductCode] = 'AmazonEC2'
        and [product/instanceType] <> ""
        and [lineItem/LineItemType] is 'Usage'
        and [lineItem/UsageType] LIKE '%BoxUsage%'
        and [lineItem/Operation] LIKE 'RunInstances%'
    GROUP BY
        [lineItem/ResourceId]
)
, spot_existence AS (
    SELECT
        [lineItem/ResourceId],
        round(sum([lineItem/UnblendedCost]), 4) as existence_cost
    FROM CUR
    WHERE
        [lineItem/ProductCode] = 'AmazonEC2'
        and [product/instanceType] <> ""
        and [lineItem/LineItemType] is 'Usage'
        and [lineItem/UsageType] LIKE '%SpotUsage%'
        and [lineItem/Operation] LIKE 'RunInstances%'
    GROUP BY
        [lineItem/ResourceId]
)
, reserved_existence AS (
    SELECT
        [lineItem/ResourceId],
        round(sum([reservation/EffectiveCost]), 4) as existence_cost
    FROM CUR
    WHERE
        [lineItem/ProductCode] = 'AmazonEC2'
        and [product/instanceType] <> ""
        and [lineItem/LineItemType] is 'DiscountedUsage'
    GROUP BY
        [lineItem/ResourceId]
)
, savings_plan_existence AS (
    SELECT 
        [lineItem/ResourceId],
        round(sum([savingsPlan/SavingsPlanEffectiveCost]), 4) as existence_cost
    FROM CUR
    WHERE
        [lineItem/ProductCode] = 'AmazonEC2'
        and [product/instanceType] <> ""
        and [lineItem/LineItemType] is 'SavingsPlanCoveredUsage'
    GROUP BY 
        [lineItem/ResourceId]
)
SELECT
    CUR.[lineitem/ResourceId],
    CUR.[product/instanceType],
    CUR.[product/region],
    COALESCE(on_demand_existence.existence_cost, 0) as on_demand_existence_cost,
    COALESCE(spot_existence.existence_cost, 0) as spot_existence_cost,
    COALESCE(reserved_existence.existence_cost, 0) as reserved_existence_cost,
    COALESCE(savings_plan_existence.existence_cost, 0) as savings_plan_existence_cost,
    (COALESCE(on_demand_existence.existence_cost, 0) + COALESCE(spot_existence.existence_cost, 0) + COALESCE(reserved_existence.existence_cost, 0) + COALESCE(savings_plan_existence.existence_cost, 0)) AS total_existence_cost
FROM CUR
LEFT JOIN 
    on_demand_existence
    ON on_demand_existence.[lineItem/ResourceId] = CUR.[lineItem/ResourceId]
LEFT JOIN 
    spot_existence
    ON spot_existence.[lineItem/ResourceId] = CUR.[lineItem/ResourceId]
LEFT JOIN 
    reserved_existence
    ON reserved_existence.[lineItem/ResourceId] = CUR.[lineItem/ResourceId]
LEFT JOIN 
    savings_plan_existence
    ON savings_plan_existence.[lineItem/ResourceId] = CUR.[lineItem/ResourceId]
WHERE 
    CUR.[lineItem/ProductCode] is 'AmazonEC2'
    and CUR.[product/instanceType] <> ""
    and CUR.[lineitem/ResourceId] <> ""
GROUP BY
    CUR.[lineitem/ResourceId],
    CUR.[product/instanceType],
    CUR.[product/region]
ORDER BY
    total_existence_cost;

Final Thoughts

Cost Explorer does not meet the engineering teams' need to understand costs. The three examples discussed in this blog represent three significant roadblocks that create unnecessary work for engineering teams.

Any engineer can easily replace Cost Explorer for free by creating a Cost and Usage Report and doing your own SQL analysis. If there are any specific cost problems your team is fighting, let me know in the comments, and I'll add the analysis to the Developer's Guide to AWS Costs project!

Content powered by Strake