My Azure Resource Graph Queries using Aggregation

Azure Resource Graph supports querying resources across multiple subscriptions and is fast. The key value add is that it supports aggregation using the summarize operator.

For example answering questions such as How many resources grouped by subscription or location? How many of each OS type are used in resource groups? What is the most used resource type for each resource group?

The following are a collection of resource graph queries that you may use and extend to understand your Azure environments at scale.

# Count by resource group, subscription
Search-AzureRmGraph -Query "summarize Num_ResourceGroups = count(resourceGroup) by subscriptionId" | Out-GridView

# Count by resource type, resource group, subscription
Search-AzureRmGraph -Query "summarize Num_ResourceTypes = count() by type, resourceGroup, subscriptionId " | Out-GridView

# Count by resource type, resource group order by resource type count descending
Search-AzureRmGraph -Query "summarize resource_type_count = count() by resourceGroup, type | order by resource_type_count" | Out-GridView

# Count by resource type, subscription order by type name ascending
Search-AzureRmGraph -Query "summarize count() by type, subscriptionId | order by type asc" | Out-GridView

# Count by resurce group, location and subscription for virtual machines
Search-AzureRmGraph -Query "where type =~ 'Microsoft.Compute/virtualMachines' | summarize Num_VM = count() by resourceGroup, location, subscriptionId"

# Count by operating system type on the OS Disk for VMs grouped by resource group where there is an existing OS type.
Search-AzureRmGraph -Query "where tostring(properties.storageProfile.osDisk.osType) != '' | summarize count() by tostring(properties.storageProfile.osDisk.osType), resourceGroup"

# Count by operating system type on the OS Disk for VMs of that of Windows grouped by resource group, location and subscription.
Search-AzureRmGraph -Query "where type =~ 'Microsoft.Compute/virtualMachines' and tostring(properties.storageProfile.osDisk.osType) == 'Windows' | summarize Num_VM = count() by resourceGroup, location, subscriptionId"

# the most used resource type per resource group
Search-AzureRmGraph -Query "summarize resource_type_max = max(type) by resourceGroup" | Out-GridView

Notice I sometimes pipe the output into Out-GridView this gives a better user experience to browse the resulting data set.

I like point out what the results look like for the more sophisticated query:

# Count by operating system type on the OS Disk for VMs grouped by resource group where there is an existing OS type.

Search-AzureRmGraph -Query “where tostring(properties.storageProfile.osDisk.osType) != ” | summarize count() by tostring(properties.storageProfile.osDisk.osType), resourceGroup”

Notice that I queried based on nested properties of properties.storageProfile.osDisk.osType. The values can be either Windows or Linux. Looks like I only got VMs with Windows operating system.

One way of getting familiar with the properties and its values is using Azure the Resource Explorer tool at resources.azure.com. The following shows the osType for the SQL2017 VM.