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

Azure logic app which runs on recurrent schedule
Azure automation job which pulls necessary information out of Azure and output JSON for Azure Logic App to consume
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 ( https://gist.github.com/artisticcheese/846f509618cb7bd8754bf705732cba64) and import it into Automation Account. This runbook calls child runbook called Get-VMs ( https://gist.github.com/artisticcheese/89421f49819cecc153e3b33a9c388d6e ) 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":  "10.0.0.4",
        "publicIPs":  "13.71.162.184",
        "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":  "10.0.1.4",
        "publicIPs":  "23.101.204.1",
        "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

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

Create following folder structure in Sharepoint online. You can get Excel file here (https://github.com/artisticcheese/artisticcheesecontainer/blob/master/docs/inventory.xlsx)

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 ( https://gist.github.com/artisticcheese/a150555a8192a8193ff8c71c9e77543e )