Building better Azure inventory system

Azure portal provides semi-usable ability to inventory your VMs for various folks at you company but have significant limitations, namely:

  • Users will still require be at least in ReadOnly role in your subscription
  • Available information is not well structured (for example you can not see in list size of OS disk drive for each machine)
  • There is no historical information available about state of environment since what you see in snapshot in time of inventory

Solution below will allow to output required information in Excel file hosted in Office 365. Following are working pieces of solution

  1. Azure logic app which runs on recurrent schedule
  2. Azure automation job which pulls necessary information out of Azure and output JSON for Azure Logic App to consume
  3. Office 365 workspace which will hold resulting Excel file written as part of LogicApp job

Automation Job

Automation job consists of 2 run books. Get-AzureInventory graphical powershell runbook which calls child jobGet-VMs

Get-AzureInventory runbook shown below is used to pull Automation Account credentials and login to target subscription. Download this file ( and import it into Automation Account. This runbook calls child runbook called Get-VMs ( ) download and import it into Automation account as well.

The output of this job is JSON file which will be consumed downstream by Azure LogicApp and sent to Excel online.

Example of output is below.

        "VMName":  "WinVM",
        "Location":  "canadacentral",
        "ResourceGroup":  "TEST",
        "OSType":  "Windows",
        "PowerState":  "VM running",
        "BootDiagnostics":  null,
        "OSDiskSizeGB":  127,
        "NumberOfDataDisks":  2,
        "Offer":  "WindowsServer",
        "Publisher":  "MicrosoftWindowsServer",
        "VMSize":  "Standard_DS1_v2",
        "DataDisksSize":  "10|10",
        "VnetName":  "Test-vnet",
        "Subnet":  "default",
        "privateIPs":  "",
        "publicIPs":  "",
        "EnvironmentTag":  "Production",
        "VMCores":  1,
        "VMmemory":  3.5
        "VMName":  "LinuxVM",
        "Location":  "westus",
        "ResourceGroup":  "TEST",
        "OSType":  "Linux",
        "PowerState":  "VM running",
        "BootDiagnostics":  null,
        "OSDiskSizeGB":  30,
        "NumberOfDataDisks":  0,
        "Offer":  "UbuntuServer",
        "Publisher":  "Canonical",
        "VMSize":  "Standard_B1ms",
        "DataDisksSize":  "",
        "VnetName":  "Testvnet646",
        "Subnet":  "default",
        "privateIPs":  "",
        "publicIPs":  "",
        "EnvironmentTag":  "Development",
        "VMCores":  1,
        "VMmemory":  2

You can modify script Get-VMs to your liking. For example current script looks for 4 specific tags and outputs into JSON which might be different in your environment. The same goes for custom output of DataDisk sizes etc.

Azure Logic App

Azure Logic App is what automates entire process of pulling information out of Azure via Automation Runbook job, massaging it and outputing it into Excel online. Steps are shown below.

App consists of following major steps

  1. Recurrent execution
  2. Instantiating and getting results of Automation job in JSON
  3. Copying Excel template file to populate from JSON obtained in step 2
  4. Copying file into History folder for historical reasons

Create following folder structure in Sharepoint online. You can get Excel file here (

Add recurrence step with whatever recurrence you desire

Add Azure Automation job you created in previous step

Add Get Job Output job and pass JobID from previous step

Add Parse JSON step which will convert output of Automation job into LogicApp artifact. Paste following JSON into schema ( )

Add Copy File Sharepoint task to copy template file into root folder

Add foreach block and map Excel fields to results of Parse JSON statement

Add step to copy file to history folder for historical reasons

If everything was done correctly then running Azure Logic App job manually will populate excel file with information from Azure

One thought on “Building better Azure inventory system

Leave a Reply

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

You are commenting using your 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