RazorSPoint
RazorSPoint

Get all VMs grouped by Subscription with Azure Resource Graph

Sebastian SchützeSebastian Schütze

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.

Also, RBAC information cannot be queued with the resource graph currently. You need to do it with the dedicated cmdlet for this.

And Voila! it’s done!

Result of the query


Also published on Medium.

Sebastian is an Azure Nerd with focus on DevOps and Azure DevOps (formerly VSTS) that converted from the big world of SharePoint and O365. He was working with O365 since 2013 and loved it ever since. As his focus shifted in 2017 to more DevOps related topics in the Microsoft Stack. He learned to love the possibilities of automation. Besides writing articles in his blog and German magazines, he is still contributing to the SharePoint Developer Community (and PnP SharePoint) to help to make the ALM part a smoother place to live in.

Comments 0
There are currently no comments.

This site uses Akismet to reduce spam. Learn how your comment data is processed.