Quantify usage of a component resource

This query uses the ProviderName column. ProviderName is deprecated in v1.3 and will be removed in v1.4.

FOCUS Versions

v1.2

Context

Capture daily aggregate usage of component resources, such as core hours or gigabytes, when Providers bill by virtual machines or quantity provisioned. This approach allows FinOps Practitioners to account for scenarios such as analyzing usage across auto-scaled compute services when instantaneous APIs cannot capture historical trends.

FOCUS SQL Query

SELECT
    ProviderName,
    ServiceName,
    PricingUnit,
    RegionName,
    JSON_UNQUOTE(JSON_EXTRACT(SkuPriceDetails, '$.InstanceSeries')) AS InstanceSeries, 
    SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(SkuPriceDetails, '$.CoreCount')) AS UNSIGNED)) AS TotalCoreCount
FROM focus_data_table
WHERE ChargePeriodStart >= ? and ChargePeriodEnd  < ?
AND JSON_CONTAINS_PATH(SkuPriceDetails, 'all', '$.CoreCount', '$.InstanceSeries')
GROUP BY
    ProviderName,
    ServiceName,
    PricingUnit,
    RegionName,
    InstanceSeries