Intune and PowerBI Deep Dive - Part 1 - Where to Start

In this series, I will guide you through the process of extracting data from Microsoft Graph, specifically Microsoft Intune, and integrating it into PowerBI. We will explore different approaches, considering their advantages and disadvantages, in order to provide you with a solid foundation for creating your own reports within your specific environment. Its intent is to take you on a journey as I discover what to do, so please do take the time to read them in order to ensure you come along for the ride! I have invested significant time and effort into studying this subject and understanding the potential pitfalls associated with various approaches. Whilst undertaking this journey I also took and passed my PL-400 exam, an achievement I am very proud of. Throughout this series, we will cover the prerequisites, data ingestion methods, the approach I personally adopt, and I will share some of my queries. Additionally, I aim to demonstrate effective strategies for managing this integration. So… lets get to it.

Quick Links

Unfortunately, Microsoft doesn’t offer a straightforward method for extracting data from Intune and importing it into PowerBI as you would expect. They offer one solution which is Intune the data warehouse OData feed. Using traditional advanced techniques like leveraging functions in Power Query to bypass this limitation, handle pagination of the API call and query the Microsoft Graph directly, the resulting output cannot be published into the PowerBI because you are using hand-authored queries.

Consequently, we must confront these obstacles head-on and devise a strategy to overcome them. The objective is to create a robust and dynamic report that provides up-to-date insights, ideally querying the graph directly so we can refresh at any moment and get data which is as up to date as can be.

Lets take a look first at the Intune Data Warehouse, and why not to use it.

Intune Data Warehouse (Explore the tabs)

Exploring this method

Having explored this approach and successfully imported the data into PowerBI, I must admit that I was not particularly impressed. While there are certain datasets that may prove useful, the overall quality is lacking. Establishing relationships between tables would require a considerable amount of effort since they are not pre-configured. Moreover, upon closer examination, it becomes apparent that the so-called “data” is not comprehensive data and how it can be used for business purposes to report on business critical functions is very questionable . Although there are some details available for devices and users, the amount of useful data is minimal. As a huge fan of Microsoft Graph, I find that the data retrieved through Graph queries far surpasses the information obtained from the Intune Data Warehouse feed.

Here you can see me clicking through the data, you may notice lots of nested reference number tables you would have to create multiple relationships for which is sort or remnant of tables inside the Configuration Manager database

Marks out of ten?

• Ease to set up? 9/10 - Easy to grab the unique URL from the Intune console and use.
• Data? 2/10 - Not much useful data to use, especially for business reasons.
• Overall result - 4/10

Getting Microsoft Graph query data into PowerBI

It became clear at this point that the better option would be to query the graph directly but previously, having used the Graph Explorer, I’ve always authenticated with a username and password, getting data into PowerBI especially using a web input feed, was going to be interesting. In simplistic terms you’d be forgiven for thinking you could go grab the Graph URL and use it as a web input in PowerBI, then simply add your credentials and off you go. Sadly, this doesnt work.

So what is the solution? Well, we can leverage a couple of different approaches. Web.Contents and OData.Feed are the two I will cover in this series. Although I’ll show you two methods for Web.Contents. All of these methods require an application registration in Azure which will allow (once permissions are granted) access to data.

In the next post, I will explain how to set up an Application Registration as an intermediary for authentication between you and the Microsoft Graph and how to grant it permissions to access data from your tenant. By granting these application permissions, you can bypass the need for entering a username and password and authenticate from PowerBi directly. Similar to API Keys in other APIs you may have come across, the application registration serves as a centralized authorization mechanism for accessing the data.

Join me in Part 2 where I go into details about how to set one up.

Links and References