Reporting is a key component in my opinion when working with Microsoft licenses. You should be able to know fairly easily at all times how many licenses you have, how many you have left, and who is using what. This is especially valuable when you are doing a Microsoft True-up, or when you need to report on how much money you are spending on Microsoft licenses for various purposes. There is also the scary factor that you run out of licenses for new users and then get calls from users who can’t access Outlook because they are not licensed, all because you didn’t know upfront.
So, in order to save time and be up-front with my licensing assignments, I decided to create a Power BI report using Microsoft Graph. Here is how I have done it and how it looks
Pre-requisites:
- Service Principal with the following permissions:
Organization.Read.All
User.Read.All - Power BI desktop application
- Power BI workspace (To host the report so others in your org. can view it)
Connect to Microsoft Graph via Power BI:
The connection to Microsoft Graph via Power BI will be done using the data source “Blank query”. By using the blank query, we will get the option to enter custom code to be able to query the Microsoft Graph. We will be using the Service Principal to authenticate with, since we don’t want it to be our own user. If you are comfortable running PowerShell scripts using Microsoft Graph, then this won’t be much different than that. In the Power BI ‘blank query’ we get the option to run code in the programming language ‘M’, and that is the language we will be using to query Microsoft Graph for license information. So, start Power BI, select “Get data”, then choose “Blank query” and in the new pop-out window, select “Advanced editor”.
First, we need to create three Parameters. These will be used to store the ClientID, Secret and Tenant ID that are to be used to authenticate to the Microsoft Graph.
Start by clicking on Manage Parameters and select New Parameter. In name type ClientId and then add the ClientId of the Service Principal in the Current Value box.
Repeat this process for Secret and Tenant Id.
When all of the Parameters are created, we can then proceed to add the Microsoft Graph query to get all the licenses. Select Query1 and then Advanced Editor at the top.
Paste the following query into the editor:
We need to add another query as well (All Users).
Select Done – You will then be presented with a dialog box about Edit credentials – twice. In both cases, connect anonymously.
You will also see a dialog box with Privacy levels. Select Organizational in both and then save.
When all this is done. Select Close & Apply
The Power BI report and fantastic features
Now that we have connected to the Microsoft Graph, we can start to set up the report as we would like it. This is of course up to the individual person to create their own report with the desired information. You can download my version here via this link – But be mindful that you need to edit the Parameters with your own ClientId, Secret and TenantId.
When the report has been designed to your liking, you can then publish it to your Power BI workspace.
Download my report here: https://github.com/ChrFrohn/PowerShell/blob/master/LicenseReport.pbit
Acknowledgments:
I want to give a special thanks to my colleague Ida for designing this report and for inspiring me to write this blog post. Her expertise in Power BI made this solution much more effective and user-friendly.
By creating a Power BI report to show you the information about the license consumption in your company you are able to leverage some of the great features in Power BI such as Set Alert and Subscribe.
The Set Alert feature enables you to configure your own alert thresholds based on the metrics that suit you and to receive notifications via either Teams or Outlook.
The Subscribe button will enable you to receive a report on the schedule you prefer that will show the changes in the report (changes in licenses since last time). You can even get a Copilot generated summary.
These two features are for me the reason I chose to create a license report using Power BI instead of using PowerShell. By leveraging these two features, the users of this report can filter it in the way they want to and get notified the way they want to instead of having to go through me to get a different report etc.
For me this is a great way to combine insight into license usage with self-service for stakeholders.
Tip: You can secure the Service Principal with conditional access in order to protect it better https://www.christianfrohn.dk/2024/04/22/securing-service-principals-in-microsoft-entra-id-with-conditional-access-policies/