Azure resource graph query for empty resource groups

Sriramajeyam Sugumaran 21 Reputation points
2020-06-23T15:57:53.167+00:00

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
Azure Blueprints
An Azure service that provides templates for quick, repeatable creation of fully governed cloud subscriptions.
60 questions
No comments
{count} votes

Accepted answer
  1. DCtheGeek-MSFT 436 Reputation points
    2020-06-24T15:44:08.67+00:00

    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://learn.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.


0 additional answers

Sort by: Most helpful