Office 365 License Reporting

UPDATE 2014/14/09: New version 0.6 has been released to support Power BI for Office 365 licenses.

I’ve been looking for some way of reporting on Office 365 licenses assigned to users and creating a simple Pivot Chart to get an overview of the licenses.

There are alot of PowerShell scripts out there, but the ones I found didn't really fit my requirements.

I decided to throw something together to help me with my requirements ,nothing fancy, but I came with the following.

Basically, I’m using Excel to call PowerShell to export the raw license data to CSV. Once the export process has finished you just use the import button to import the CSV into the Excel sheet. The Pivot Chart will then update with the relevant data.

The Chart will look like this:

 

You can manipulate the Chart by filtering on AccountSKU, ServiceName and ProvisioningStatus.

I re-used some code in the PowerShell from Alan Byrne's script, but I needed the licensing data in each row to create a report on different SKU’s. So my script basically loops through each user and export SKU’s and Service names against every user.

I hope this helps you to get an overview on your licenses assigned on your tenant.

You can download the tool here

This requires the Windows Azure PowerShell module to work.

I also included this feature in my Migration Management tool to help keep track of licenses during a migration.

If you have any feedback, don’t hesitate to ping me at technicalramblings@outlook.com

Until next time,

Michael Hall (MCS)

Comments

• Anonymous
  January 01, 2003
  How can I add the DomainName or the UPN in the Excel report?

• Anonymous
  January 01, 2003
  Great story

• Anonymous
  January 01, 2003
  Mike, This is a great script. One question though. How are you writing the column titles i.e. DisplayName, AccountSku etc. AND is there a way to pull an ExtendedAttribute (per user) from AD/Azure and sort the data by that attribute. Thanks Much!!

• Anonymous
  March 19, 2014
  Thanks for saving me HOURS of work

• Anonymous
  April 15, 2014
  This is an AWESOME tool! I added UPN by unhiding the Scripts tab in Excel and carefully ... carefully adjusting two lines. I also wanted the UPN so I could use the output from Michael Halls awesome tool to make it an INPUT file for my GRANULAR LICENSING PowerShell Script.
  The lines to adust: 114 and 115 ... added this ;"UPN" = $MSOLUser.userprincipalname to 114 and then just ,UPN to 115
  The buttons now have text under them and that does not look great. But it did not breat the Pivot Table Output tab.
  Amazing work, Mr. Hall.
  

  • Anonymous
    May 20, 2016
    Great script and thanks for the tip on UPN, David Grand. For me, the lines to update were 122 and 123. Nice work again!

• Anonymous
  July 10, 2014
  Great job, you should add a colum with the number of licenses available / total licenses purchased

• Anonymous
  January 31, 2016
  Mike, I don't usually post on here, but I just wanna say THANK YOU for the work you did there. Saved me about a week of hard work.
  
  Thank you

• Anonymous
  June 13, 2016
  I am not able to view Alan Byrne's script, can you please send me over that ?