Usage

Savings Plans, Reserved Instance, On Demand and Spot Usage

To improve the utilization of cost models for businesses, we provide here a few examples that highlight scenarios using EC2 as Savings Plans or Reserved Instance effectively when compared to On Demand billing.

1. Display the accounts using EC2 as Reserved Instance and the cost they would pay if calculated at public rates. This is extremely useful for the organization in calculating chargeback.

SELECT "bill_payer_account_id",
	"bill_billing_period_start_date",
	"line_item_usage_account_id",
	"reservation_reservation_a_r_n",
	"line_item_product_code",
	"line_item_usage_type",
	sum("line_item_usage_amount") as Usage,
	"line_item_unblended_rate",
	sum("line_item_unblended_cost") as Cost,
	"line_item_line_item_description",
	"pricing_public_on_demand_rate",
	sum("pricing_public_on_demand_cost") as PublicCost
FROM "costmaster"."monthly_report"
WHERE "line_item_line_item_Type" like '%DiscountedUsage%'
GROUP BY "bill_payer_account_id",
	"bill_billing_period_start_date",
	"line_item_usage_account_id",
	"reservation_reservation_a_r_n",
	"line_item_product_code",
	"line_item_usage_type",
	"line_item_unblended_rate",
	"line_item_line_item_description",
	"pricing_public_on_demand_rate"
LIMIT 20

2. Query returned results

3. Display the cost for each usage type belonging to the t2 family

SELECT "line_item_usage_type",
	sum("line_item_usage_amount") as usage,
	round(sum("line_item_unblended_cost"), 2) as cost
FROM "costmaster"."monthly_report"
WHERE "line_item_usage_type" like '%t2.%'
GROUP BY "line_item_usage_type"
ORDER BY "line_item_usage_type"
LIMIT 20

4. Query returned results

5. Display the hourly cost for each usage type

SELECT "line_item_usage_type",
	round(sum("line_item_usage_amount"), 2) as usage,
	round(sum("line_item_unblended_cost"), 2) as cost,
	round(
		avg(
			"line_item_unblended_cost" / "line_item_usage_amount"
		),
		4
	) as hourly_rate
FROM "costmaster"."monthly_report"
WHERE "line_item_product_code" like '%AmazonEC2%'
	and "line_item_usage_type" like '%Usage%'
GROUP BY "line_item_usage_type"
ORDER BY "line_item_usage_type"
LIMIT 20

6. Query returned results

7. Display the number of unused Reserved Instances

SELECT bill_billing_period_start_date,
	product_region,
	line_item_usage_type,
	reservation_reservation_a_r_n,
	reservation_unused_quantity,
	reservation_unused_recurring_fee
FROM "costmaster"."monthly_report"
WHERE length(reservation_reservation_a_r_n) > 0
	and reservation_unused_quantity > 0
ORDER BY bill_billing_period_start_date,
	reservation_unused_recurring_fee desc
LIMIT 20

8. Query returned results