[Hubs] Azure Hybrid Benefit columns are wrong for SQL Server and inconsistent across ingestion paths

[RolandKrummenacher]

🐛 Problem

The Azure Hybrid Benefit (AHB) columns added in hub 0.12 — x_SkuLicenseQuantity, x_SkuLicenseStatus, x_SkuLicenseType, x_SkuLicenseUnit — are computed in four independent places that have drifted apart. Three defects follow from that:

1. SQL Server gets the Windows Server 8-core minimum (should be 4).
2. Rows with no AHB eligibility get a license quantity on the FOCUS 1.0 ingestion path.
3. x_SkuLicenseStatus casing differs by ingestion path, and the dashboard filters on it case-sensitively.

Defect 1: SQL Server uses the Windows Server minimum of 8 cores

x_SkuLicenseQuantity applies a minimum of 8 core licenses to all license types. That is the Windows Server AHB rule; the SQL Server AHB minimum is 4 core licenses per VM. None of the four implementations branch on the license type — all use the same shape:

| extend x_SkuLicenseQuantity = case(
    isempty(x_SkuCoreCount) or isempty(x_SkuLicenseType), int(null),
    x_SkuCoreCount <= 8, int(8),
    x_SkuCoreCount > 8, x_SkuCoreCount,
    int(null)
)

This is a floor of 8, not a fixed value — above 8 cores the actual core count is used. So only SQL Server rows with 2–7 cores are affected:

x_SkuCoreCount	Hub reports	Correct for SQL Server	Delta
2	8	4	+4
4	8	4	+4
6	8	6	+2
≥ 8	= core count	= core count	0

Windows Server rows are correct today and must stay at a minimum of 8 — a global change to 4 would just move the error to Windows Server.

Defect 2: Non-AHB rows get a license quantity on the FOCUS 1.0 path

The copy in HubSetup_v1_2.kql (L162-L167) — the FOCUS 1.0 to 1.2 upgrade path inside Costs_v1_2() — omits the isempty(x_SkuLicenseType) guard that the ingestion copy has:

| extend x_SkuLicenseQuantity = case(
    isempty(x_SkuCoreCount), int(null),        // <-- no license type check
    x_SkuCoreCount <= 8, int(8),
    x_SkuCoreCount > 8, x_SkuCoreCount,
    int(null)
)

Every row with a core count therefore gets an x_SkuLicenseQuantity and x_SkuLicenseUnit = 'Cores', including Linux VMs with no AHB eligibility at all. That copy's x_SkuLicenseType detection is also narrower than the ingestion copy's (no x_SkuMeterCategory in ('Virtual Machines', 'Virtual Machine Licenses') / has 'Windows' branch), so simply adding the guard is not sufficient — the two type detections should be aligned.

Defect 3: x_SkuLicenseStatus casing differs by path, and consumers filter case-sensitively

The same status value is written with different casing depending on where it is computed:

Producer	Value written
IngestionSetup_v1_2.kql (L694) — FOCUS 1.2 ingestion	'Not Enabled'
HubSetup_v1_2.kql (L159) — FOCUS 1.0 upgrade	'Not enabled'
src/power-bi/storage/Shared.Dataset/.../Costs.tmdl (L1705)	'Not enabled', plus a third value 'Not supported' that no other path emits
src/queries/catalog/costs-enriched-base.kql (L56)	'Not enabled'

The hub dashboard filters this column with the case-sensitive == operator — three occurrences of x_SkuLicenseStatus == 'Not Enabled' and six of == 'Enabled' in dashboard.json. Rows coming through the FOCUS 1.0 upgrade path carry 'Not enabled' and therefore never match, so they are silently dropped from the Eligible resources and Eligible vCPU capacity tiles.

Per the repository content standard (sentence casing except proper nouns), 'Not enabled' is the correct spelling and the FOCUS 1.2 ingestion path is the outlier — which is unfortunately the path that writes the most data.

Downstream impact

Defect 1 is not only a column value. Both the ADX dashboard and the Power BI datasets derive unused-capacity metrics from it:

| extend x_SkuLicenseUnusedQuantity = x_SkuLicenseQuantity - x_SkuCoreCount

The AHB page of the hub dashboard surfaces this directly, so an inflated quantity inflates the reported waste. For a 2-vCPU SQL Server VM the dashboard reports 6 unused vCPUs where the correct figure is 2:

• Underutilized vCPU capacity (sum(x_SkuLicenseUnusedQuantity)) — overstated
• Unused capacity / Unused vCore hours in the resource detail table — overstated
• Covered vCPU capacity / Eligible vCPU capacity (sum(x_SkuLicenseQuantity)) — overstated

👣 Repro steps

Defect 1

1. Ingest FOCUS cost data containing SQL Server AHB usage on VMs with fewer than 8 vCPUs (for example Standard_D2s_v5 or Standard_D4s_v5).
2. Query the Costs function in the hub's ADX/Fabric database:

   Costs
   | where x_SkuLicenseType == 'SQL Server'
   | where x_SkuCoreCount < 8
   | distinct x_SkuInstanceType, x_SkuCoreCount, x_SkuLicenseQuantity, x_SkuLicenseUnit

3. Every row reports x_SkuLicenseQuantity = 8, regardless of the actual core count.
4. Open the Azure Hybrid Benefit page of the hub dashboard and compare Underutilized vCPU capacity against the true unused licenses for those resources.

Defect 2 — with FOCUS 1.0 data ingested, non-Windows/non-SQL rows carry a license quantity:

Costs
| where isempty(x_SkuLicenseType) and isnotempty(x_SkuLicenseQuantity)
| distinct x_SkuMeterCategory, x_SkuMeterSubcategory, x_SkuCoreCount, x_SkuLicenseQuantity

Defect 3 — both spellings coexist in the same table:

Costs
| summarize count() by x_SkuLicenseStatus

🤔 Expected

Defect 1 — apply the minimum that matches the license type:

• Windows Server: minimum 8 core licenses per VM (unchanged)
• SQL Server: minimum 4 core licenses per VM

The license type is already available at that point in the pipeline:

| extend x_SkuLicenseQuantity = case(
    isempty(x_SkuCoreCount) or isempty(x_SkuLicenseType), int(null),
    x_SkuLicenseType == 'SQL Server', max_of(x_SkuCoreCount, int(4)),
    max_of(x_SkuCoreCount, int(8))
)

Windows Server output stays bit-identical to today; only SQL Server rows with 2–7 cores change.

Defect 2 — x_SkuLicenseQuantity and x_SkuLicenseUnit should be empty when x_SkuLicenseType is empty, on every path. Align the x_SkuLicenseType detection between the two KQL copies at the same time.

Defect 3 — standardize every producer on the sentence-cased 'Not enabled'. Because the FOCUS 1.2 path has already written 'Not Enabled' into Costs_final_v1_2 (see the note on reprocessing below), consumers should also switch from == to =~ so both spellings keep matching during the transition. The 'Not supported' value emitted only by the storage Power BI dataset should either be adopted everywhere or dropped.

🔧 Environment

• FinOps hub version: 0.12 and later (the columns were added in 0.12; the logic is unchanged in 12.0, 13.0, 14.0 and latest)
• Cost Management export: FOCUS 1.0 and 1.2-preview (both ingestion paths are affected)
• Applies to: ADX/Data Explorer deployments, Fabric RTI deployments, the ADX dashboard, and both Power BI datasets (KQL and storage-based)

Note on reprocessing

Costs_final_v1_2 is a materialized table populated by an update policy from Costs_raw (IngestionSetup_v1_2.kql L1077), so that transform runs at ingestion time and existing rows keep the old values. Fixing the KQL only changes newly ingested data unless the data is reprocessed. The HubSetup_v1_2.kql copy lives in the Costs_v1_2() function, so that one takes effect at query time and applies retroactively to FOCUS 1.0 data.

📋 Files to update

The four implementations differ from each other, so this is not a single-line change:

• src/templates/finops-hub/modules/Microsoft.FinOpsHubs/Analytics/scripts/IngestionSetup_v1_2.kql (L685-L703) — the main FOCUS 1.2 transform (defects 1 and 3).
• src/templates/finops-hub/modules/Microsoft.FinOpsHubs/Analytics/scripts/HubSetup_v1_2.kql (L152-L168) — the FOCUS 1.0 to 1.2 upgrade path (defects 1, 2 and 3).
• src/templates/finops-hub/dashboard.json — three x_SkuLicenseStatus == 'Not Enabled' filters and six == 'Enabled' filters to switch to =~ (defect 3).
• src/power-bi/storage/Shared.Dataset/definition/tables/Costs.tmdl (L1698-L1705) — a Power Query (M) reimplementation of the same rules. This dataset has no x_SkuLicenseType column at all, only x_SkuLicenseStatus, so the SQL Server case has to be derived (from tmp_SQLAHB / x_SkuMeterSubcategory) before the minimum can be applied (defects 1 and 3).
• src/queries/catalog/costs-enriched-base.kql (L54-L62) — same status/type logic again, and it does not compute x_SkuLicenseQuantity at all (defect 3, plus a consistency check).
• src/queries/finops-hub-database-guide.md (L306-L313) — documents a different, older banding (8, then 16, then 24 for 20 cores, then actual) that no longer matches any shipped transform and has the same SQL Server issue. The surrounding x_SkuLicenseStatus / x_SkuLicenseType snippets have also drifted (different branch order, contains where the current code uses has).
• src/templates/agent-plugin/agents/ftk-database-query.agent.md (L150) — documents the value set as Enabled, Not enabled; confirm it matches after standardizing.
• Generated artifacts under release/ (finops-hub/modules/.../IngestionSetup_v1_2.kql, finops-hub/modules/.../HubSetup_v1_2.kql, finops-hub-fabric-setup-Ingestion.kql, finops-hub-fabric-setup-Hub.kql, finops-hub-dashboard.json) are rebuilt from the templates by Package-Toolkit.
• Consider consolidating the KQL copies so each rule lives in one place.
• Decide whether existing Costs_final_v1_2 data should be reprocessed (see the note above).

ℹ️ Additional context

Licensing references

• SQL Server AHB is subject to a minimum of four core licenses per virtual machine — Azure Hybrid Benefit for Windows Server, SQL Server, or Linux subscriptions, Expanded Azure Hybrid Benefit rights for Microsoft SQL Server
• Windows Server AHB requires a minimum of 8 core licenses per VM — Azure Hybrid Benefit for Windows Server

Known remaining limitation after the fix

For SQL Server, actual license consumption also depends on the edition (1 Enterprise core license covers 4 vCPUs of Standard edition on Azure VMs). The edition is not reliably available in the cost data, so x_SkuLicenseQuantity stays a vCPU-based approximation — just with the correct minimum.

🙋‍♀️ Ask for the community

We could use your help:

1. Please vote this issue up (👍) to prioritize it.
2. If you use SQL Server Azure Hybrid Benefit, let us know whether the 4-core minimum matches how you track license consumption.