[SOLVED] How to get a list of resource count per subscription with Azure Resource Graph Explorer query?

How to get a list of resource count per subscription with Azure Resource Graph Explorer query?

I'm trying to get a list of resource count per subscription. This is simple enough and Azure also has documentation on how to do so. Here's my version.

resources
| join kind=inner (
    resourcecontainers
    | where type == "microsoft.resources/subscriptions"
    | project subscriptionId, subscriptionName = name )
    on subscriptionId
| summarize count() by subscriptionName
| sort by count_ desc

What I can't figure out is how to include subscriptions with 0 resources on this table?

resources table only contains subscriptions that have at least one resource but it doesn't contain empty subscriptions. When I check resourcecontainers table, there are around 30 more subscriptions that are not showing up with the above query. These 30 subscriptions are empty.

I learned today that let or creating variables in Azure Resource Graph Explorer is not supported. Otherwise, I could assign the result of the above to a variable, and do something like "resourcecontainers | where subscriptionName !in result_variable.subscriptionName" and then union the two results together.

My last resort is to export the results of both tables and generate what I need with Bash/PowerShell but just wanted to ask here if anybody knows how to do this here directly in Azure Resource Graph Explorer? Thanks :)

Solution

You need to do a left outer join between the resourcecontainers and the resources tables. Try this:

resourcecontainers
| where type == "microsoft.resources/subscriptions"
| project subscriptionId, subscriptionName = name
| join kind=leftouter (resources | project subscriptionId, id) on subscriptionId
| summarize countif(isempty(id) == false) by subscriptionName