Using a Microsoft Fabric Notebook to get all Power BI Licenses
In this blog post I am going to show you how to get all the Power BI licenses in your tenant.
This can be very useful to understand how many licenses you have, what type of licenses are being paid for, and potentially how you can save by removing licenses due to inactive use or if the licenses are no longer required.
I’m going to be pulling on my previous Blog post where I explained how to get the Entra ID users and groups using a Service Principal for access
Notebook to get all user licenses
The details of the notebook code below will explain how I am going through and getting all the user licenses in my tenant.
NOTE: The reason I am doing it this way is because it will make me easier to find which licenses are for Power BI in the subsequent steps, instead of trying to figure this out as part of the initial API call.
In the previous blog post I had got all the user details, which included the User Principal Name (UPN). As shown below this is where I can see the UPN for my tenant
The section below is where I am then completing the following:
- LINES 19 – 25
- This is where I am getting all the UPNs
- LINES 28 – 35
- This is where I am creating the Dataframe and then creating the data to be looped through using “collect()” method.
The section below is where I am looping through all the data and then saving the output to a Lakehouse table. Some details I will call out below.
- LINE 45 & LINE 87
- This is where I am putting a Try and exception, because not all the UPNs will have any licenses, so if it fails it will allow the code to continue.
- LINE 48
- This is where I am passing the UPN to the Graph API, and it is then getting the License Name, which is known as the “skuPartNumber”
- NOTE: I have previously authenticated with the Service Principal in previous steps.
- LINES 61 – 70
- This is where I am getting the response from the API and appending the information to the “all_upn_licenses”
- LINES 72 – 81
- This is where I am creating the dataframe, adding in the Insert Date Time, so I know when it was inserted and finally writing the information to my Lakehouse staging table.
When the notebook cell is running, I can then see the output of the UPN to make sure that it is running as expected.
In the final code cell, is where I am either inserting or updating existing rows into my final table.
Here you can see below the details in my table “tb_Entra_ID_User_Licenses”
Additional functionality when you have the license data
A good case for getting the Power BI licenses is that I can now use this information with my Power BI Audit Log to see which users have not logged in and what licenses they have assigned to them.
Having users assigned to licenses that are not being used, means there could be significant cost savings when you can see this data.
Summary
In this blog post I have shown you how to get the user licenses in your tenant.
As well as showing some additional functionality to join this with audit data to understand which licenses could be removed.
Here is a link to the Notebook shown above: Fabric/Blog – Get All Entra ID Groups and Users and Licenses.ipynb at main · GilbertQue/Fabric
Thanks for reading and if you have any questions or comments, please let me know.