Use Intune Data Warehouse in PowerBI
The Intune data warehouse is a repository of information gathered by the Intune Service that you see and learn more about the hardware and software that the service is managing. To use the OData feed in Power BI to connect to the Intune data warehouse, you will need basically the URL for the OData feed and the necessary credentials to connect to it. From here on you can create Custom Reports like this one: “Visualizing Windows End of Life”.
Table of Contents
Create a Custom Report to Visualizing Windows End of Life Devices
There are several important reasons why your organization should not have a device running a Windows version that is end of life:
- Security risks
- Compliance issues
- Limited support
- Limited compatibility
- Loss of productivity
Connect Intune Data Warehouse
First, we connect the Intune OData feed source to PowerBI.
To be able to connect them you have to authenticate with an admin account. In principle, the account needs the role “Intune Administrator”.
Next the data we need for the report can be found in the table “devices”. As you can see there is a lot of awesome informations here which for other purposes maybe is interesting, especially “dates”.
After confirming this with “Load” the data will be imported into PowerBI.
Connect endoflife.date API Json
On the Internet there are many great websites which are free to use, one of them is definitely endoflife.date. This site shows which Windows versions no longer receive security updates and many more.
The platform even offers an API which we are now taking advantage of. What a great Opportunity for everbody.
The API is well described on the website and must be checked out.
No login is necessary here and the PowerQuery Editor can be left as it is.
Finally we have now imported all the data and table. Which we now somehow still need to connect with each other.
Manage Relationships in PowerBI
Since the table have different values in the osVersion / buildID column we have to mutate this in advance.
To adjust this in the “devices” table we need to start the PowerQuery editor of the table. After that the osVersion column must be split.
We now divide the column from left to 10 characters. We do this so that we have the same data in the “devices” and the “windows” table. Which we can then connect with each other.
The column has now been split into “osVersion.1” and “osVersion.2”.
Under Relationship we can now link the tables together. To do this, simply drag the “osVersion.1” column onto the “buildID”.
Now the relationship manager goes up.
We make a “many to many” connection, the openting window you can close as it is with “OK” without adjustment.
The relationship between the tabs was successfully created.
Create a Custom Report to Visualize EOL
Now I wish you success in creating your custom report. Here is an example from me how this could look like.
The great thing about this is that with the right filter column now all devices are displayed which have e.g. “Windows 10 2004” which is End of Life.
I hope I was able to show the topic to some extent. The data that Intune provides must bring you Opportunities to make improvements.
Conclusion
Overall, it’s important to keep track of the end-of-life status of the devices in your organization and to upgrade or replace them as necessary in order to maintain security, compliance, and productivity. And again big shoutout to endoflife.date!
If you want to learn more about Intune capabilities, the following posts might also be something for you:
