Microsoft Azure – Create Pie Charts For Orphaned Resources using KQL

Workbooks allow monitoring data to be presented as charts. It supports charts for both logs and metric data sources. Orphaned resources are those resources that are not handled when an associate VM is deleted. To learn more about querying Azure resources using KQL, take a look at this article.

In this article, we will look into the process of representing orphaned Resources using KQL through workbooks:

Implementations:

Follow the below queries to implement the problem statement:

Note: You can use Workbooks to Save the Graph Queries and You can Pin to Dashboard for Analysis.

1. To get the Count of Azure Orphaned Disks by Subscription Category:

KQL Azure Resource Graph Query:

Resources
| where type has "microsoft.compute/disks"
| extend diskState = tostring(properties.diskState)
| where  diskState == 'Unattached' or managedBy == ""
| extend SubscriptionName=case(subscriptionId =~ 'Add Subscription 1 Id here ', 'Add Subscription 1 Name Here', 
subscriptionId =~ 'Add Subscription 2 Id here ', 'Add Subscription 2 Name Here'
subscriptionId =~ 'Add Subscription 3 Id here ', 'Add Subscription 3 Name Here'
,subscriptionId) // You can in similar way to add more
| summarize count() by SubscriptionName

Output:

2. To get the Count of Azure Orphaned NICs by Subscription Category:

KQL Azure Resource Graph Query:

Resources
| where type has "microsoft.network/networkinterfaces" 
| where properties !has 'virtualmachine'
| extend SubscriptionName=case(subscriptionId =~ 'Add Subscription 1 Id here ', 'Add Subscription 1 Name Here', 
subscriptionId =~ 'Add Subscription 2 Id here ', 'Add Subscription 2 Name Here'
subscriptionId =~ 'Add Subscription 3 Id here ', 'Add Subscription 3 Name Here'
,subscriptionId) // You can in similar way to add more
| summarize count() by SubscriptionName

Output:

3. To get the Count of Azure Orphaned NSGs by Subscription Category:

KQL Azure Resource Graph Query:

Resources
| where type =~ 'microsoft.network/networksecuritygroups' 
and isnull(properties.networkInterfaces) 
and isnull(properties.subnets)
| extend SubscriptionName=case(subscriptionId =~ 'Add Subscription 1 Id here ', 'Add Subscription 1 Name Here', 
subscriptionId =~ 'Add Subscription 2 Id here ', 'Add Subscription 2 Name Here'
subscriptionId =~ 'Add Subscription 3 Id here ', 'Add Subscription 3 Name Here'
,subscriptionId) // You can in similar way to add more
| summarize count() by SubscriptionName

Output:

That’s it!