CloudHealth New Experience - Flex report for Negotiated Discounts (AWS)

Products

• CloudHealth

Issue/Introduction

Retrieve AWS Negotiated Discounts and reconcile them with realized savings in the CloudHealth New Experience.

Resolution

{
  "sqlStatement": " WITH \"cxtemp_TotalDiscount\" AS ( SELECT timeInterval_Month AS Month, SUM(lineItem_UnblendedCost) AS SUM_lineItem_UnblendedCost, SUM(pricing_publicOnDemandCost) AS SUM_pricing_publicOnDemandCost, SUM(lineItem_NetUnblendedCost) AS SUM_lineItem_NetUnblendedCost, SUM(reservation_NetEffectiveCost) AS SUM_reservation_NetEffectiveCost, SUM(savingsPlan_NetSavingsPlanEffectiveCost) AS SUM_savingsPlan_NetSavingsPlanEffectiveCost, SUM( CASE WHEN lineItem_LineItemType = 'DiscountedUsage' THEN ( pricing_publicOnDemandCost - reservation_NetEffectiveCost ) WHEN lineItem_LineItemType = 'SavingsPlanCoveredUsage' THEN ( lineItem_UnblendedCost - savingsPlan_NetSavingsPlanEffectiveCost ) WHEN lineItem_LineItemType = 'Usage' THEN ( lineItem_UnblendedCost - lineItem_NetUnblendedCost ) ELSE 0 END ) AS TotalDiscount FROM AWS_CUR WHERE ( (lineItem_LineItemType IN ('DiscountedUsage')) OR (lineItem_LineItemType IN ('Usage')) OR ( lineItem_LineItemType IN ('SavingsPlanCoveredUsage') ) ) GROUP BY timeInterval_Month ), \"cxtemp_commitmentDiscount\" AS ( SELECT timeInterval_Month AS Month, SUM(lineItem_UnblendedCost) AS SUM_lineItem_UnblendedCost, SUM(reservation_EffectiveCost) AS SUM_reservation_EffectiveCost, SUM(savingsPlan_SavingsPlanEffectiveCost) AS SUM_savingsPlan_SavingsPlanEffectiveCost, SUM(pricing_publicOnDemandCost) AS SUM_pricing_publicOnDemandCost, SUM( CASE WHEN lineItem_LineItemType = 'DiscountedUsage' THEN pricing_publicOnDemandCost - reservation_EffectiveCost ELSE pricing_publicOnDemandCost - savingsPlan_SavingsPlanEffectiveCost END ) AS Committed_Discount FROM AWS_CUR WHERE ( (lineItem_LineItemType IN ('DiscountedUsage')) OR ( lineItem_LineItemType IN ('SavingsPlanCoveredUsage') ) ) GROUP BY timeInterval_Month ORDER BY Month ASC ) SELECT cxtemp_TotalDiscount.Month, TotalDiscount, Committed_Discount, Round( ( ROUND(TotalDiscount, 2) - ROUND(Committed_Discount, 2) ), 2 ) as NegotiatedDiscount from cxtemp_TotalDiscount join cxtemp_commitmentDiscount on cxtemp_TotalDiscount.Month = cxtemp_commitmentDiscount.Month ",
  "needBackLinkingForTags": true,
  "dataGranularity": "MONTHLY",
  "timeRange": {
    "last": 11,
    "excludeCurrent": true
  },
  "limit": -1
}