Search code examples
azure-resource-graph

Azure Resource Graph Query to List Resource IDs/Names of Azure VMS without Backup Enabled

I am attempting to use a resource graph query to find all virtual machines that do not have backups enabled and display their associated resource IDs and the virtual machine names. So far, I can pull the virtual machine names, but having issues including the resource IDs as well. Here is the query I have so far:

// Azure Resource Graph Query
// Find all VMs that do NOT have Backup enabled
// Run query to see results.
recoveryservicesresources
| where type =~ 'Microsoft.RecoveryServices/vaults/backupFabrics/protectionContainers/protectedItems'
| where properties.dataSourceInfo.datasourceType =~ 'Microsoft.Compute/virtualMachines'
| project idBackupEnabled=properties.sourceResourceId
| extend name=strcat_array(array_slice(split(idBackupEnabled, '/'), 8, -1), '/')
| union (
    resources
    | where type =~ 'Microsoft.Compute/virtualMachines'
    | project name, id
)
| summarize countOfName = count() by name
| where countOfName == 1
| project-away countOfName

To get the id column to show up, I first tried referencing the id column in the summarize operator which works, but negates the project-away operator that I am using to remove the vms that show up in both the original tables.

I also tried adding a leftouter join at the end of the query as shown below:

| join kind=leftouter (
    resources
    | where type =~ 'Microsoft.Compute/virtualMachines'
    | project name, id 
) on name

However, with this approach I received the following error: Table resources was referenced as right table 2 times, which exceeded the limit of 1”

Solution

  • I transitioned away from the union operator to the join of kind leftouter to merge to two tables on the name column. The query below worked for me:

    // Azure Resource Graph Query
// Find all VMs that do NOT have Backup enabled
// Run query to see results.
resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| project name, id
| join kind=leftouter (
    recoveryservicesresources
    | where type =~ 'Microsoft.RecoveryServices/vaults/backupFabrics/protectionContainers/protectedItems'
    | where properties.dataSourceInfo.datasourceType =~ 'Microsoft.Compute/virtualMachines'
    | project idBackupEnabled=properties.sourceResourceId
    | extend name=strcat_array(array_slice(split(idBackupEnabled, '/'), 8, -1), '/')
) on name
| where isnull(idBackupEnabled)
| project-away idBackupEnabled
| project-away name1