This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

List Your Azure VMs in Excel – Part 3

The third and final part to the trilogy.
I show you how to list your Azure VMs in Excel O365 using Power Query, and this time we enhance the code even further using the Power Query function List.Accumulate, to accumulate the returned list of VMs from each subscription into one big list.

Like an episode of a tacky day-time television show, it’s always worth wasting some words on a recap.
So, in-case you’re not aware, here’s what has happened in this saga so far:

List Your VMs in Excel – part1
The first part of the trilogy, showed how to create a new Power Query in Excel O365, and how to enter the code, which generated a basic VM list from Azure.

List Your VMs in Excel – part2
The second part enhanced the code slightly, parameterising more of the text strings.
It also introduced the ability to get the powerState of each VM from Azure, allowing you to see in Excel, which VMs were running and which were deallocated.

By applying the code changes from this post, you will no longer need multiple Power Query queries, going from 1 per Azure subscription, to just 1 query for all subscriptions.

What’s New?

As mentioned, the code now includes the use of the Power Query “List.Accumulate” function to combine the lists of VMs from multiple subscriptions.

I’ve never really use Power Query before, so even I had no idea how to loop on a list of values and execute a function on each loop. After a bit of searching I found that the List.Accumulate function did exactly what I needed it to do, with minimal coding needed.

Here’s a pretty comprehensive description of how List.Accumulate works: https://datachant.com/2016/06/02/power-query-list-accumulate-unleashed/

What you will notice is that our code has the following properties:
subscriptions = Our defined list of Azure subscriptions.
{} = Our blank list as a seed.
List.Combine = Is executed for each entry in our subscriptions list.

List.Accumulate(subscriptions, 
                {},
                (state,current)=>
                              List.Combine({state,FnGeneratedVMList(current)}))

We define the subscriptions list right at the start of the code:

subscriptions = {"[your subscription1]","[your subscription2]"} as list, 

To make our code work with the List.Accumulate, we have changed the “GeneratedVMList” variable contents, to be a function, instead of a string:

// FnGeneratedVMList pages through the subscription and gets the VM lists. 
FnGeneratedVMList = (subscription as text) as list => 
 List.Generate( ()=>[i=0, 
  res = FnGetOnePage(endPoint & "/subscriptions/" & subscription & "/providers/Microsoft.Compute/virtualMachines?api-version=" & apiVersion)], 
  each [i]null, 
  each [i=[i]+1, 
  res = FnGetOnePage([res][Next])], 
  each [res][Data]),

The main benefit of the function is that it can now be passed a parameter “subscription“, which is used to adjust the URI to the Azure API for the correct subscription.

The End Result

The end result of our changes is the following code:

let 
 iterations = 10 as number, 
 // Max Number of Pages of VMs. 
 endPoint = "https://management.azure.com" as text, 
 subscriptions = {"[your subscription1]","[your subscription2]"} as list, 
 apiVersion = "2019-07-01" as text,
 
// FnGetOnePage 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,
 
// FnGetVMdisplayStatus gets the instanceView object for the passed VM ID 
// then parses out the displayStatus from one of two possible locations. 
FnGetVMdisplayStatus = (idURI) as text => 
 let Source = Json.Document(Web.Contents(endPoint & idURI & "/instanceView?api-version=" & apiVersion)), 
 statuses = Source[statuses], 
 vmDisplayStatus1 = try statuses{1}[displayStatus] otherwise "", 
 vmDisplayStatus2 = try statuses{2}[displayStatus] otherwise "", 
 vmDisplayStatus = vmDisplayStatus1 & vmDisplayStatus2 
in
 vmDisplayStatus,

// FnGeneratedVMList pages through the subscription and gets the VM lists. 
FnGeneratedVMList = (subscription as text) as list => 
 List.Generate( ()=>[i=0, 
  res = FnGetOnePage(endPoint & "/subscriptions/" & subscription & "/providers/Microsoft.Compute/virtualMachines?api-version=" & apiVersion)], 
  each [i]null, 
  each [i=[i]+1, 
  res = FnGetOnePage([res][Next])], 
  each [res][Data]),

// SubscriptionsVMList combines the returned lists using the Accumulator. 
SubscriptionsVMList = 
 List.Accumulate(subscriptions, {},(state,current)=>List.Combine({state,FnGeneratedVMList(current)})),
 
#"VMListTable" = Table.FromList(SubscriptionsVMList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
#"Expanded-VMListTable-Column1" = Table.ExpandListColumn(#"VMListTable", "Column1"), 
#"VMdetail-list" = Table.ExpandRecordColumn(#"Expanded-VMListTable-Column1", "Column1", {"name","id"}), 
#"VMdetail-list-with-displayStatus" = Table.AddColumn(#"VMdetail-list", "displayStatus", try each FnGetVMdisplayStatus([id]) otherwise "??") 
in 
#"VMdetail-list-with-displayStatus"

You will need to adjust [your subscription1] and [your subscription2] with your subscriptions, adding any additional subscriptions in the same format.

If you only have one subscription, you can still use this code, just remove one of the items from the subscriptions list, like so:

subscriptions = {"[your subscription1]"} as list,

Summary

Hopefully you can see how you can enhance the code slightly to include more VM details if you need them.
As an example, this line here can be adjusted to include additional columns as well as “name” and “id” (for example: “location“), as per the VM list API call (see the first part of this trilogy for more detail):

#"VMdetail-list" = Table.ExpandRecordColumn(#"Expanded-VMListTable-Column1", "Column1", {"name","id"}), 

You can now enjoy your data in Excel.

Update 23-June: Due to popular demand, here is how the above code can be changed to include the “location” and “vmSize” fields, which will depict the location and series/size of the VM.
We make a small change to the last few lines like so:

#"VMListTable" = Table.FromList(SubscriptionsVMList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
"Expanded-VMListTable-Column1" = Table.ExpandListColumn(#"VMListTable", "Column1"),
#"VMdetail-list" = Table.ExpandRecordColumn(#"Expanded-VMListTable-Column1", "Column1", {"name","id", "location", "properties"}),
#"VMdetail-list-properties" = Table.ExpandRecordColumn(#"VMdetail-list", "properties", {"hardwareProfile"}, {"hardwareProfile"}),
#"VMdetail-list-properties-hardwareProfile" = Table.ExpandRecordColumn(#"VMdetail-list-properties", "hardwareProfile", {"vmSize"}, {"vmSize"}),
#"VMdetail-list-with-displayStatus" = Table.AddColumn(#"VMdetail-list-properties-hardwareProfile", "displayStatus", try each FnGetVMdisplayStatus([id]) otherwise "??"),
in 
#"VMdetail-list-with-displayStatus"

List Your Azure VMs in Excel – Part Deux

If you’ve been following, I recently showed you how to get a list of Azure hosted VMs into Microsoft Excel (O365) using Power Query.

You can find the original post here: List Your Azure VMs in Excel

The list was basic, listing only the VM names.
I promised a follow-up post on how to get the Power State of the VM, which would show if it was running, deactivated or powered off completely.
Here is the follow-up.

Can We Just Adjust our API Call?

If you look at the Azure REST API specification for the Compute collection, you will see that it looks like a simple enough task to get the VM Power State as part of the call to VirtualMachines ListAll (see here https://docs.microsoft.com/en-us/rest/api/compute/virtualmachines/listall).

However, all is not well with the call when applying the “statusOnly” URI parameter to the call. It generates an error in Excel when called via “JSON.Document” and Googling around would seem to throw up some interesting comments from others with the same issue.

In short, it doesn’t seem to work the way the API docs say it should, and there appears to be no other way apart from looping on the “VirtualMachineListResult” array that you get back from the “ListAll” call, then making a separate call to get the VM details from the VMInstance view.

So, this is exactly what I did but from within Power Query 🙂

What Changes Were Needed?

As part of the changes to our code, we had to include the VM ID, so that we could use it to call the VMInstance view.
To do this we included a new function FnGetVMdisplayStatus:

// FnGetVMdisplayStatus gets the instanceView object for the passed VM ID 
// then parses out the displayStatus from one of two possible locations. 
FnGetVMdisplayStatus = (idURI) as text => 
 let Source = Json.Document(Web.Contents(endPoint & idURI & "/instanceView?api-version=" & apiVersion)), 
 statuses = Source[statuses], 
 vmDisplayStatus1 = try statuses{1}[displayStatus] otherwise "", 
 vmDisplayStatus2 = try statuses{2}[displayStatus] otherwise "", 
 vmDisplayStatus = vmDisplayStatus1 & vmDisplayStatus2 
in
 vmDisplayStatus,

The new function uses the VM ID, passed in as the function parameter idURI, to formulate the API call URL.
You can see that we’ve also had to parameterise the URL elements throughout, to reduce duplication.

 endPoint = "https://management.azure.com" as text, 
 subscription = "[your subscription]" as text,
 apiVersion = "2019-07-01" as text,

The Power State (we use JSON field displayStatus) of the VM, is returned in one of two items inside a JSON statuses array element. Since there is no guarantee which item in the array will contain the displayStatus, we check in both:

 vmDisplayStatus1 = try statuses{1}[displayStatus] otherwise "", 
 vmDisplayStatus2 = try statuses{2}[displayStatus] otherwise "", 

Finally, we augment our final output table, with an additional column, which will include the call to FnGetVMdisplayStatus to get the related Power State for the VM, using the current table row’s ID column value:

#"VMdetail-list-with-displayStatus" = Table.AddColumn(#"VMdetail-list", "displayStatus", try each FnGetVMdisplayStatus([id]) otherwise "??")

The End Result

The end result of our modifications means that we now have an additional column in our output table, which is populated by our additional function call to the Azure API to get the VMInstance view, parse it and extract the displayStatus.

Once again, thanks to Gil Raviv for the original code with the pagination technique (available here: datachant.com/2016/06/27/cursor-based-pagination-power-query/).

NOTE: I am a Power Query novice, so if YOU (Jon 😉 ) have any tips on how to make this better, neater, faster, please leave a comment and I will test any recommendations.

let 
 iterations = 10 as number,
 // Max Number of Pages of VMs. 
 endPoint = "https://management.azure.com" as text, 
 subscription = "[your subscription]" as text, 
 apiVersion = "2019-07-01" as text, 
 vmListUrl = endPoint & "/subscriptions/" & subscription & "/providers/Microsoft.Compute/virtualMachines?api-version=" & apiVersion as text,
 
// FnGetOnePage 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,

// FnGetVMdisplayStatus gets the instanceView object for the passed VM ID 
// then parses out the displayStatus from one of two possible locations. 
FnGetVMdisplayStatus = (idURI) as text => 
 let Source = Json.Document(Web.Contents(endPoint & idURI & "/instanceView?api-version=" & apiVersion)), 
 statuses = Source[statuses], 
 vmDisplayStatus1 = try statuses{1}[displayStatus] otherwise "", 
 vmDisplayStatus2 = try statuses{2}[displayStatus] otherwise "", 
 vmDisplayStatus = vmDisplayStatus1 & vmDisplayStatus2 
in
 vmDisplayStatus,

// GeneratedVMList is the function to page through the subscriptions and get the VM lists. 
GeneratedVMList = 
 List.Generate( ()=>[i=0, res = FnGetOnePage(vmListUrl)], 
  each [i]null, 
  each [i=[i]+1, 
  res = FnGetOnePage([res][Next])], 
  each [res][Data]),

#"VMListTable" = Table.FromList(GeneratedVMList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
#"Expanded-VMListTable-Column1" = Table.ExpandListColumn(#"VMListTable", "Column1"), 
#"VMdetail-list" = Table.ExpandRecordColumn(#"Expanded-VMListTable-Column1", "Column1", {"name","id"}), 
#"VMdetail-list-with-displayStatus" = Table.AddColumn(#"VMdetail-list", "displayStatus", try each FnGetVMdisplayStatus([id]) otherwise "??") 
in 
#"VMdetail-list-with-displayStatus"

You need to replace [your subscription] with your actual Azure subscription ID.

You can insert the code into an Excel O365 workbook by following the steps in my original post here:

List Your Azure VMs in Excel

Once you have entered the code, you will get back as a result, the VM name, id URI and the displayStatus which will reflect the power status of the VM.

That’s Amazing, What Next?

Can we enhance this code even further? Yes we can.
Right now, if you have multiple subscriptions, you will need to create multiple Power Query queries and then combine them into another query with the other queries as sources. This is quite cumbersome.

In another blog post, I will show how you can provide a list of multiple subscriptions and get all the results in just one query. Much neater.

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.