KQL Query to determine orphaned VM Backups

Question

I have been attempting to identify VM Backups that have been orphaned by the deletion of their VM they are backing up and am putting together a workbook to track these resources. I have been working on a query to pull them but so far can not get it to properly filter out the backups that have no associated VM by name. Below is the query I have been using which is an attempt to invert a more common query to determine VMs that aren't being backed up. Any help would be appreciated, thank you. It currently pulls basically all resources and isn't filtering as anticipated, especially with the limits imposed on these queries I have hit a wall.

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), '/') 
	| join kind=leftouter ( 
		resources
		| where type =~ 'Microsoft.Compute/virtualMachines'
		| project name, id, resourceGroup, location
	) on name
	| where isnotnull(idBackupEnabled)
	| project-away idBackupEnabled

Answer 1

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Issue:

I have been attempting to identify VM Backups that have been orphaned by the deletion of their VM they are backing up and am putting together a workbook to track these resources. I have been working on a query to pull them but so far can not get it to properly filter out the backups that have no associated VM by name.

Solution (provided by @Liam Hosfeld )

RecoveryServicesResources | where type in~ ('Microsoft.RecoveryServices/vaults/backupFabrics/protectionContainers/protectedItems')|extend policy = properties.policyInfo.policyId| extend vaultName = case(type in~ ('microsoft.dataprotection/backupVaults/backupInstances', 'microsoft.dataprotection/backupVaults/deletedBackupInstances'),split(split(id, '/Microsoft.DataProtection/backupVaults/')[1],'/')[0],type =~ 'Microsoft.RecoveryServices/vaults/backupFabrics/protectionContainers/protectedItems',split(split(id, '/Microsoft.RecoveryServices/vaults/')[1],'/')[0],'--')| extend vaultId = case(type in~ ('microsoft.dataprotection/backupVaults/backupInstances', 'microsoft.dataprotection/backupVaults/deletedBackupInstances'),tolower(split(id, '/backupInstances/')[0]),type =~ 'Microsoft.RecoveryServices/vaults/backupFabrics/protectionContainers/protectedItems',tolower(split(id, '/backupFabrics/')[0]),'--')| extend dataSourceType = case(type =~ 'Microsoft.RecoveryServices/vaults/backupFabrics/protectionContainers/protectedItems',strcat(properties.backupManagementType,'/',properties.workloadType),type in~ ('microsoft.dataprotection/backupVaults/backupInstances', 'microsoft.dataprotection/backupVaults/deletedBackupInstances'),properties.dataSourceInfo.datasourceType,'--')| extend friendlyName = properties.friendlyName| extend dsResourceGroup = split(split(properties.dataSourceInfo.resourceID, '/resourceGroups/')[1],'/')[0]| extend dsSubscription = split(split(properties.dataSourceInfo.resourceID, '/subscriptions/')[1],'/')[0]| extend lastRestorePoint = case(isnull(properties.lastRecoveryPoint), properties.lastBackupTime, properties.lastRecoveryPoint)| extend primaryLocation = properties.dataSourceInfo.resourceLocation| extend parentType = properties.parentType| extend instanceName = case(isnotnull(properties.serverName),strcat(properties.serverName,'/', properties.parentName), 'NA')| extend policyName = case(type in~ ('microsoft.dataprotection/backupVaults/backupInstances', 'microsoft.dataprotection/backupVaults/deletedBackupInstances'), extract(@'([^/]*)/backupPolicies/([^/]*)', 2, tostring(policy)),type =~ 'Microsoft.RecoveryServices/vaults/backupFabrics/protectionContainers/protectedItems', properties.policyInfo.name,'--')| extend protectionState = properties.currentProtectionState| extend managedIdentity = case((properties.identityDetails.useSystemAssignedIdentity == false), 'userassigned', 'systemassigned')| project id, name,type, resourceGroup, vaultName, friendlyName, instanceName, subscriptionId, dataSourceType, protectionState, policyName, primaryLocation, lastRestorePoint, properties, dsResourceGroup, dsSubscription, location, parentType, managedIdentity, resourceId =
                    case(
                        type in~ ('microsoft.dataprotection/backupVaults/backupInstances', 'microsoft.dataprotection/backupVaults/deletedBackupInstances'),
                            tolower(tostring(properties.dataSourceInfo.resourceID)),
                        type =~ 'Microsoft.RecoveryServices/vaults/backupFabrics/protectionContainers/protectedItems',
                            tolower(tostring(properties.sourceResourceId)),
                        '--')
                        | where isnotnull(resourceId) and isnotempty(resourceId)
                    | join kind = leftouter (Resources | where type in~ ('microsoft.compute/virtualmachines','microsoft.classiccompute/virtualmachines')
                        | extend resourceId = tolower(id), backupItemId = id) on resourceId
                    | extend Resource_Exists = isnotempty(backupItemId)
                    | extend extendedLocationId=tolower(extendedLocation.name)
                    | extend extendedLocationName=split(extendedLocationId, "/customlocations/", 1)[0]
                    | where (Resource_Exists == (0))| where (protectionState in~ ('ConfiguringProtection','ProtectionConfigured','ConfiguringProtectionFailed','ProtectionStopped','BackupsSuspended','SoftDeleted','ProtectionError')) and (dataSourceType in~ ('AzureIaasVM/VM'))

Answer 2

Hello, the below query accomplishes the goal, very beautiful...

RecoveryServicesResources | where type in~ ('Microsoft.RecoveryServices/vaults/backupFabrics/protectionContainers/protectedItems')|extend policy = properties.policyInfo.policyId| extend vaultName = case(type in~ ('microsoft.dataprotection/backupVaults/backupInstances', 'microsoft.dataprotection/backupVaults/deletedBackupInstances'),split(split(id, '/Microsoft.DataProtection/backupVaults/')[1],'/')[0],type =~ 'Microsoft.RecoveryServices/vaults/backupFabrics/protectionContainers/protectedItems',split(split(id, '/Microsoft.RecoveryServices/vaults/')[1],'/')[0],'--')| extend vaultId = case(type in~ ('microsoft.dataprotection/backupVaults/backupInstances', 'microsoft.dataprotection/backupVaults/deletedBackupInstances'),tolower(split(id, '/backupInstances/')[0]),type =~ 'Microsoft.RecoveryServices/vaults/backupFabrics/protectionContainers/protectedItems',tolower(split(id, '/backupFabrics/')[0]),'--')| extend dataSourceType = case(type =~ 'Microsoft.RecoveryServices/vaults/backupFabrics/protectionContainers/protectedItems',strcat(properties.backupManagementType,'/',properties.workloadType),type in~ ('microsoft.dataprotection/backupVaults/backupInstances', 'microsoft.dataprotection/backupVaults/deletedBackupInstances'),properties.dataSourceInfo.datasourceType,'--')| extend friendlyName = properties.friendlyName| extend dsResourceGroup = split(split(properties.dataSourceInfo.resourceID, '/resourceGroups/')[1],'/')[0]| extend dsSubscription = split(split(properties.dataSourceInfo.resourceID, '/subscriptions/')[1],'/')[0]| extend lastRestorePoint = case(isnull(properties.lastRecoveryPoint), properties.lastBackupTime, properties.lastRecoveryPoint)| extend primaryLocation = properties.dataSourceInfo.resourceLocation| extend parentType = properties.parentType| extend instanceName = case(isnotnull(properties.serverName),strcat(properties.serverName,'/', properties.parentName), 'NA')| extend policyName = case(type in~ ('microsoft.dataprotection/backupVaults/backupInstances', 'microsoft.dataprotection/backupVaults/deletedBackupInstances'), extract(@'([^/]*)/backupPolicies/([^/]*)', 2, tostring(policy)),type =~ 'Microsoft.RecoveryServices/vaults/backupFabrics/protectionContainers/protectedItems', properties.policyInfo.name,'--')| extend protectionState = properties.currentProtectionState| extend managedIdentity = case((properties.identityDetails.useSystemAssignedIdentity == false), 'userassigned', 'systemassigned')| project id, name,type, resourceGroup, vaultName, friendlyName, instanceName, subscriptionId, dataSourceType, protectionState, policyName, primaryLocation, lastRestorePoint, properties, dsResourceGroup, dsSubscription, location, parentType, managedIdentity, resourceId =
                    case(
                        type in~ ('microsoft.dataprotection/backupVaults/backupInstances', 'microsoft.dataprotection/backupVaults/deletedBackupInstances'),
                            tolower(tostring(properties.dataSourceInfo.resourceID)),
                        type =~ 'Microsoft.RecoveryServices/vaults/backupFabrics/protectionContainers/protectedItems',
                            tolower(tostring(properties.sourceResourceId)),
                        '--')
                        | where isnotnull(resourceId) and isnotempty(resourceId)
                    | join kind = leftouter (Resources | where type in~ ('microsoft.compute/virtualmachines','microsoft.classiccompute/virtualmachines')
                        | extend resourceId = tolower(id), backupItemId = id) on resourceId
                    | extend Resource_Exists = isnotempty(backupItemId)
                    | extend extendedLocationId=tolower(extendedLocation.name)
                    | extend extendedLocationName=split(extendedLocationId, "/customlocations/", 1)[0]
                    | where (Resource_Exists == (0))| where (protectionState in~ ('ConfiguringProtection','ProtectionConfigured','ConfiguringProtectionFailed','ProtectionStopped','BackupsSuspended','SoftDeleted','ProtectionError')) and (dataSourceType in~ ('AzureIaasVM/VM'))

Answer 3

Hi Liam ,

It looks like you are trying to identify VM backups that have become orphaned due to the deletion of their associated VM. The issue might be with the join condition and filtering. Try modifying the query as follows:

kusto

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), '/')      | join kind=leftouter (          resources         | where type =~ 'Microsoft.Compute/virtualMachines'         | project name, id, resourceGroup, location     ) on $left.name == $right.name     | where isnull(id)     | project-away idBackupEnabled

This query performs a left outer join and then filters out the rows where there is no match in the right side, which should help identify orphaned VM backups. Regards, Saravanan Ganesan.

Answer 4

Hello, @Liam Hosfeld ! We received your feedback and want to make sure that your question has been answered.

How do I find and delete orphaned VM disks or other resources?

Official approach:

When you delete a virtual machine (VM) in Azure, any disks that are attached to the VM aren't deleted by default. This helps to prevent data loss due to the unintentional deletion of VMs. After a VM is deleted, however, you will continue to pay for unattached disks. The following documentation shows you how to find and delete any unattached disks to reduce unnecessary costs using Azure CLI (Linux and VMSS only), Azure PowerShell, or the Portal.

Notice in the PowerShell example that ManagedBy equals null for managed disks and page blob LeaseStatus equals Unlocked for unmanaged disks.

Kusto Query:

If you want to use a Kusto Query, there is a Geeks for Geeks blog that goes through this process in detail:

https://www.geeksforgeeks.org/microsoft-azure-find-orphaned-disks/

Resources
| where type has "microsoft.compute/disks"
| extend diskState = tostring(properties.diskState)
| where  diskState == 'Unattached' or managedBy == ""
| project name, diskState, managedBy, subscriptionId, resourceGroup, location

Using a workbook:

You can also leverage an existing workbook designed to find all orphaned Azure resources, including orphaned disks:

https://techcommunity.microsoft.com/t5/fasttrack-for-azure/azure-orphan-resources/ba-p/3492198

---

I hope this has been helpful! Your feedback is important so please take a moment to let us know that your question has been addressed by accepting answers. If you still have questions, please let us know what is needed in the comments so the question can be answered. Thank you for helping to improve Microsoft Q&A!