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!
Top comments (0)