Monthly Archives: July 2016

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…


PS Script to list all Office 365 licenses and sublicenses available (AccountSKUID and ServiceName)

We’re transitioning some licensing at work and I thought it would be helpful to have a ‘catalog’ of all the available options.

The script below will produce such a list…