Finding VMs which are not being backed up

A client had a bunch of Virtual Machines and wanted to ensure that they were all being backed up. They had recently installed Cloud Adoption Framework and various policies including one for ensuring this, however, they initially wanted to quickly know how many VMs were affected so we wrote this KQL to identify them...

resources
| where type in~ ('microsoft.compute/virtualmachines','microsoft.classiccompute/virtualmachines') 
| extend resourceId=tolower(id) 
| join kind = leftouter (resourcecontainers | where type=='microsoft.resources/subscriptions' | project SubscriptionName=name,subscriptionId) on subscriptionId
| join kind = leftouter (RecoveryServicesResources
|                        where type == "microsoft.recoveryservices/vaults/backupfabrics/protectioncontainers/protecteditems"
|                        where properties.backupManagementType == "AzureIaasVM"
|                        project resourceId = tolower(tostring(properties.sourceResourceId))
                         ,       backupItemid = id
                         ,       isBackedUp = isnotempty(id)
                         ,       currentProtectionState = properties.currentProtectionState ) on resourceId 
| extend isProtected = isnotempty(backupItemid)
| where (isProtected == (0) or currentProtectionState == 'ProtectionStopped')
| project SubscriptionName,subscriptionId,resourceName = name,location
| join kind=leftouter (
    resources
    | where type in~ ('microsoft.compute/virtualmachines','microsoft.classiccompute/virtualmachines')
    | extend resourceId=tolower(id) 
    | mvexpand tags
    | extend tagKey = tostring(bag_keys(tags)[0])
    | extend tagValue = tostring(tags[tagKey])
    | where (tagKey == 'Exclude From Backup')
    | where not (tags == '')
    | project resourceName = name,resourceName2 = name)
on resourceName
| where resourceName2 == ''
| project SubscriptionName,subscriptionId,resourceName,location
| order by resourceName,SubscriptionName

In this particular case the client had a method of having a Tag on their VMs to exclude certain VMs from being backed up (where they were easier to replace via code and pipelines) so there is a little extra code in there to check for the presence of the Tag (Exclude From Backup).

The query was saved into the Graph Queries library and it's results emailed out periodically to admins to ensure that nothing got missed.

Clearly, having a backup is one thing but recovery is what matters most - make sure you regularly test your backups!