Resource Graph Queries for AHUB on SQL VMs

AHUB for PaaS.kql

resources
| where type == "microsoft.sql/servers/databases" or type == "microsoft.sql/managedinstances" or type == "microsoft.sql/servers/elasticpools"
| where properties.licenseType == "LicenseIncluded"
| extend SqlEdition = case(sku.name hasprefix_cs "GP_", "Standard", sku.name hasprefix_cs "HS_", "Standard", sku.name hasprefix_cs "BC_", "Enterprise", "Unknown")
| extend vCores = toint(sku.capacity)
| extend licensesNeeded = toint(round(vCores/2))
| project licensesNeeded, vCores, SqlEdition
| summarize sum(licensesNeeded) by SqlEdition

AHUB for VMs.kql

Resources
| where type == "microsoft.sqlvirtualmachine/sqlvirtualmachines" and properties.sqlServerLicenseType == "PAYG"
| extend ServerName = tostring(split(properties.virtualMachineResourceId, "/")[-1])
| extend vmResourceId = tolower(tostring(properties.virtualMachineResourceId))
| join kind = leftouter (
    Resources
    | where type == "microsoft.compute/virtualmachines"
    | extend vCoresArray = extract_all(@"(\d+)", tostring(properties.hardwareProfile.vmSize))
    | project vmid = tolower(id), vCores = iff(tostring(properties.hardwareProfile.vmSize) contains "-", toint(vCoresArray[1]), toint(vCoresArray[0]))
)
on $left.vmResourceId == $right.vmid
| extend SqlEdition = tostring(properties.sqlImageSku)
| extend licensesNeeded = iff(vCores >= 4, toint(round(vCores/2)), 2)
| project ServerName, licensesNeeded, vCores, SqlEdition
| summarize sum(licensesNeeded) by SqlEdition

SQL AHUB Detail List.kql

resources
| where (type == "microsoft.sqlvirtualmachine/sqlvirtualmachines" and properties.sqlServerLicenseType == "PAYG")
    or ((type == "microsoft.sql/servers/databases" or type == "microsoft.sql/managedinstances" or type == "microsoft.sql/servers/elasticpools") and properties.licenseType == "LicenseIncluded")
| extend Name = tostring(
    coalesce(
        split(properties.virtualMachineResourceId, "/")[-1], 
        split(properties.containerResourceId, "/")[-1],
        name
    )
)
| extend vmResourceId = tolower(tostring(properties.virtualMachineResourceId))
| extend vCoresArc = toint(properties.vCore)
| extend vCoresPaaS = toint(sku.capacity)
| join kind = leftouter (
    resources
    | where type == "microsoft.compute/virtualmachines"
    | extend vCoresArray = extract_all(@"(\d+)", tostring(properties.hardwareProfile.vmSize))
    | project vmid = tolower(id), vCoresVm = iff(tostring(properties.hardwareProfile.vmSize) contains "-", toint(vCoresArray[1]), toint(vCoresArray[0])), vmSize = tostring(properties.hardwareProfile.vmSize)
)
on $left.vmResourceId == $right.vmid
| extend vCores = coalesce(vCoresArc, vCoresVm, vCoresPaaS)
| extend SqlEdition = tostring(coalesce(properties.sqlImageSku, properties.edition, case(sku.name hasprefix_cs "GP_", "Standard", sku.name hasprefix_cs "HS_", "Standard", sku.name hasprefix_cs "BC_", "Enterprise", "Unknown")))
| extend licensesNeeded = iff(
    (type == "microsoft.sql/servers/databases" or type == "microsoft.sql/managedinstances" or type == "microsoft.sql/servers/elasticpools"),
    toint(round(vCores/2)),
    iff(vCores >= 4, toint(round(vCores/2)), 2)
)
| project id, vmSize, licensesNeeded, vCores, SqlEdition

Windows License Needs

resources
| where type == "microsoft.compute/virtualmachines"
| where properties.licenseType == "Windows_Server"
| extend vCoresArray = extract_all(@"(\d+)", tostring(properties.hardwareProfile.vmSize))
| project id = tolower(id), type, vCoresVm = iff(tostring(properties.hardwareProfile.vmSize) contains "-", toint(vCoresArray[1]), toint(vCoresArray[0]))
| extend licensesNeeded = iff(vCoresVm < 8, 4, vCoresVm/2)