How to Create an Office 365 user license report with PowerShell

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 find all available licenses. But what if there are no more licenses available for a certain product?

I wanted to find out which users were assigned to 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, if you find that the user is attached to a license, write down their name. 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 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.

Getting started: preparing to create a PowerShell list

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

1. Microsoft Online Services Sign-In Assistant for IT Professionals
   http://go.microsoft.com/fwlink/?LinkID=286152
2. Azure Active Directory Module for Windows PowerShell (64-bit version)
   http://go.microsoft.com/fwlink/p/?linkid=236297

You can find more information about how to use PowerShell in your Office 365 tenant here.

Once you’ve installed these tools, you can connect to your tenant: Connect-MsolService

After providing your credentials, you can get and modify information about your Office 365 users.

Step 1: create a list of licensed users in Office 365

The first thing I want to know is how many Office 365 licenses are available:

$MsolAccountSkus = Get-MsolAccountSku

I also want to know all users that consume licenses:

$LicensedMsOnlineUsers = Get-MsolUser | Where-Object { $_.IsLicensed -eq "TRUE" }

$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 have been assigned Office 365 licenses. The actual information about which licenses are assigned 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.

Step 2: create a custom array for an Office 365 licensing report

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.

FUNCTION O365_License_Object($AccountSkuID, $ActiveUnits, $ConsumedUnits 
, $RemainingUnits,){
    $Object = New-Object PSObject 
    $Object | add-member Noteproperty AccountSkuID $AccountSkuID                           
    $Object | add-member Noteproperty ActiveUnits $ActiveUnits
    $Object | add-member Noteproperty ConsumedUnits $ConsumedUnits
    $Object | add-member Noteproperty RemainingUnits $RemainingUnits
    $Object | add-member Noteproperty Users $Users

    return $Object
} #End O365_License_Object

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.

$O365_Licenses = @() 

foreach($MsolAccountSku in $MsolAccountSkus){

    $AccountSkuID = $MsolAccountSku.AccountSkuID
    $ActiveUnits = $MsolAccountSku.ActiveUnits
    $ConsumedUnits = $MsolAccountSku.ConsumedUnits
    $RemainingUnits = $ActiveUnits - $ConsumedUnits

    $O365_License = O365_License_Object $AccountSkuID $ActiveUnits $ConsumedUnits $RemainingUnits
    $O365_Licenses += ,$O365_License
}

Step 3: generate an Office 365 licensing report

The next step is to find out which licenses were assigned to which users. For this, 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.

foreach($O365_License in $O365_Licenses){
    
    $Users = @()
     
    foreach($LicensedMsOnlineUser in $LicensedMsOnlineUsers){
        if($LicensedMsOnlineUser.Licenses.AccountSkuId.Contains($O365_License.AccountSkuID)){
            $Users += $LicensedMsOnlineUser.UserPrincipalName
        }
    }
    $O365_License.Users += $Users
}

Now every item in $O365_Licenses is an object that contains the information I wanted to attain. For example:

You can now proceed to visualize this information, for example by creating a HTML table with an overview of all licenses and all users.