Optimizing Azure Inventory with Powershell Azure Functions and Power BI

In previous post I showed how to build Azure inventory system by utilizing Azure Automation Account, Azure Logic App and SharePoint.

Drawbacks of that approach:

  1. Solution is pretty cumbersome due to the use Azure powershell for query for necessary information and takes up to 30 mins to execute
  2. Neither Azure Logic App no Azure Automation are well integrated into source control and hence makes it difficult to move around as well use modern technologies for CI/CD
  3. Use of SharePoint Excel causing additional headache for concurrency since each item is inserted one by one into Excel online which is causing occasional timeouts and needs to be handled in retry option in Azure Logic App
  4. Result Excel file is single dimensional database and non interactive which is sub-optimal
  5. Report is always stale since it’s last view of state when report was successfully run, so if you run it weekly you might be looking at stale data

Alternative solution which fixes drawbacks above are based on completely different set of technologies. Azure Logic App is replaced with Azure Function, Excel is replaced with Power BI, Azure powershell calls replaced with Azure Resource Graph.

Flow of this setup is as follows:

  1. Azure powershell function is created with system assigned identity to query Azure Resource Graph
  2. Resulting JSON is ingested as web source in to Power BI report

Code for function and power bi file is available here. This is just proof of concept setup and hence you’d need to modify exact query script as well as BI dashboard to fit your needs. Current incarnation just outputs disk information data.

Steps

Create Function App. It shall be powershell function on Windows

Create system assigned identity for application and assign read permissions to subscription

If you are using Azure Blueprints you can create this assignment in your management group

Deploy function app from VSCode

Test application which shall return information from all subscriptions identity have access to

Create function URL which would be called later via Power BI by clicking “Get Function URL”

Open PBIX file from the same repo and click on File/Options and Settings/Data Source settings

Click “Change source” and point to URL you copied in step above

You shall report similar to below. It’s interactive so you can click on VM and see what disk is used by that VM, or you can click on location and only filter VMs from that specific location, etc.

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