The guide describes how to setup a Patch My PC Power BI report for Intune using Azure automation to regularly export data from Intune into Azure blob storage as the data source for the report.
Overview
In this guide we will create a Power BI report from a template to report on apps and updates in Intune managed by the Patch My PC publishing service. Patch My PC is a great tool for keeping 3rd party applications up-to-date. The report is only for applications in Intune and not ConfigMgr.
As Power BI does not natively support connections to Microsoft Graph, we will use Azure automation to regularly export data from Intune using the Microsoft Graph REST APIs and save this as CSV files in Azure blob storage. Power BI supports the use of files in an Azure storage account as a data source, and the Power BI service also supports scheduled refresh on this data source, so your report can automatically stay up to date.
High-Level Process
Although we create the required Azure resources in this guide, you may wish to use resources you already have in Azure for this.
1.
Create / configure an Azure automation account
2.
Grant API permissions to the managed identity or Run as account
3.
Create / configure an Azure storage account
4.
Create the Automation runbook
5.
Create the Power BI report
Requirements
A working Patch My PC for Intune installation with apps / updates created and assigned
Some familiarity with Azure administration
Some familiarity creating Power BI reports
PowerShell knowledge
Appropriate permissions to create / configure the Azure resources
Change Log
Date
Detail
2022-07-08
Updated the Power BI template with a new-look dark theme. Added an "Application counts" chart on the installs report pages, as well as device and install counts, and an "install state" filter.
2022-07-08
Updated the Remove-MSGraphExportJobDuplicates function with some more efficient code that (in testing) is around 30% quicker. Thanks to Yannick Van Landeghem for the tip on this!
2022-05-27
Major rewrite to move away from the reports/exportJobs Graph endpoint due to much tighter limits put in place by MS for frequent requests for jobs. Switched back to the reports/getDeviceInstallStatusReport endpoint again and used looping to overcome the current 50 item limit per request.
2022-05-12
Added a filter to the Request-ExportJobs function in the automation runbook to only include install states that are NOT 'not applicable'. This significantly reduces the overall processing time of the runbook where apps are targeted to a large number of devices for which the app is not applicable to that device.
2021-09-27
Added some retry logic to the Request-ExportJobs function in the runbook to handle when Graph returns a bad request response. This is in response to changes made to service limits in MS Graph around 2021-09-24.
2021-08-23
Switched from using the deviceManagement/reports/getDeviceInstallStatusReport Graph endpoint to exportJobs for the device installation status data due to the limitation of the endpoint only returning 50 results per call.
2021-08-23
Added a function in the runbook to create a unique result set for the device installation status data by including only the most recent entry per device
2021-08-23
Released version 1.1 of the Power BI report template removing the 'Not applicable' status from the donut charts for a better view.
2021-08-17
Fixed an issue with the automation runbook where it would fail if the number of apps or updates in an export job was a multiple of 20.
2021-07-07
Updated the runbook script to include better error handling and some retry logic in case the http 504 error (gateway timeout) is returned.