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

• Granfeldt Powershell Management Agent
  • checkout this post where I bring in the Office365 Licensing info to MIM
• Microsoft Identity Manager
  • This is of course where the data is synchronised too
• Lithnet MIIS PowerShell Module
  • checkout this post for more of a background on getting and using it 
•  PowerBI
  • To use PowerBI as the target for the reports via PowerShell you’ll need to get:
    • PowerBI Powershell Module (PowerBIPS)
    • and create a PowerBI Client App

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