Support Tip: Using PowerBI to build custom reports with Intune data
Published Dec 05 2018 11:43 AM 38.6K Views

By Aaron Hamilton | Principal Program Manager on the Enterprise Mobility and Customer Experience Engineering Team

 

Mobile Device administrators frequently find themselves asked by other departments for complex reports or dashboards to help other stakeholders get answers to critical questions. The administrators can use the console and Graph API to get reports, but if the report is needed on an ongoing basis, they might have to frequently refresh or rebuild the report. This is where the Intune Data Warehouse (IDW) coupled with PowerBI can be extremely powerful.

 

In this example, let’s say that the security team wants to understand the lay of the land for the company’s Android footprint across all their users. The security team has asked the mobile device team to help them understand a few key data points and wants to be able to slice the data based on the following types of questions:

  • How many Android devices are in our environment??
  • What is the breakdown by OS version?
  • What is the breakdown by Manufacturer?
  • What is the breakdown by Security Patch Level?
  • How do we get associated users or devices (as a best practice, follow RBAC permissions) for a given OS, Version, Manufacturer, Patch Level?

Using the IDW and Power BI the MDM team can provide a simple to use report that answers all these questions and makes it easy to read the data and understand what the environment looks like.

 

In this post, we’re going to build this sample report, show you some basics on how to use the report, and then how to publish it so others within your organization with the appropriate permissions can access it directly through the Power BI portal. Doing all this will provide a few extra capabilities:

  • Online and delegated report access
  • Automatic data refresh
  • Ability to customize the report/view
  • Use of natural language queries

 

Steps to build your report

  1. Install Power BI Desktop. You can either download it from PowerBi.com or on Windows 10 install it from the Windows Store. I recommend installing it from the store because the store will automatically update the Power BI desktop install as new versions are released; the direct download will not automatically update.

 

  1. Browse to the Intune Portal and copy your IDW URL as shown below:

 AaronPost1.png

 

  1. Open Power BI desktop, login, and then click on the “Get Data”:

AaronPost2.png

 

  1. Select “OData feed” from the drop down:

AaronPost3.png

 

  1. Paste your Data Warehouse URL into the OData Feed URL field and click on “OK”:

AaronPost4.png

  1. Next Power BI will show you all the available data in the IDW. For this report, we’re going to select the following entities:
    1. currentUsers
    2. devices
    3. deviceTypes
    4. mdmStatuses

Your screen will look something like this:

 AaronPost5.png

  1. Click on the “Load” button and wait for Power BI to finish loading your data:

 AaronPost6.png

  1. After Power BI has loaded your data you will see the entities loaded under “fields”:

 AaronPost7.png

 

  1. We’re going to start with building charts to display our Android device data. Click on the stacked vertical bar chart to add the bar chart to your page:

 AaronPost8.png

 

  1. Your page will now have a stacked bar chart on it. Drag it to resize and fill roughly 2/3 of the page:

 AaronPost9.png

 

  1. Now click on the bar chart, expand the devices field, and drag the deviceID field to the “value” field under for the chart visualization. You should immediately see a bar representing the total device count for all of your enrolled devices:

 AaronPost10.png

 

  1. Next, because we’re only interested in active Android devices, we’re going to apply a page filter to exclude everything but Android devices currently enrolled. Expand the deviceTypes field and select deviceTypeName and drag it to page level filters, then check the boxes for Android, AndroidEnterprise,AndroidForWork. Repeat this for devices.osVersion, select advanced filtering, and then “not blank”.  Repeat again for devices.isDeleted and select per below:

AaronPost11.png

  

AaronPost12.png

 

  1. Now we’d like to be able to break out our Android devices by OS version. Select your chart, expand the devices field, click on OSversion and drag it to axis under visualizations. You’ll see your chart then update and the device counts are now broken out by OS version:

 AaronPost13.png

 

  1. Next, we’re going to re-sort the chart based on the OS version itself. Click on the ellipses in the upper right-hand corner of your chart, select Sort by, and then osVersion:

 AaronPost14.png

 

  1. Then return to the same options and sort them in ascending order:

 AaronPost15.png

 

  1. Your chart should now have all the Android devices broken out by OS version and sorted in ascending order:

 AaronPost16.png

 

  1. If we want to exclude the “blank” values (failed enrollments or other issues), we can then add a page filter again based on OS version. Under devices, drag osVersion to page filters, change the filter type to Advanced filtering, and then select “is not blank”:

 AaronPost17.png

 

  1. Next, we’re going to add the ability to see for each OS version the various patch levels. Click on your chart, expand devices, and then select androidSecurityPatchLevel and drag it to Legend on the Visualizations blade:

 AaronPost18.png

 

  1. Now to enable drill down, we’re going to add one or more fields to the Axis field. For this example, we’re going to add the ability to drill down by manufacturer, then model. If you want additional fields (like user, etc.) you would just add more fields. As you drag the field you should see a yellow line below the existing fields, osVersion in this example:

AaronPost19.png

 

  1. Repeat this process for fields you’d like to drill down on and it should look something like this when completed:

 AaronPost21.png

 

  1. Now you can click on the downward arrow on your chart to enable drill down and then click on a data point to drill down into the chart. In this example, I have drilled down into OS version 7 and I can see the manufacturers. Click again would show me the models for each. I click on the upward arrow to go back up a level:

 AaronPost22.png

 

  1. Next, we’re going to add some slicers to help quickly narrow the focus on a report. This will allow someone using the report to select a few key data items and change the graph. Click on blank portion of the page and then select slicer from the visualizations. Note: If your chart suddenly changes, just hit ctrl-z and make sure you don’t have your chart selected before you click on the slicer, or it will change your existing chart to a slicer:

AaronPost23.png 

 

  1. Now with your slicer selected we’re going to select osVersion. You’ll then see a slicer available OS version and be able to select one or more versions:

 AaronPost24.png

 

  1. Add additional sliders for manufacturer and androidSecurityPatchLevel and resize to fit appropriately:

 AaronPost25.png

 

  1. To add the ability to easily view the associated users and devices, we’re going to also add a grid control and display fields. Click on the grid control in visualization to add it to the chart.

 AaronPost26.png

 

  1. Select the grid and then add any display fields you’d like to display from the column data. In this example, I’ve added userID and model from devices. Note if you attempt to add fields that do not have relationships then an error will be thrown. To correct this error, you will either need to use different fields or manually build relationships:

AaronPost27.png 

 

  1. Now to make this report available, we’ll click on publish in the ribbon and publish it directly into the Power BI portal:

 AaronPost28.png

 

  1. Select a workspace to publish the report to and click on “Select” and it should publish successfully to the Power BI portal. Note you will be prompted to save your file. Make sure you name it something meaningful as this will be the name it shows up within the portal:

 AaronPost29.pngAaronPost30.pngAaronPost31.png

 

  1. Published report will show under Reports in the Power BI Portal:

 AaronPost32.png

 

  1. Now to build a dashboard click on the “Pin Live Page” button on the center of the page, then select “New dashboard” and then name your new dashboard:

AaronPost33.png AaronPost34.png

 

  1. You will now see your report as a dashboard:

 AaronPost35.png

 

  1. You can now also use the Q&A “Ask a question about your data” for natural language queries:

 AaronPost36.png

 

  1. To setup automatic refresh of the data and Cortana access click on My Workspace, then datasets, select the Android report, click on the ellipses, and then settings. On the settings screen click on datasets, expand scheduled refresh and enable, then expand Q&A and Cortana and check the box to allow Cortana to access the data. Click apply on both options:

 AaronPost37.png

 AaronPost38.png

  1. Finally let’s share the work with the requestors. Go back to the dashboard and click on “Share” and then enter the email addresses of the requestors on the sharing screen:

AaronPost39.pngAaronPost40.pngAaronPost41.png

 

  1. Also check out the Cortana integration from the desktop. Note that this is rendering in the full-size page we created. If you wanted the format to be appropriately sized, you could create Cortana-friendly reports by editing the page size in Power BI and then publish these as Cortana pages. This approach would be great for basic metric reports like total enrolled devices, compliance across all platforms, and reporting other KPI’s.

AaronPost42.pngAaronPost43.png 

 

Let us know if you have any questions on this post and creating PowerBI dashboards and reports for your teams, and how these steps work for you when using the Intune data warehouse and PowerBI.

21 Comments
Silver Contributor

This is nice. This is probably also possible to post as a tab in Teams?

Microsoft

Absolutely.  In a teams channel click on the "+" to add a tab and then select Power BI.  After than select your publishing work space and then the report.  This will embed the report directly in the teams channel as a tab.

TEams1.jpgteams2.jpg

 

Deleted
Not applicable

Hi,

 

I am unable to connect to OData feed with my Intune Service Admin account. Getting the attached authentication error. Is there any other special permission required to get connected?Annotation 2018-12-11 154023.jpg

Microsoft

Click on "Organizational Account" and login.  

Deleted
Not applicable

Thanks. It worked.

Copper Contributor

Hi, I cannot filter on AndroidEnterprise, because it does not exist as deviceTypeName. It is also strange for me, because I can only see Android devices if I choose Leer (which means blank). If I disable this, there is no devices. We have only AndroidForWork in Intune, legacy Android is being blocked. Any ideas?

 

@Trang_95, Take a look over doc: https://docs.microsoft.com/en-us/intune/reports-proc-create-with-odata and attempt to load the report again to validate if the AndroidEnterprise filter for the deviceTypeName becomes available. If you continue facing an issue with these categories not available as expected, please open a support case via the Intune Admin console's Help and Support. Our support team would be happy to further assist with resolving your issue. Feel free to direct message us with your support case number for follow up!

Copper Contributor

Thanks, but I am already using the custom OData feed copied from Intune. I have opened a case via Intune two days ago but nobody has contacted me yet.

Copper Contributor

Thanks! very good guide. My data was off until I filtered with the azureADRegistered value. Also, this link is helpful to know which data is in which table, although the names are not up to date https://docs.microsoft.com/en-us/mem/intune/developer/reports-ref-devices

Copper Contributor

Thanks for the article. I'm trying to do the first few steps loading the table "applicationInventories" when I click "load data" it start loading....up to 200MB has taken about an hour on a fast connection is this normal? Trying to get the version of applications deployed.

Copper Contributor

thanks for this article. I want to include the Android devices' WiFi IP address in my report. I cannot find the table that stores such data. Any advise?

 

By the way, as of Sep 2021, the IDW URL is now found in the Reports menu:

 

mobilismexico_0-1630586526300.png

 

Copper Contributor

Hello,

 

I am a bit new here and stuck at the top on step 6:

 

  1. Next Power BI will show you all the available data in the IDW. For this report, we’re going to select the following entities:
    1. currentUsers
    2. devices
    3. deviceTypes
    4. mdmStatuses

CurrentUsers is no longer an option to choose and is missing. Curious on next step or advice?

 

@AzionHzmilton 

@Intune_Support_Team 

 

 

Thanks!

Microsoft

@Supermike84  - currentUsers has been deprecated.  You can use users instead.

Copper Contributor

.

Copper Contributor

@AzionHzmilton Hello! Is this Aaron? I came across a video where you were having a Q&A at the end (19mins) mentioning a step-by-step guide in adding some of the other tables my management wants me to add and of course I cannot find a how-to anywhere and probably invested more time than I should in trying to complete a dashboard task I am assigned too. However, I am a bit new to Power BI and was wondering if you had a link to these other steps or advice to help me?

 

That would really make my day! :) 

 

 

Thanks for any of your support.

Microsoft

Yes, this is Aaron!  The guide is a bit old at this point but you're welcome to a copy: https://1drv.ms/b/s!Aj1BA8LVmk8srPhfypYoyu28XNP7ug?e=3oTMAp 

 

If you're new to Power BI it's not a bad intro.  If you review the Power BI docs site you'll also find some great info as well: Get started with Power BI Desktop - Power BI | Microsoft Docs

Copper Contributor

@AzionHzmilton Thank you very much for responding.

 

I appreciate the advice and info provided for my request. 

 

 

Thanks again.

 

 

Michael

Copper Contributor

 

 

 

Microsoft

@Supermike84 Yes.  There have been some minor changes to entity names since the initial creation of data warehouse.  The data model doc should be used as the authoritative source for what each entity represents: Data Warehouse data model - Microsoft Intune | Microsoft Docs   The MAM specific items are here: Mobile App Management (MAM) - Microsoft Intune | Microsoft Docs

 

 

Copper Contributor

@AzionHzmilton Thank you for the above. However having trouble. Never mind how to even change the timeline to 7 days like:

 

Supermike84_1-1638486188894.png

 

 

But how do you correctly select the fields to get red line of failure "for" count? I've sat here trying and can't match output like the above... Thanks for any direction on this specifically.

 

Supermike84_0-1638486166135.png

I've tried enrollmentfailure to deviceconfigprodevice >failure in fields to visuals/ counts of...

 

 

Thanks!

 

Microsoft

Ping me directly at my microsoft.com email address (it's just first.last @ microsoft.com) and I'll get some time scheduled with you.  It shouldn't take to much time to show you a few basics.

Version history
Last update:
‎Dec 19 2023 01:31 PM
Updated by: