Microsoft 365 License overview per user

There are many licenses you can add to your 365 tenant, but it’s difficult to get a good overview of all users with their assigned licenses and what each license plan contains. I wrote a PowerShell script for that, it outputs all the users with their assigned SKU (Short for Stock-Keeping-Unit, but in Microsoft terms a license SKU predefines all the properties of a license, including Product/Version/Features) in a CSV file.

How does the script work?

The biggest problem was how to get a good list of SKUs and what friendly names they have which I could use because… The normal output is something like this:

tenantname:DEFENDER_ENDPOINT_P1
tenantname:Win10_VDA_E3
tenantname:EMS
tenantname:SPE_E3
tenantname:POWER_BI_STANDARD

It gives you a hint, but you don’t know exactly what licensed features are available to the user. Luckily Microsoft has a nice list on their website, https://docs.microsoft.com/en-us/azure/active-directory/enterprise-users/licensing-service-plan-reference, but they also provide a CSV file on that site containing all that information that I could use in my script. Putting those things together resulted in the script below.

The script

In order to get all details from the 365 tenant, you need to have the MSOnline module installed. (https://docs.microsoft.com/en-us/powershell/azure/active-directory/install-msonlinev1?view=azureadps-1.0)

#Connect to MSOL if not connected
Write-Host ("Checking MSOnline module") -ForegroundColor Green
try {
    Get-MsolDomain -ErrorAction Stop | Out-Null
}
catch {
    if (-not (get-module -ListAvailable | Where-Object Name -Match 'MSOnline')) {
        Write-Host Installing MSOnline module.. -ForegroundColor Green
        Install-Module MSOnline
    }
    Connect-MsolService
}
 
#Create table of users and licenses (https://docs.microsoft.com/en-us/azure/active-directory/enterprise-users/licensing-service-plan-reference)
#Download csv with all SKU's
$ProgressPreference = "SilentlyContinue"
Write-Host ("Downloading license overview from Microsoft") -ForegroundColor Green
$csvlink = ((Invoke-WebRequest -Uri https://docs.microsoft.com/en-us/azure/active-directory/enterprise-users/licensing-service-plan-reference).Links | where-Object Href -Match 'CSV').href
Invoke-WebRequest -Uri $csvlink -OutFile $env:TEMP\licensing.csv
$skucsv = Import-Csv -Path $env:TEMP\licensing.csv
$UsersLicenses = @()
foreach ($user in Get-MsolUser -All | Sort-Object UserPrincipalName) {
    if ($user.isLicensed -eq $True) {
        foreach ($License in $User.licenses) {
            $SKUfriendlyname = $skucsv | Where-Object String_Id -Contains $License.AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1
            $SKUserviceplan = $skucsv | Where-Object String_Id -Contains $License.AccountSkuId.Split(':')[1] | Sort-Object Service_Plans_Included_Friendly_Names
            foreach ($serviceplan in $SKUserviceplan) {
                $Licenses = [PSCustomObject]@{
                    User        = $User.UserPrincipalName
                    LicenseSKU  = $SKUfriendlyname.Product_Display_Name
                    Serviceplan = $serviceplan.Service_Plans_Included_Friendly_Names
                }
                $UsersLicenses += $Licenses
            }
        }
    }   
}
 
#Output all license information to c:\temp\userslicenses.csv and open it
$UsersLicenses | Sort-Object User, LicenseSKU, Serviceplan | Export-Csv -NoTypeInformation -Delimiter ';' -Encoding UTF8 -Path c:\temp\userslicenses.csv
Invoke-Item c:\temp\userslicenses.csv

Script Output

After running the script, it will automatically open c:\temp\userslicenses.csv and will look like the screenshot below. For each user, it will output the LicenseSKU with the service plan in it. This way you know which user has a certain license capability and from what bundle it originates. (Office 365 E3, EMS E5, etc.)

Download the script(s) from GitHub here

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.