Calculate Azure reservation and savings plan usage and savings

Products

• CloudHealth

Issue/Introduction

FlexReports can be utilized to calculate usage and savings for Azure reservations and savings plans with the query below.

Resolution

{

"sqlStatement" :

" SELECT

timeInterval_Month AS Month,

SUM(CostInBillingCurrency) AS SUM_CostInBillingCurrency,

SUM(Quantity) AS SUM_Quantity,

UnitPrice AS UnitPrice,

ROUND(SUM(Quantity), 2) AS HoursCoveredBySP,

ROUND(UnitPrice * SUM(Quantity), 2) AS CostBeforeDiscount,

ROUND(SUM(CostInReportingCurrency), 2) AS SP_RI_Cost,

ROUND(

(

UnitPrice * SUM(Quantity) - SUM(CostInReportingCurrency)

),

2

) AS SP_RI_Discount,

ROUND(

(

(UnitPrice * SUM(Quantity)) - SUM(CostInReportingCurrency)

) / (UnitPrice * SUM(Quantity)) * 100,

2

) AS Savings_Percentage,

MetricType AS MetricType,

ResourceId AS ResourceId,

SubscriptionName AS SubscriptionName,

ResourceGroup AS ResourceGroup,

ResourceName AS ResourceName,

MeterCategory AS MeterCategory,

ProductName AS ProductName,

PricingModel AS PricingModel,

AccountName AS AccountName,

ConsumedService AS ConsumedService

FROM

AZURE_COST_USAGE

GROUP BY

timeInterval_Month,

Quantity,

CostInBillingCurrency,

SubscriptionName,

ResourceGroup,

ResourceId,

ProductName,

MeterCategory,

ResourceName,

UnitPrice,

MetricType,

PricingModel,

ConsumedService,

AccountName

ORDER BY

ConsumedService DESC ",

"needBackLinkingForTags" : true ,

"dataGranularity" : "MONTHLY" ,

"timeRange" : {

"last": 1,

"excludeCurrent": true

} ,

"limit" : -1

}