Tag: CloudOps

Audit Azure VM Storage and Ownership Fast with this KQL Query

Ever

Ever been asked to pull a report of specific Azure VMs, their OS versions, who owns them, and exactly how much storage they are chewing up? Clicking through the Azure Portal to piece this together is a massive time sink.

This is where Azure Resource Graph (ARG) shines. I use the KQL query below whenever I need to quickly audit environments for cost allocation, cleanup projects, or basic inventory tracking.

Why this query is useful:

  • Calculates real storage: It grabs the OS disk and iterates through all attached data disks to give you a true TotalCombinedStorageGB metric. It also safely handles edge cases, like VMs with zero data disks, without dropping them from the report.
  • Makes it readable: It joins backend tables to swap the ugly Subscription ID for the human-readable Subscription Name.
  • Filters like a pro: It extracts the Owner tag and the OSVersion, allowing you to easily filter for specific teams or operating systems using fuzzy matches and lists.

How to use it:

  1. Open Resource Graph Explorer in the Azure Portal.
  2. Paste the KQL query below.
  3. Update the YOUR FILTERS section with your target OS and Owner names.
  4. Hit Run and export your clean table directly to a CSV!
Resources
| where type =~ 'microsoft.compute/virtualmachines'
| extend VMName = tostring(name)
| extend Region = tostring(location)
| extend ResourceGroup = tostring(resourceGroup)
| extend OSType = tostring(properties.storageProfile.osDisk.osType)
| extend OSVersion = strcat(tostring(properties.storageProfile.imageReference.offer), " ", tostring(properties.storageProfile.imageReference.sku))
| extend Owner = tostring(tags['Owner'])
// --- YOUR FILTERS ---
| where OSVersion contains "<ENTER_OS_VERSION_HERE>"
// The in~ operator checks against a list of values
| where Owner in~ ("<OWNER_1>", "<OWNER_2>", "<OWNER_3>")
// --------------------
| extend OSDiskSizeGB = toint(properties.storageProfile.osDisk.diskSizeGB)
| extend OSDiskSizeGB = iff(isnull(OSDiskSizeGB), 0, OSDiskSizeGB)
| extend DataDisks = properties.storageProfile.dataDisks
| extend DataDisks = iff(isnull(DataDisks) or array_length(DataDisks) == 0, dynamic([{"diskSizeGB": 0}]), DataDisks)
| mv-expand DataDisks
| extend DataDiskSizeGB = toint(DataDisks.diskSizeGB)
| extend DataDiskSizeGB = iff(isnull(DataDiskSizeGB), 0, DataDiskSizeGB)
| summarize TotalDataDiskSizeGB = sum(DataDiskSizeGB) by id, VMName, ResourceGroup, subscriptionId, Region, OSType, OSVersion, Owner, OSDiskSizeGB
| extend TotalCombinedStorageGB = OSDiskSizeGB + TotalDataDiskSizeGB
| join kind=leftouter (
    ResourceContainers
    | where type =~ 'microsoft.resources/subscriptions'
    | project subscriptionId, SubscriptionName = tostring(name)
) on subscriptionId
| project
    VMName,
    ResourceGroup,
    SubscriptionName,
    Region,
    OSType,
    OSVersion,
    Owner,
    OSDiskSizeGB,
    TotalDataDiskSizeGB,
    TotalCombinedStorageGB
| order by VMName asc