Automating Data Exports from Microsoft Graph

In this guide we will look at some options for automating the export of data from Microsoft Graph into locations such as an Azure storage account or a Log analytics workspace.

If you or your organisation uses Power BI for data visualisation you probably find yourself frustrated with the lack of support for Microsoft Graph in Power BI, making it difficult to create custom reports from Intune data. It's not impossible to make use of the Web connector in Power BI to get data from Microsoft Graph but this isn't supported or recommended by Microsoft.

With a little creativity it's still possible to use Power BI with Microsoft Graph however. You need some kind of intermediary process that can export data from Microsoft Graph into a format and location that Power BI is happy to use as a data source.

If you use Microsoft Azure, you can make use of its automation services to export Graph data on a schedule using, for example, a Function app or Logic apps. My preferred method for this is to use an Azure automation account as its easy to use, inexpensive and secure. Automation accounts now support managed identities, meaning you can access Microsoft Graph and Azure resources without requiring credentials in code, or even using a Runas account.

A benefit of using PowerShell to export data from Microsoft Graph is that you can process or transform your data before sending it on to a storage location. For example, some Graph entities such as for device installation status can return multiple entries per device. You may wish to clean this data and keep only the latest entry for a device, ensuring you only have one entry per device and your deviceId column contains only unique values. In order to create one-to-one relationships between tables in Power BI it is essential to have only unique values in the columns used to create relationships. Or you may wish to export a single table built with data from multiple Graph entities, for example.

Your automation service could export data into an Azure storage account in CSV format which can be easily consumed by Power BI. Alternatively, you could make use of Azure Monitor logs. Your Intune data can be sent to custom logs in a Log Analytics workspace and your log queries can be used in Power BI. Both options support scheduled data refresh to keep your reports up-to-date.

Setting up this automation requires some familiarity with Azure administration as well as understanding how to query Microsoft Graph with PowerShell. In this guide, I'll walk through how to set up the Azure resources to create the framework for your custom reporting, as well as provide some example PowerShell runbooks that demonstrate how to export data from MS Graph and send it to Azure storage or a Log Analytics workspace.