List Your Azure VMs in Excel

In this post I would like to show how you can use an Excel Power Query inside the latest version of Excel, to be able to dynamically list your Azure VMs in Excel.

You are probably wondering why you would need to do such a thing.
Well if you want to validate that all VMs in Azure have been picked up by your SAP LaMa system, then this is a fairly easy way to perform that validation. By combining both the SAP LaMa list and the Azure list and validating both using an Excel vlookup, for example.

Prerequisites

• You’re going to need the latest version of Excel (O365).
• You will also need read access to your Azure subscription (if you can log into the Azure portal and see VMs, then that should be good enough).

Create a Workbook

Open Excel and create a new blank workbook.
Select the “Data” tab:

Click “Get Data” and select “From Other Sources”, then click “Blank Query”::

Click “Advanced Editor”:

Remove any existing text from the query box:

Secret Sauce – The Code

We modified some cool Power Query code provided by Gil Raviv (available here: datachant.com/2016/06/27/cursor-based-pagination-power-query/).

Instead of querying Facebook like in Gil’s example, we change it to the URL of our Azure subscription and the specific Compute API for Virtual Machines in Azure (API details are here: docs.microsoft.com/en-us/rest/api/compute/virtualmachines/listall).

Adjust the code below, changing:
[YOUR SUBSCRIPTION] = Your Azure subscription ID.

You may also need to increase the “iterations” parameter if you have more than say 900 VMs.

 let
 iterations = 10, // Max Number of Pages of VMs, you may need more depending on how many 100s of VMs you have. 
 url = "https://management.azure.com/subscriptions/[YOUR SUBSCRIPTION]/providers/Microsoft.Compute/virtualMachines?api-version=2019-07-01", 

 // FnGetPage is the function that performs an import of single page. 
 // The page consists of a record with the data and the URL in the 
 // fields data and next. Other Web APIs hold the data and cursor in different formats 
 // but the principle is the same. 
 FnGetOnePage = (url) as record => let Source = Json.Document(Web.Contents(url)), 
 data = try Source[value] otherwise null, 
 next = try Source[nextLink] otherwise null, 
 res = [Data=data, Next=next] in res, 
 GeneratedList = List.Generate( ()=>[i=0, res = FnGetOnePage(url)], 
 each [i]null, each [i=[i]+1, 
 res = FnGetOnePage([res][Next])], 
 each [res][Data]),

 #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
 #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"), 
 #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"name"}, {"VM-Name"}) 
 in 
 #"Expanded Column2"

Paste your modified into the query box:

Check there are no syntax errors and click “Done”:

Click “Edit Credentials”:

Select “Organizational account” and select the first entry that contains your subscription ID, then click “Sign in”:

Sign in with your username and password for the Azure Portal.
Click “Connect” once signed in.

You will then see your VMs listed in the query output:

Click “Close & Load”:

The query is now embedded into a new worksheet:

That’s it.
For now this is a basic VM listing.
You may be wanting to extract more information about the VMs in the list, maybe the powerState, or the resourceGroup.
I’ll be showing you how to do this in the second post here.