A common request I’m hearing from my customers is visibility of Office365 licensing. Typically this is more from the management staff over the technical team as they don’t have the know-how to get the info themselves. From a management perspective it is also about making sure they get full use of their licensing entitlements. Also to know when they are running close to their licensing limit and the conversations about procuring additional licenses need to be had.
In this post I detail how I’m using PowerShell, the Granfeldt PowerShell Management Agent, the Lithnet MIIS PowerShell module, Microsoft Identity Manager and PowerBI to build a simple reporting dashboard showing user Office365 license assignment as well as tenant Office365 licensing status.
- Granfeldt Powershell Management Agent
- Microsoft Identity Manager
- This is of course where the data is synchronised too
- Lithnet MIIS PowerShell Module
The highlevel process goes like this:
- Office 365 Tenant and User License info synchronises into the MIM Metaverse via the Office365 PowerShell MA. See how to here.
- Using the Lithnet MIIS PowerShell Module query the Metaverse to get User and Tenant Licensing information
- Using the PowerBIPS Module we create the Dataset and Tables in PowerBI
- Using the PowerBIPS Module we take the information from the query in step 2 and populate the tables in PowerBI
- In PowerBI we create our licensing Report and Dashboard, and share it with whoever needs the information
Office 365 Reporting in PowerBI from Microsoft Identity Manager
Here are Steps 2-5 from the Logic Overview.
Using the Lithnet MIIS PowerShell Module we query the MV to get User and Tenant Licensing information
As indicated in the other posts I have an ObjectType in the Metaverse for LicencePlans. On each user I also have the assigned and provisioned licenses.
The image below shows my LicensePlans MV ObjectType.
The image below shows my Person MV ObjectType with assigned and provisioned licenses.
Below is the PowerShell to query my Metaverse to get the licensing information.
Using the PowerBIPS Module we create the Dataset and Tables in PowerBI
Using the PowerBIPS PowerShell module we create the Dataset, Tables and Schema to hold the licensing data.
Using the PowerBIPS Module we take the information from the query in step 2 and populate the tables in PowerBI
The following commands use the defaults for the PowerBIPS module and will export your entire dataset to PowerBI.
In PowerBI we create our licensing Report and Dashboard, and share it with whoever needs the information
Now in PowerBI you will see you Dataset with the four tables containing the data you exported from the Metaverse.
It’s time to create a report by selecting columns from the tables. Pin your report(s) to an existing or new Dashboard. Customise as required and share with whoever needs the info.
Using PowerShell I’ve shown:
- Importing Office365 User and Tenant Licensing information to Microsoft Identity Manager via the Granfeldt PowerShell Management Agent
- Using the Lithnet MIIS PowerShell Module I’ve extracted Office365 User and Tenant Licensing information from the Microsoft Identity Manager Metaverse
- Using the PowerBI PowerShell Module I’ve created a Dataset in PowerBI and exported the Office365 User and Tenant Licensing information
All the data is present for any report you may want around O365 licensing. Using the PowerBI PowerShell Module you can flush the tables and refresh the data as required.
Follow Darren on Twitter @darrenjrobinson