Get all VMs grouped by Subscription with Azure Resource Graph

Sebastian Schütze
Jan 29 · 2 min read

This is a quick one. To notify all Windows VM owners in Azure we wanted to get all VMs for each subscription with their respective owners and contributors.

Of course, I started with a normal Az PowerShell module and it’s cmdlets. But then I remembered the resource graph and wondered if I can get all VMs with subscription id, os type, VM name, resource group name, location and so on. But grouped by subscription id. And all in one query. And I did it!

$allVms =  (
Search-AzGraph `
-Query "
where type =~ 'microsoft.compute/virtualmachines'
| extend virtualMachine =
pack(
'SubscriptionId',subscriptionId,
'SubscriptionName',subscriptionDisplayName,
'ResourceGroupName',resourceGroup,
'VmName', name,
'Location',location,
'OsType',properties.storageProfile.osDisk.osType
)
| project subscriptionId, virtualMachine
| summarize VirtualMachines=make_set(virtualMachine) by subscriptionId
" `
-Include DisplayNames `
-First 5000
)

The important parts are, that you first filter by the resource type and then create your custom object with the pack function, then you would have all returned properties plus the new property “virtualMachine”. Then I would use project to only return the subscription id and my own property. Finally, I would use the summarize function with make_set, which allows me to group the array by one property with another property.

The parameter “- Include DisplayName” is needed so I can get the tenant display name and subscription name which is not coming by default when you use project.

And Voila! it’s done!


Originally published at RazorSPoint.

Sebastian Schütze

Written by

I am an Azure Nerd with focus on DevOps and Azure DevOps that converted from the big world of SharePoint and O365.

RazorSPoint

Publications from a DevOps enthusiast working with Microsoft Azure

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade