|
WITH constants AS ( |
|
-- Set these values according to your cloud strategy |
|
SELECT |
|
0.75 AS ri_allocation_target, |
|
0.70 AS ri_allocation_range_lower_thresh, |
|
0.80 AS ri_allocation_range_upper_thresh, |
|
date_trunc('day', CURRENT_DATE - interval '4 days') AS analysis_day, |
|
-- https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/apply_ri.html |
|
8.0 AS base_size_normalization_factor |
|
), |
|
line_items AS ( |
|
-- get the base data set, filtered by time range, product, etc |
|
SELECT |
|
date_trunc('hour', lineitem_usagestartdate::timestamp) AS hour, |
|
lineitem_normalizedusageamount::float / |
|
(SELECT base_size_normalization_factor FROM constants) AS normalized_usage, |
|
product_region AS region, |
|
split_part(product_instancetype, '.', 1) AS instance_family, |
|
lineitem_lineitemtype AS lineitemtype |
|
FROM aws.cost_and_usage_201806 -- point this at your most recent cost and usage report |
|
WHERE lineitem_productcode = 'AmazonEC2' |
|
AND lineitem_lineitemtype IN ('Usage', 'DiscountedUsage') |
|
AND product_instancetype <> '' |
|
AND product_tenancy = 'Shared' -- we do not purchase RIs for Dedicated Instances |
|
AND date_trunc('day', lineitem_usagestartdate::timestamp) = (SELECT analysis_day FROM constants) |
|
), |
|
total_usage AS ( |
|
-- get the usage sum by region/instance/hour family for all instances |
|
SELECT hour, region, instance_family, SUM(normalized_usage) AS total_usage |
|
FROM line_items |
|
GROUP BY hour, region, instance_family |
|
), |
|
ri_usage AS ( |
|
-- get the usage sum by region/instance/hour for only reserved instances |
|
SELECT region, instance_family, SUM(normalized_usage) AS reservation_usage |
|
FROM line_items |
|
WHERE lineitemtype = 'DiscountedUsage' |
|
-- grab the most recent snapshot of normalized reservations, no need to aggregate here |
|
AND hour = (SELECT analysis_day FROM constants) + interval '23 hours' |
|
GROUP BY region, instance_family |
|
), |
|
minimums AS ( |
|
-- roll up into minimums by region/instance, and add zeroes |
|
SELECT |
|
region, |
|
instance_family, |
|
NVL(reservation_usage, 0.0) AS reservation_usage, |
|
MIN(total_usage) AS total_usage -- account for workloads that scale daily |
|
-- only purchase for usage troughs to avoid |
|
-- unused RIs |
|
FROM total_usage |
|
LEFT OUTER JOIN ri_usage USING(region, instance_family) |
|
GROUP BY region, instance_family, reservation_usage |
|
) |
|
-- do a little math |
|
SELECT |
|
region, |
|
instance_family, |
|
FLOOR(reservation_usage) AS normalized_reservations, |
|
FLOOR(total_usage) AS normalized_usage, |
|
FLOOR( |
|
CASE |
|
WHEN reservation_usage < (SELECT ri_allocation_range_lower_thresh FROM constants) * total_usage THEN |
|
(SELECT ri_allocation_target FROM constants) * total_usage - reservation_usage |
|
WHEN reservation_usage > (SELECT ri_allocation_range_upper_thresh FROM constants) * total_usage THEN |
|
(SELECT ri_allocation_target FROM constants) * total_usage - reservation_usage |
|
ELSE 0 |
|
END |
|
) AS to_purchase, |
|
TO_CHAR(100.0 * reservation_usage / total_usage, 'FM990D0%') AS ri_allocation |
|
FROM minimums |
|
ORDER BY region, instance_family |
This comment has been minimized.
Great Stuff! We had a similar tool up and running using the old DBR reporting so great to see how we can move this to the CUR.
I may be missing something but one thing you may want to consider in including in this solution is accounting for any RIs that are expiring as well.