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.
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.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.
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.
In part 3 , I will show how you can provide a list of multiple subscriptions and get all the results in just one query. Much neater.
