How to use Azure monitor workbooks for managing Azure inventory

Posts earlier (https://artisticcheese.wordpress.com/2019/07/30/optimizing-azure-inventory-with-powershell-azure-functions-and-power-bi/ and https://wordpress.com/post/artisticcheese.wordpress.com/1063) showed how to assist inventory managers to get necessary information for auditing or similar reasons which are lacking in native Azure portal.

This post will show how to provide similar functionality by utilizing following technologies

  1. Azure monitor workbooks
  2. Azure Resource Graph behind the scenes

Start with navigating to Azure Monitor/Workbooks and create new workbook from Empty. Import code for workbook by clicking “Edit” button and click “advanced editor”/”Gallery Template”. Code to paste is available here https://raw.githubusercontent.com/artisticcheese/artisticcheesecontainer/master/workbook.json

Once saved you shall get working inventory workbook with 2 tabs. One is showing geographic distribution of your VMs and second tab list of VMs with preset information in template.

Details about how this all put together is below.

Azure monitor workbook allows to execute Azure Resource Graph queries and present results in multitude of visualizations.

Current workbook consists of 2 tabs which first element of workbook initialising 2 tabs (Maps and VMs). As action for user who selected a tab a parameter names view is set to specific value which is used in views to hide/show specific items.

Next is resource graph query for all VMs in subscription with visualization of type Map. Resource graph query is below pulling all VMs and summarizing them by location

 Resources
  | where type == 'microsoft.compute/virtualmachines'
  | where resourceGroup notcontains 'databricks'
  | summarize ["Number of Resources"] = count() by ["Location"] =  location

This view shall be only shown when user selects Map tab which is governed on second tab of the view

Next view is view for VMs which are result of graph query below

 Resources
  | where type == 'microsoft.compute/virtualmachines'
  | where resourceGroup notcontains 'databricks'
| join kind = leftouter (
Resources
| where type == 'microsoft.compute/virtualmachines'
 | mv-expand disk = properties.storageProfile.dataDisks
 | summarize TotalDataDisks = sum(toint(disk.diskSizeGB)) by name, id
 ) on $left.id == $right.id
 | extend Name = name,
 ["Resource Group"] = resourceGroup,
   location,  ["OS Type"] =  properties.storageProfile.osDisk.osType, VMsize = properties.hardwareProfile.vmSize,
   OsDisk = properties.storageProfile.osDisk.diskSizeGB,
   associated_nic = extract(".*/(.*)\"}$", 1, tostring(properties.networkProfile.networkInterfaces[0])),
   associated_nic_id = tostring(properties.networkProfile.networkInterfaces[0].id),
   powerstate = strcat_array(split(properties.extended.instanceView.powerState.code, "/", 1), '')
 | join kind = leftouter (
   Resources
   | where type == 'microsoft.network/publicipaddresses'
   | project ["Public IP Address"] = properties.ipAddress,
     associated_nic = extract(".*/networkInterfaces/(.*?)/.*", 1, tostring(properties.ipConfiguration.id))
   ) on associated_nic
  | join kind = leftouter (
  Resources 
  | where type == 'microsoft.network/networkinterfaces' 
  | project PrivateIP = properties.ipConfigurations[0].properties.privateIPAddress, 
  subnet = strcat_array(split(properties.ipConfigurations[0].properties.subnet.id, "/", 10),''), 
vnet = strcat_array(split(properties.ipConfigurations[0].properties.subnet.id, "/", 8), ''), id,
["accelerated networking"] = properties.enableAcceleratedNetworking
) on $left.associated_nic_id == $right.id
| project Name, ["Resource Group"], location, tostring(["OS Type"]), tostring(VMsize), tostring(OsDisk), TotalDataDisks, tostring(["Public IP Address"]), tostring(powerstate), tostring(PrivateIP), subnet, vnet, tostring(["accelerated networking"]), tags

It’s KQL query with aggregation and conditional functions to get desired set of columns. You can debug KQL query separately from workbook inside Azure Resource Explorer. Only subset of KQL language is available for Resource Graph queries, which are listed here.

You can pass parameters from different areas of workbook to a query via query parameter which is done for passing Location parameter to Azure Resource Graph query as denoted below with squiggly brackets

Resources
| where location in ({location})

Additional query item is added called VMsizesJSON is JSON based view with static values for available VM sizes, which is later joined to results of Azure Resource Graph query above to add CPU/RAM capability for each VM since those values are not available directly in Azure Resource Graph

Power column is custom column of type Icon which allows to display images based on value of a cell with wide range of icon choices. In this case it’s used to display if VM is running or not.

Data Disks column is represented as Heatmap and also formatted as bytes sizes value to correctly display Gib/Tib etc.

Additional ability can be added for people to be able to export data to Excel file for offline viewing/analysis

So far solution based on Azure monitor workbooks seems to be the best choice for outputting Azure inventory information for consumption, it’s robust, easy to maintain and always current

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s