Report Office 365 License Assignments to User Accounts

Quick PowerShell Script Does the Trick

Browsing the personal blog of Vasil Michev, the esteemed technical editor of the Office 365 for IT Pros eBook (I have to call him that as otherwise he gets very vexed and causes problems when he edits a chapter), I found an interesting post about using names specified in a CSV file to remove licenses from Office 365 accounts. In fact, I found a logic bug in Vasil’s PowerShell code, which neatly reversed the normal situation when he criticizes my poor attempts at coding.

In any case, the thought came to me that it would be useful to have a script that reported the license assignments to users and output a CSV file that an Office 365 administrator could either use for their own purposes or as an input to Vasil’s script.

You can find license assignment information in the Billing section of the Office 365 Admin Center (select Licenses – Figure 1).

The Office 365 Admin Center also supports the option of exporting license information, but only after you choose a specific license. Anyway, it’s nice to be able to do your own thing in terms of automating administrative processes, which is what PowerShell is all about.

Report Office 365 License Assignments

The quick and dirty PowerShell code in this script fetches details of all licensed accounts in an Office 365 tenant and extracts the license assignment information from each account. The information is written into an array that’s then grouped to calculate a number for each license assignment. We then write the information about to the CSV file, taking care to sort it by license and displayname (just to show how to do multi-property sorts in PowerShell). Here’s the code:

# Quick and dirty code to create a report of license assignments in an Office 365 tenant
$Report = [System.Collections.Generic.List[Object]]::new() 
$Users = Get-MsolUser -All | Where {$_.isLicensed -eq $true} | Select UserPrincipalName, DisplayName, Department, IsLicensed      
Write-Host "Processing Users"
ForEach ($User in $Users) {
   $SKUs = @(Get-MsolUser -UserPrincipalName $User.UserPrincipalName | Select -ExpandProperty Licenses)
   ForEach ($Sku in $Skus) {   
   $ReportLine = [PSCustomObject]@{ 
        User  = $User.UserPrincipalName
        SKU   = $Sku.AccountSkuId.Split(":")[1]
        Name  = $User.DisplayName
        Dept = $User.Departmnent} 
   $Report.Add($ReportLine) }
}
Cls
# Write out the information
Write-Host "License information"
$Groupdata = $Report | Group-Object -Property SKU
$Groupdata | Sort Count -Descending | Select Name, Count
# Set sort properties so that we get ascending sorts for one property after another
$Sort1 = @{Expression='SKU'; Ascending=$true }
$Sort2 = @{Expression='Name'; Ascending=$true }

$Report | Select SKU, Name, User | Sort-Object $Sort1, $Sort2 | Export-CSV c:\Temp\UserLicenses.CSV -NoTypeInformation

Figure 2 shows what the on-screen output looks like:

While Figure 3 shows how the data appears in the CSV file.

If you want to make the license names clearer (for example, to translate ENTERPRISEPACK to Office 365 E3), you can add some code to replace the license name before writing it to the output array.

Update May 30, 2020: The code in the GitHub version of the script shows how to resolve SKU names to user-friendly license names.

Another idea is to increase the number of account properties written out to the CSV file to make analysis easier or more productive. For example, you could include properties such as UsageLocation (Country), City, or Department to focus in on license usage in those areas.

---

The intricacies of dealing with Office 365 licenses via PowerShell are explained in the Office 365 for IT Pros eBook. We might even add this script in a future update!