question

yesoreyeram avatar image
0 Votes"
yesoreyeram asked tommihovi commented

Azure resource graph query for empty resource groups

can someone help me to write a azure resource graph kusto query to identify empty resource groups using azure resource graph?

resourcecontainers
| where type == "microsoft.resources/subscriptions/resourcegroups"
| distinct name

resources
| distinct resourceGroup


I have above two queries but i am struggling to make a join out of this.

azure-blueprints
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

DCtheGeek avatar image
4 Votes"
DCtheGeek answered tommihovi commented

You've got pieces of it. Here's the query I came up with:

 ResourceContainers
 | where type == "microsoft.resources/subscriptions/resourcegroups"
 | extend rgAndSub = strcat(resourceGroup, "--", subscriptionId)
 | join kind=leftouter (
     Resources
     | extend rgAndSub = strcat(resourceGroup, "--", subscriptionId)
     | summarize count() by rgAndSub
 ) on rgAndSub
 | where isnull(count_)
 | project-away rgAndSub1, count_

  • First, I'm getting all the resource groups from ResourceContainers since it has all of them, including the empty ones. We extend the new property "rgAndSub" to concat the resource group and subscription ID to a unique string. I did this because I found that I have a resource group with the same name in multiple subscriptions, some are empty and others aren't, and needed a way to uniquely identify each across subscriptions.

  • Then I do the join to Resources as a "leftouter" (because we expect records in ResourceContainers that won't have matching records in our join -- which are our empty RGs). Here, I'm using the same extend function and summarizing the count of resources based on that custom property.

  • Then, I use isnull to only find the instances of "count_" (from the summarize in the joined table) where the value is null. You aren't looking for 0 here because anything that had resources has an integer value that's equal to or greater than 1. So we use isnull instead.

  • Last, I project-away the duplicate property from the join and the count since we don't need either in the final results.


Hope that's what you were looking for! Note that since Resource Graph doesn't index every Azure resource yet (see supported list here: https://docs.microsoft.com/azure/governance/resource-graph/reference/supported-tables-resources), it's possible that you'll get a resource group back from this query that does have a resource in it, just not one that Resource Graph is tracking today.

To make it easy, run this query in Azure portal.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I found this to be useful and worked like a charm in my environment. Thanks!

0 Votes 0 ·