Getting an excel list of all Azure Virtual machines

Now i don't think i'm unique in getting frustrated that from some of the Azure portal screens there is no download as csv option or a more effective filter to show exactly what i want.

A prime example which has annoyed me a few times is just getting a list of all my virtual machines - this is all there on the VM blade but how do i get that in excel format?

There are a few ways of going about getting this - but i think one of the easiest is to take the approach i describe below....

First up we need to create a resource graph query - this is a relatively new feature that allows us to query the Azure metadata using Kusto ( i talked a little more about that here) We can then make use of the Kusto functionality to join to lots of data sources and pull together a report of exactly what we want.

The primary data i want to pull out is the machine name,resource group, subscription, vmsize, os and the iteraplan identifier (this is present as a tag on our resource groups).

Using kusto i can do this relatively easily as:

(Resources |where type == "microsoft.compute/virtualmachines" 
| project vmname=name,resourceGroup=toupper(resourceGroup),vmsize=properties.hardwareProfile.vmSize,sub=toupper(subscriptionId),os=properties.storageProfile.osDisk.osType
| join kind=inner (ResourceContainers 
|where type=='microsoft.resources/subscriptions/resourcegroups' 
|project name=toupper(name),itera=tags.IteraplanID) on $left.resourceGroup==$right.name)
| join kind=leftouter (ResourceContainers 
| where type=='microsoft.resources/subscriptions' 
| project SubName=name,sub=toupper(subscriptionId)) on $left.sub==$right.sub 
|project vmname,resourceGroup,SubName,vmsize,os,itera

This gives an output like this:

That screen helpfully has a download as csv button so i can then extract it in the format i want - i can even save it as a resource graph query (see the save as button in the screenshot) - then i can directly navigate to that - and also share that with other people.

To make things even simpler i can do the following:

1) create a new dashboard name 'VM CMDB' - choose the dashboard option and then click New dashboard and give it a name (leave the dashboard itself empty for now)

2) Now that dashboard is set as the current one we can go back to resource graph explorer screen and click the 'pin to dashboard' option. This then create a tile automatically on the current dashboard.

A little bit of resizing and then i get this:

I can now just choose that dashboard to go direct to that view and extract the data in excel.

3) i can even then share the dashboard (those are objects too) - i just click the share button from the top of the dashboard - set privileges as appropriate - then share the direct link with people (cut and paste from browser address bar) - this makes it dead easy to give everyone access.