Overview

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.

https://dl.dropboxusercontent.com/u/76015/BlogImages/PowerBIReporting%20O365%20Licensing/PBI-Dashboard.png

Prerequisites

Logic Overview

The highlevel process goes like this:

  1. Office 365 Tenant and User License info synchronises into the MIM Metaverse via the Office365 PowerShell MA. See how to here.
  2. Using the Lithnet MIIS PowerShell Module query the Metaverse to get User and Tenant Licensing information
  3. Using the PowerBIPS Module we create the Dataset and Tables in PowerBI
  4. Using the PowerBIPS Module we take the information from the query in step 2 and populate the tables in PowerBI
  5. 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.

https://dl.dropboxusercontent.com/u/76015/BlogImages/PowerBIReporting%20O365%20Licensing/MV-LicensePlans.png

The image below shows my Person MV ObjectType with assigned and provisioned licenses.

https://dl.dropboxusercontent.com/u/76015/BlogImages/PowerBIReporting%20O365%20Licensing/MV-UserDetails.png

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.

https://dl.dropboxusercontent.com/u/76015/BlogImages/PowerBIReporting%20O365%20Licensing/PBI-Tables.png

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.

https://dl.dropboxusercontent.com/u/76015/BlogImages/PowerBIReporting%20O365%20Licensing/Dashboard.png

Summary

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.

Enjoy.

Follow Darren on Twitter @darrenjrobinson

 

 

Category:
Business Intelligence, FIM, Identity and Access Management, Power BI, PowerShell

Join the conversation! 8 Comments

  1. Would this data be in the AADSyncMetaverse and could this idea be adapted to pull the info from their rather than MIM

  2. Is there any other way to get the total amount of licenses to show up rather than all of these steps? I’ve been playing around with the Office 365 Adoption package and they have just about everything in there except for total amount of licenses available. I’m trying to figure out if its just missing query statement or if I have to go through all these steps just to get a simple number.

    • You could do a simplified version and store the count and plans in the MIM Service as custom objects and then fund them through the UI.

Comments are closed.