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

Question

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”

Answer 1

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

Answer 2

I have reproduced in my environment and below are the expected results:

To get the id you can use below query.

Firstly, I have got some nulls and some values like below:

Then I have used | where isnull(idBackupEnabled) to get only nulls as below:

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
)
| where isnull(idBackupEnabled)

Output:

You can use project-away to remove the nulls column as below:

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
)
| where isnull(idBackupEnabled)
|project-away idBackupEnabled