MEM report dashboards with Power Automate and Power BI

If you’ve been reading my posts over the last few months there’s a theme and it’s all around automation with Power Automate, however it doesn’t stop there as I’m also demonstrating the value gained from Microsoft 365 by integrating and utilizing services to improve user, admin, and business productivity. As I work with customers the topic of value comes up quite a bit and during these times where more people are working from home, organizations are looking at every option to gain more value out of subscriptions such as Microsoft 365. I too am value hungry and when I invest in something I expect to utilize it to the fullest extent.

During customer engagements I often discover a solution was purchased for one or two specific purposes while other offerings within a solution are not seen as a priority or I hear something like “we’ll get to it later”. To me, the purchase is devalued at that point and it’s a good practice to take step back and look at the larger picture and understand the full value of a product.

The value I have seen when customers deploy Microsoft Endpoint Manager (MEM) isn’t just one product, it spans multiple solutions both within MEM and across the Azure and Microsoft 365 ecosystem. Recently I had a conversation with a customer who was interested in creating additional MEM dashboards for their leadership. There are multiple methods within MEM Intune to view, export, and connect programmatically to data, however with Power Automate we have another method to query data and publish to a dashboard and in this instance I publish to a Power BI dataset.

Let’s get started!

In this post I walk through creating a Power BI dashboard to show device compliance status.

Requirements

1. Power Automate
2. Power BI
3. Microsoft Endpoint Manager – Intune

Registering an app in Azure Active Directory to access the Power BI service

The quickest and easiest method to register an app in Azure AD is to go to: https://dev.powerbi.com/apps, however you can go directly to Azure AD and register an app that way as well. Both methods accomplish the same thing. Once the app is registered navigate to Azure AD > app registrations and select “All applications” to see all registered apps (using https://dev.powerbi.com/apps will not show up under “Owned applications” so we select “All applications” to see it).

Select the app and then select “API permissions” and make sure consent is granted, if it’s not grant consent as shown below.

Creating a Power BI dataset

To post data to Power BI a dataset must exist, however to use the Power BI connectors in Power Automate the dataset must be created via and the Power BI API. There are a few steps we need to go through to set up a new Power BI dataset. I use Postman quite a bit for my queries and if you’re interested see my use of Postman from previous posts, however Graph Explorer works just as well.

Creating the dataset using Postman

The instructions to create a new dataset using Graph are located here: https://docs.microsoft.com/en-us/rest/api/power-bi/pushdatasets/datasets_postdatasetingroup

In the body we need to create and add the columns to the table as shown below. For this dataset I’m interested in creating a compliance Power BI dashboard so I added columns I’m interested in viewing. We can see columns by running the following Get Graph query in Graph Explorer:

https://graph.microsoft.com/v1.0/deviceManagement/deviceCompliancePolicies/CompliancePolicyID/deviceStatuses

Here’s the full query and JSON I utilized to create the dataset in Power BI:

Get dataset from Power BI

To validate the dataset was crated we can run a Get query in Graph Explorer (or in my case Postman): https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/getdatasets

If you’d like, navigate to Power BI as well to validate the dataset was created:

Power Automate

Let’s step through the process:

1. I manually trigger this flow, however change the first step to a recurrence trigger to schedule this to run when you’d like it to run.
2. The next four items are where I store variables for use in the HTTP action to perform a Graph query.
3. Get compliance state is a custom connector I created that uses Graph to query compliance. I used a connector as I intend to utilize it in other flows so it’s quicker to add in vs create an HTTP action to call Graph every time.
4. Next I parse the JSON returned from the “Get compliance state” connector.
5. Next I use an HTTP action to make a Graph call and use the compliance policy ID to circulate through each policy looking for device compliance state (see previous posts for fuller details if needed).
6. I parse JSON from the output of the HTTP action.
7. Finally I write the compliance state and additional fields to a Power BI dataset.

Power BI action details

Connect to the workspace and select the dataset then table. Next add the dynamic content retrieved from the JSON output to the columns displayed after the table is selected:

Note: the “Add row to a dataset” action is currently in preview.

Creating a Power BI dashboard

Creating a Power BI dashboard is fairly straightforward. Simply check the field you’re interested in viewing and choose the type of visual you’d like to see of the data.

Take the Power Automate process further by adding a step and sending users notifications or even links to the dashboard in Teams or email or alerting your ticketing system.

Note: the Intune data warehouse is an option for historical data if needed: https://docs.microsoft.com/en-us/mem/intune/developer/reports-api-url

Coincidently V2 of the Power BI compliance report using the Intune data warehouse was published a few days ago: https://techcommunity.microsoft.com/t5/intune-customer-success/announcing-a-new-version-of-the-powerbi-compliance-report-with/ba-p/1391343

Conclusion

That’s it! We created a dataset in Power BI using Graph then utilized Power Automate to populate the dataset with near real-time data from MEM Intune.