Developer's Guide to AWS Costs

Raise your hand if you love managing #awscloud Costs..

No? Me neither. That's why I'm excited to share the Developer's Guide to AWS Costs on GitHub! 🚀

At Strake, we believe engineers should spend their time on feature development, not cost management.

That's why this project uses #sql code to analyze AWS Billing Data and allows engineers to get back to what really matters: BUILDING PRODUCT.

Creating Cost and Usage Reports and analyzing EC2 costs are the most common needs in our community. So we did that right away..

For example, maybe you need to isolate the costs of your EBS Volumes and NAT Gateways? We got you covered:

##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]);

##Subresource costs for NAT Gateways
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 '%natgateway%'
GROUP BY
    [lineItem/ResourceID],
    [lineitem/lineitemtype],
    [lineItem/Operation]
ORDER BY
    sum([lineItem/UnblendedCost]);

Next, maybe you need to know the hourly EC2 costs, by pricing model and region, for all the instance types your team is using? We've also got you covered:

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.[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.[product/instanceType],
    CUR.[product/region]
ORDER BY
    total_existence_cost;

This is a sample of what we have on our GitHub page with much more coming soon! We're going to dig into more AWS Services: RDS, Lambda, S3 as well as expanding on topics: EDP Commitments, Savings Plans, and RIs.

We'd love to get some feedback from this community on whether or not this product is helpful for you and your teams. If you are having a cost problem we haven't solved yet, let us know on our Roadmap Discussion and we will get to work. We love a challenge!

Stay up to date: sign up for our newsletter