Asset Queries

Azure Resource Graph and Log Analytics queries to report on a managed estate of virtual machines. They are written for the Azure Monitor data source in Grafana: the Resource Graph queries run in Azure Resource Graph mode, the last one runs in Logs mode against the Log Analytics workspace. You can also run them in the portal (Resource Graph Explorer, Log Analytics) when you remove the line that uses the dashboard variable ${vms}.

See Asset management for the strategy these queries support, and Managed VM module for the module.

Applies to the managed VM module

The queries use the tags that the managed VM module writes to the resource group of a configuration set. If your platform has no such module, use them as a pattern and replace the tag filter with the tags of your own tag set (Naming and tags). Test them on a small scope first.

How to identify a managed VM

This decides whether a report can be trusted, so it is worth being explicit. The module applies its platform tags to the resource group of a VM configuration set, unconditionally: source, vm_configuration, environment, os_type, vm_size, public_ip, shutdown_schedule_hours and one ownerN tag per configuration owner. The tags a customer defines go on the VM resources instead, and are optional.

So the reliable definition is:

A managed VM is a virtual machine whose resource group carries the source and vm_configuration tags applied by the module.

Every query below starts from that filter and joins to the resources, which is why they contain a resourcecontainers join. The alternatives are worse:

• Filtering on the VM name. The module names machines vm-<workload>-<configuration>-<env>-<location>-NN, but nothing stops a team from naming its own machines the same way, and the legacy naming variant (useOldVMNaming) does not follow the pattern.
• Filtering on tags of the VM itself. Those are the customer's tags and may be absent.
• Filtering on the maintenance configuration. It can be switched off per configuration set with disable_default_maintenance_configuration, so it misses machines.
• Relying only on the ${vms} dashboard variable. That is a display filter, not a scope filter. If the variable is populated from a broad query, or set to All, unmanaged resources appear in the result.

The ${vms} variable

Populate the dashboard variable from the same definition, so the variable itself only ever offers managed machines:

resourcecontainers
| where type =~ 'microsoft.resources/subscriptions/resourcegroups'
| where isnotempty(tags['source']) and isnotempty(tags['vm_configuration'])
| project rgId = tolower(id)
| join kind=inner (
    resources
    | where type =~ 'microsoft.compute/virtualmachines'
    | extend rgId = tolower(strcat('/subscriptions/', subscriptionId, '/resourcegroups/', resourceGroup))
    | project rgId, name
) on rgId
| distinct name
| order by name asc

In the queries below, the line with ${vms} (for example | where name in (${vms})) narrows the result to the dashboard selection. Remove that line to report on every managed machine regardless of the selection.

Resource Graph supports only a subset of KQL. let and bag_keys() are not available, and a query may contain at most three join and three mv-expand operators. The queries below stay within those limits.

1. Managed VM inventory

The active managed machines with their configuration, power state and platform tags.

resourcecontainers
| where type =~ 'microsoft.resources/subscriptions/resourcegroups'
| where isnotempty(tags['source']) and isnotempty(tags['vm_configuration'])
| project rgId = tolower(id),
    vmConfiguration = tostring(tags['vm_configuration']),
    environment = tostring(tags['environment']),
    source = tostring(tags['source']),
    owner1 = tostring(tags['owner1']),
    owner2 = tostring(tags['owner2'])
| join kind=inner (
    resources
    | where type =~ 'microsoft.compute/virtualmachines'
    | extend rgId = tolower(strcat('/subscriptions/', subscriptionId, '/resourcegroups/', resourceGroup))
    | extend
        powerState = tostring(properties.extended.instanceView.powerState.code),
        vmSize = tostring(properties.hardwareProfile.vmSize),
        osType = tostring(properties.storageProfile.osDisk.osType),
        imageOffer = tostring(properties.storageProfile.imageReference.offer),
        imageSku = tostring(properties.storageProfile.imageReference.sku)
    | project rgId, name, powerState, vmSize, osType, imageOffer, imageSku,
        resourceGroup, location, subscriptionId
) on rgId
| where name in (${vms})
| project vmname = name, powerState, vmSize, osType, imageOffer, imageSku,
    vmConfiguration, environment, source, owner1, owner2,
    resourceGroup, location, subscriptionId
| order by vmname asc

2. All assets of the managed configuration sets

Every resource that belongs to a managed configuration set, not only the machines — disks, network interfaces, key vault, vaults, data collection rules.

resourcecontainers
| where type =~ 'microsoft.resources/subscriptions/resourcegroups'
| where isnotempty(tags['source']) and isnotempty(tags['vm_configuration'])
| project rgId = tolower(id),
    vmConfiguration = tostring(tags['vm_configuration']),
    environment = tostring(tags['environment'])
| join kind=inner (
    resources
    | extend rgId = tolower(strcat('/subscriptions/', subscriptionId, '/resourcegroups/', resourceGroup))
    | project rgId, name, type, location, resourceGroup, subscriptionId
) on rgId
| project vmConfiguration, environment, resourceType = type, name,
    resourceGroup, location, subscriptionId
| order by vmConfiguration asc, resourceType asc, name asc

Add | summarize assetCount = count() by vmConfiguration, environment, resourceType for the counts per type instead of the individual resources.

3. Managed VMs per owner

The module writes one ownerN tag per configuration owner. bag_keys() is not available in Resource Graph, so the owner tags are packed into an array and expanded.

resourcecontainers
| where type =~ 'microsoft.resources/subscriptions/resourcegroups'
| where isnotempty(tags['source']) and isnotempty(tags['vm_configuration'])
| extend owners = pack_array(
    tostring(tags['owner1']), tostring(tags['owner2']),
    tostring(tags['owner3']), tostring(tags['owner4']))
| mv-expand owner = owners to typeof(string)
| where isnotempty(owner)
| project rgId = tolower(id), owner, vmConfiguration = tostring(tags['vm_configuration'])
| join kind=inner (
    resources
    | where type =~ 'microsoft.compute/virtualmachines'
    | extend rgId = tolower(strcat('/subscriptions/', subscriptionId, '/resourcegroups/', resourceGroup))
    | project rgId, name, location, subscriptionId
) on rgId
| project owner, vmname = name, vmConfiguration, location, subscriptionId
| order by owner asc, vmname asc

Extend the pack_array if a configuration set ever has more than four owners.

4. Tag coverage

Resource groups that hold virtual machines but are missing one or more platform tags. A configuration set deployed through the module always carries them, so any result here is a resource group that was created outside the pipeline.

resources
| where type =~ 'microsoft.compute/virtualmachines'
| extend rgId = tolower(strcat('/subscriptions/', subscriptionId, '/resourcegroups/', resourceGroup))
| summarize vmCount = count() by rgId
| join kind=inner (
    resourcecontainers
    | where type =~ 'microsoft.resources/subscriptions/resourcegroups'
    | project rgId = tolower(id), resourceGroup = name, subscriptionId, tags
) on rgId
| extend
    hasSource = isnotempty(tostring(tags['source'])),
    hasVmConfiguration = isnotempty(tostring(tags['vm_configuration'])),
    hasEnvironment = isnotempty(tostring(tags['environment'])),
    hasOsType = isnotempty(tostring(tags['os_type'])),
    hasVmSize = isnotempty(tostring(tags['vm_size'])),
    hasOwner = isnotempty(tostring(tags['owner1']))
| where not(hasSource and hasVmConfiguration and hasEnvironment and hasOsType and hasVmSize and hasOwner)
| project resourceGroup, subscriptionId, vmCount,
    hasSource, hasVmConfiguration, hasEnvironment, hasOsType, hasVmSize, hasOwner
| order by resourceGroup asc

5. Disks of the managed VMs

Storage as part of the asset record: size, SKU and encryption.

resourcecontainers
| where type =~ 'microsoft.resources/subscriptions/resourcegroups'
| where isnotempty(tags['source']) and isnotempty(tags['vm_configuration'])
| project rgId = tolower(id), vmConfiguration = tostring(tags['vm_configuration'])
| join kind=inner (
    resources
    | where type =~ 'microsoft.compute/disks'
    | extend rgId = tolower(strcat('/subscriptions/', subscriptionId, '/resourcegroups/', resourceGroup))
    | extend
        diskSizeGb = toint(properties.diskSizeGB),
        diskState = tostring(properties.diskState),
        skuName = tostring(sku.name),
        encryptionType = tostring(properties.encryption.type),
        attachedTo = tostring(split(tostring(properties.managedBy), '/', 8)[0])
    | project rgId, diskName = name, diskSizeGb, skuName, diskState,
        encryptionType, attachedTo, resourceGroup
) on rgId
| where isnotempty(attachedTo)
| where attachedTo in (${vms})
| project vmname = attachedTo, diskName, diskSizeGb, skuName, diskState,
    encryptionType, vmConfiguration, resourceGroup
| order by vmname asc, diskName asc

6. Backup protection state

Both vaults are deployed into the resource group of the configuration set, so the same tag filter applies. The Recovery Services Vault is the default and the Backup Vault is the opt-in, so the two are combined.

recoveryservicesresources
| where type =~ 'microsoft.recoveryservices/vaults/backupfabrics/protectioncontainers/protecteditems'
| extend
    vmname = tostring(properties.friendlyName),
    protectionState = tostring(properties.protectionState),
    lastBackupStatus = tostring(properties.lastBackupStatus),
    lastBackupTime = todatetime(properties.lastBackupTime),
    policyName = tostring(properties.policyName),
    vault = tostring(split(id, '/', 8)[0])
| project vmname, vault, policyName, protectionState, lastBackupStatus, lastBackupTime,
    subscriptionId, resourceGroup
| union (
    recoveryservicesresources
    | where type =~ 'microsoft.dataprotection/backupvaults/backupinstances'
    | extend
        vmname = tostring(properties.friendlyName),
        protectionState = tostring(properties.currentProtectionState),
        lastBackupStatus = tostring(properties.protectionStatus.status),
        lastBackupTime = todatetime(properties.latestRecoveryPointTime),
        policyName = tostring(properties.policyInfo.name),
        vault = tostring(split(id, '/', 8)[0])
    | project vmname, vault, policyName, protectionState, lastBackupStatus, lastBackupTime,
        subscriptionId, resourceGroup
)
| extend rgId = tolower(strcat('/subscriptions/', subscriptionId, '/resourcegroups/', resourceGroup))
| join kind=inner (
    resourcecontainers
    | where type =~ 'microsoft.resources/subscriptions/resourcegroups'
    | where isnotempty(tags['source']) and isnotempty(tags['vm_configuration'])
    | project rgId = tolower(id), vmConfiguration = tostring(tags['vm_configuration'])
) on rgId
| project vmname, vmConfiguration, vault, policyName, protectionState,
    lastBackupStatus, lastBackupTime
| order by vmname asc

7. Outstanding patches

The patch dashboard reports the last patch installation. This reports what is still outstanding, which is the other half of the picture.

patchassessmentresources
| where type =~ 'microsoft.compute/virtualmachines/patchassessmentresults'
| extend
    vmname = tostring(split(id, '/', 8)[0]),
    assessmentTime = todatetime(properties.startDateTime),
    osType = tostring(properties.osType),
    rebootPending = tostring(properties.rebootPending),
    criticalAndSecurityPatchCount = toint(properties.criticalAndSecurityPatchCount),
    otherPatchCount = toint(properties.otherPatchCount)
| extend rgId = tolower(strcat('/subscriptions/', subscriptionId, '/resourcegroups/', resourceGroup))
| join kind=inner (
    resourcecontainers
    | where type =~ 'microsoft.resources/subscriptions/resourcegroups'
    | where isnotempty(tags['source']) and isnotempty(tags['vm_configuration'])
    | project rgId = tolower(id), vmConfiguration = tostring(tags['vm_configuration'])
) on rgId
| where vmname in (${vms})
| summarize arg_max(assessmentTime, *) by vmname
| project vmname, vmConfiguration, assessmentTime, osType, rebootPending,
    criticalAndSecurityPatchCount, otherPatchCount
| order by criticalAndSecurityPatchCount desc

8. Network exposure

No managed VM has a public IP. This returns the managed machines that do, so the result should be empty.

resourcecontainers
| where type =~ 'microsoft.resources/subscriptions/resourcegroups'
| where isnotempty(tags['source']) and isnotempty(tags['vm_configuration'])
| project rgId = tolower(id), vmConfiguration = tostring(tags['vm_configuration'])
| join kind=inner (
    resources
    | where type =~ 'microsoft.network/networkinterfaces'
    | extend rgId = tolower(strcat('/subscriptions/', subscriptionId, '/resourcegroups/', resourceGroup))
    | mv-expand ipConfig = properties.ipConfigurations
    | extend
        publicIpId = tostring(ipConfig.properties.publicIPAddress.id),
        attachedTo = tostring(split(tostring(properties.virtualMachine.id), '/', 8)[0])
    | where isnotempty(publicIpId) and isnotempty(attachedTo)
    | project rgId, nic = name, publicIpId, attachedTo, resourceGroup, subscriptionId
) on rgId
| project vmname = attachedTo, vmConfiguration, nic, publicIpId, resourceGroup, subscriptionId

9. Recent changes to the managed VMs

Resource Graph keeps change history. Without the tag filter this returns changes across the whole tenant, so the join is what keeps it to the managed estate.

resourcechanges
| extend
    changeTime = todatetime(properties.changeAttributes.timestamp),
    changeType = tostring(properties.changeType),
    targetId = tostring(properties.targetResourceId),
    targetType = tostring(properties.targetResourceType),
    changedBy = tostring(properties.changeAttributes.changedBy)
| where changeTime > ago(30d)
| where targetType =~ 'microsoft.compute/virtualmachines'
| extend
    resourceName = tostring(split(targetId, '/', 8)[0]),
    rgId = tolower(strcat('/subscriptions/', subscriptionId, '/resourcegroups/', resourceGroup))
| join kind=inner (
    resourcecontainers
    | where type =~ 'microsoft.resources/subscriptions/resourcegroups'
    | where isnotempty(tags['source']) and isnotempty(tags['vm_configuration'])
    | project rgId = tolower(id), vmConfiguration = tostring(tags['vm_configuration'])
) on rgId
| project changeTime, vmname = resourceName, vmConfiguration, changeType, changedBy, targetId
| order by changeTime desc

Change targetType to report on another resource type, or drop that line to see every change inside the managed configuration sets.

10. Software inventory inside the machine

Change Tracking & Inventory writes into the Log Analytics Workspace. This query runs on the Azure Monitor datasource in Logs mode, not Resource Graph.

The module deploys a workspace per VM configuration set, so the workspace scope is already the managed estate and no tag filter is needed. let is available here, unlike in Resource Graph.

ConfigurationData
| where ConfigDataType == "Software"
| summarize arg_max(TimeGenerated, *) by Computer, SoftwareName, CurrentVersion
| project Computer, SoftwareName, CurrentVersion, Publisher, SoftwareType, TimeGenerated
| order by Computer asc, SoftwareName asc

Replace Software with WindowsServices, Daemons, Files or Registry for the other inventory types. ConfigurationChange holds the same data as a change history rather than a current state.

Related pages

• Asset management
• Managed VM module
• Monthly health check