Join resource count to resourcecontainers in Azure resource graph query

Question

I want a summary of resource count by each container type:

• management group
• subscription
• resource group

The management group and subscription should show the accumulated count for the child containers.

Answer 1

The following will produce a result extending resourcecontainers with the columns

• hierarchyLevel, sort results top of hierarchy first
• resourceCount, sort results secondarily based on resourceCount ascending

resourcecontainers
| extend hierarchyLevel = case(
    (type =~ "Microsoft.Management/managementGroups" and tostring(properties.displayName) == "Tenant Root Group"), 0.0,
    type =~ "Microsoft.Management/managementGroups", 1.0 + coalesce(array_length(properties.details.managementGroupAncestorsChain)/10.0,0.0),
    type =~ "Microsoft.Resources/subscriptions", 2.0,
    type =~ "Microsoft.Resources/resourceGroups", 3.0,
    4.0 // Default or unknown types
)
| project id, name, displayName=coalesce(properties.displayName, name), type, location, subscriptionId, managedBy, properties, tags, hierarchyLevel
| extend id_lower=tolower(id)
| join kind=leftouter (
    resourcecontainers
    | where type =~ "microsoft.management/managementgroups"
    | join (
        // Get the set of subscriptions for each management group
        resourcecontainers
        | where type =~ "microsoft.resources/subscriptions"
        | extend chain = properties.managementGroupAncestorsChain
        | mv-expand chain
        | project subscriptionId, management_group_name=tostring(chain.name)
        | summarize subscription_ids=make_set(subscriptionId) by management_group_name
    ) on $right.management_group_name == $left.name
    | mv-expand subscription_id=subscription_ids
    | extend subscription_id = tostring(subscription_id)
    | join kind=leftouter (
        resources
        | summarize resourceCount=count() by subscriptionId
    ) on $left.subscription_id == $right.subscriptionId
    | summarize resourceCount=sum(resourceCount) by id
    | union (
        resources
        | extend id = strcat("/subscriptions/", subscriptionId)
        | summarize resourceCount=count() by id
    )
    | union ( 
        resources
        | extend id = strcat("/subscriptions/", subscriptionId, "/resourceGroups/", resourceGroup)
        | summarize resourceCount=count() by id
    )
    | extend resourceCount=coalesce(resourceCount,0)
    | extend id_lower=tolower(id)
) on id_lower
| project-away id_lower, id_lower1, id1
| extend resourceCount=coalesce(resourceCount,0)
| order by hierarchyLevel asc, resourceCount asc

Note that resource graph query isn't Kusto; it doesn't support let statements and it also only allows 4 joins.