How to Create an Office 365 user license report with PowerShell

Jan 11, 2017

Nils Pelzer

Usually, when creating a new user in Office 365, the first thing you do is assign a license to this user. You navigate to the user’s license overview in the Office 365 Portal and will find all available licenses. But what if there are no more licenses for a certain product available?

I wanted to find out which users consume those six Visio licenses. Unfortunately, the only way to get this information via the Office 365 Portal is to go through every user’s license overview and write down the name if you find that the user is consuming a license. Apparently, license information is stored only inside a user object – there is no overview that tells you which users have been assigned a specific license.

So I decided to take a look into what’s possible with PowerShell – and I found a solution to my problem! But why only check the licenses for one product? I decided to write a small script that gives me all users for all licenses.

To connect to your Office 365 tenant via PowerShell, you will need two things:

$MsolAccountSkus is an array of objects that contains information about every available license, such as ID, how many licenses were consumed and how many are still available.

The AccountSkuId is a combination of your tenant’s name and the name of the license.

$LicensedMsOnlineUsers is an array of all user objects that consume Office 365 licenses. The actual information about which licenses are consumed is stored in the Licenses.AccountSkuID property of the user object. It is an array with all assigned AccountSkuIDs. For example:

$MsolAccountSkus and $LicesedMsOnlineUsers are basically what you can see in a user’s license overview of the Office 365 portal.

There are many different ways to achieve what I was looking for. I decided to collect every piece of information that I needed and store it in a custom object. Those objects are then stored in the array $O365_Licenses.

This function creates a custom object that can store the name of the license (AccountSkuID), how many licenses are available in your Office 365 tenant (ActiveUnits), how many licenses are already assigned to users (ConsumedUnits), how many licenses are still available (RemainingUnits) and which users these licenses were assigned to (Users).

For every item in $MsolAccountSkus, I collect the information, create the new custom object, and store it in $O365_Licenses.

The next step is to find out which licenses were assigned to which users. I loop through every item in $O365_Licenses and check if there are any items in $LicensedMsOnlineUsers that have a matching AccountSkuID. If so, the users UPN is stored in the .users property of the $O365_License object.