Microsoft Endpoint Manager aka Intune has been around for a while now and has evolved quite significantly since its early days and the old Silverlight portal (remember that?). Historically Intune hasn’t been particularly good with its reporting capability, but since end 2019 and the announcement of the new reporting framework, things are starting to improve with more built-in reports appearing all the time and now the ability to create your own Azure monitor workbooks from log analytics – welcome additions for sure.
That being said, if you use Power BI as a corporate reporting service there’s no better place to create custom reports according to your needs and share those reports with interested parties without having to give them access to Intune and the MEM portal. Intune data can be queried using Microsoft Graph yet there is currently no native way to use Microsoft Graph as data source in Power BI short of developing a custom connector. With the advent of log analytics data for Intune, we will be able to export log analytics queries to Power BI using M query language which looks promising.
In the meantime, we need to use a little creativity to get data out of Intune and into Power BI to furnish a custom report. I’ve seen examples of using Logic Apps and Function app in Azure as an intermediary process, which is cool. I decided to take a slightly different approach to this and use an Azure automation account to simply export data from Microsoft Graph on a schedule and dump it into an Azure storage account. Power BI supports the use of blob storage as a data source so this works quite nicely.
- An Azure automation runbook queries Microsoft Graph, organizes the data a bit then exports it into CSV files
- The CSV files are then uploaded as blobs in a container in a storage account
- Power BI then uses those CSV files as its datasource allowing us to create custom reports from the data
There are a few advantages to this approach:
- You can use Power BI to create your own custom reports from any Microsoft Graph data across Microsoft 365, and you can combine data from different sources in a single report
- Reports support using a scheduled refresh in the Power BI service for keeping data up-to-date
- Everything is in the cloud – there is no requirement for on-prem resources or a data gateway
- You can use a managed identity or a Run as account for simplified, secure no-credential authentication
- Graph data can be manipulated using PowerShell before export allowing customization of the final data set
On the last point, some data in Microsoft Graph contains nested data, eg a field may contain its own set of key-value pairs and you would want to expand these out into their own fields to report on them. Or you may wish to create your own calculated fields from the data, for example how many days since the last sync of an enrolled device.
Additionally Microsoft recently announced support for enabling a managed identity for an automation account, and this is excellent for simplifying and improving the security of access to Microsoft Graph.
The only slight disadvantage to this approach perhaps is that data in the report won’t be live – it will only be as good as your refresh schedule.
Just to give an example, here are a couple of screenshots of a report I created from Intune data for enrolled devices, including Android, iOS and Windows.
I’ve created a new docs site where you can download this report as a template, the runbook used to export the data as well as access detailed instructions on how to set up a data export from MS Graph into Power BI.
There’s also a bonus runbook that exports a list of unhealthy MEMCM clients and send it as an email report. We don’t want any unhealthy MEMCM clients now do we?!
