How to Create an Office 365 user license report with PowerShell | proMX
Technology Blog

Technology Blog

Technology Blog

How to Create an Office 365 user license report with PowerShell

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?assign_license

 

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:

  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:

https://msdn.microsoft.com/en-us/library/jj151815.aspx#bkmk_installmodule

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.

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.accountskuid

 

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:licensedmson

 

$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.

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
}

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.

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:365_licenses

 

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

Leave a comment

Notify me of new comments via e-mail

You can always learn more

Subscribe to newsletter

I accept that this site uses cookies for analysis, personalized content and advertisment.