Script to dump all your assigned Office 365 user licenses

As part of a licence transition from one level of office 365 to another, I wanted a way to record/audit who had what licences before/after the audit.

The script below dumps all the relevant information to a tab delimited text file.

Before we get to the script, I want to talk about some of the design considerations….

If we were to normalize the data we’d have 3 different sources of raw data:

  • List of users
  • List of possible licenses levels
  • List of possible sublicense levels.

To record all this in a relational database, you’d want a table for each of the above.

Then you’d need a few table to record the relationships and which users have which licenses.

Since many business users aren’t comfortable with relational data structures, I decided to de-normalize the data in favor of making it easy to consume.

In english, that means that much of the data will be repeated.

The fields we collect are:

  • UPN (usually email)
  • Lastname
  • FirstName
  • Displayname (It’s common for utility accounts like conference rooms to not have a first/last name so I include the displayname)
  • The Name of the Licence assigned to the user (the ‘primary’ license, otherwise known as the “MSOLAccountSKU”
  • The name of the sublicense that goes with the Primary *(See below)*
  • The deployment status of the sublicense

 

Notes about the ‘sublicense’ what I mean here is this: Say you have an E3 license, this is composed of a bunch of ‘sub’ liceneses that you can turn on-off independent of one another, examples of sub-licenses would be Exchange, SharePoint, Sway, Office Online, etc…

The sublicense values are not uniqe, for example, both E1 and E3 have a ‘Sway’ sublicense type.

Here’s another interesting tidbit: All primary licenses have at least one sublicense.

I wanted to flatten the data as much as possible so the sublicense field is a combo of the primary and sublicense

This will all be a lot clearer once you run the script so here it is…

 

Leave a Reply