Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Microsoft Azure – Create Pie Charts For Orphaned Resources using KQL

  • Last Updated : 16 Dec, 2021

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 Subscripton 1 Id here ', 'Add Subscripton 1 Name Here', 
subscriptionId =~ 'Add Subscripton 2 Id here ', 'Add Subscripton 2 Name Here'
subscriptionId =~ 'Add Subscripton 3 Id here ', 'Add Subscripton 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 Subscripton 1 Id here ', 'Add Subscripton 1 Name Here', 
subscriptionId =~ 'Add Subscripton 2 Id here ', 'Add Subscripton 2 Name Here'
subscriptionId =~ 'Add Subscripton 3 Id here ', 'Add Subscripton 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 Subscripton 1 Id here ', 'Add Subscripton 1 Name Here', 
subscriptionId =~ 'Add Subscripton 2 Id here ', 'Add Subscripton 2 Name Here'
subscriptionId =~ 'Add Subscripton 3 Id here ', 'Add Subscripton 3 Name Here'
,subscriptionId) // You can in similar way to add more
| summarize count() by SubscriptionName

Output:

That’s it!

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!