Match GCP Big Query (BQ) FlexReport 'consumption_in_units' to GCP console BQ export

Products

• CloudHealth

Issue/Introduction

The platform splits GCP BQ for on demand cost line item which includes various credits items to multiple line item in CH.

For example, BQ is queried for a line item N2 Americas cost it provides the data-{name:N2,cost:100,credits:{credit1:10,credit2:20}} and so on.  The platform splits this line item to three line items (as per number of credit in the BQ response), to calculate the credit from line-item but the cost remains same in all line-item as per the On-Demand Cost of the BQ line-item.

Resolution

In this example, 1 line-item cost becomes 3xcost.

When querying FlexReports (FR) 'Consumption In Units', it is important to not include or filter the credit line item cost (credit name is null) to get the actual On-Demand consumption of units, otherwise it show proportionately high as per the credit line items.

Please use the below query in FR(including (credit_name IS NULL)) to fetch the result.

{ "sqlStatement" : " SELECT timeInterval_Month AS Month, SUM(Consumption_In_Units) AS SUM_Consumption_In_Units, Sku_Description AS Sku_Description FROM GCP_BILLING_EXPORT WHERE ( (Sku_Description LIKE '%Core%') AND (Sku_Description NOT LIKE '%Commitment%') AND ( Sku_Description NOT LIKE '%Instance Core hosted on Sole Tenancy%' ) AND (Sku_Description NOT LIKE '%Reserved%') AND (Sku_Description NOT LIKE '%Sole Tenancy Premium%') AND (credit_name IS NULL) ) GROUP BY timeInterval_Month, Sku_Description ORDER BY SUM_Consumption_In_Units DESC ", "dataGranularity" : "MONTHLY" , "timeRange" : { "from": "2023-09", "to": "2023-09" } , "limit" : -1 }