Microsoft 365 License overview per user

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

How does the script work?

The biggest problem was getting a good list of SKUs and what friendly names they have that I could use because… The standard 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 precisely 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

To get all details from the 365 tenant, you must install the MSGraph module. (https://learn.microsoft.com/en-us/powershell/microsoftgraph/installation?view=graph-powershell-1.0) You need to connect with an account with sufficient permissions. It will ask for Admin Consent if the MS Graph permissions are not already granted.

You can use the -FilterLicenseSKU to search for specific license SKUs or the -FilterServicePlan parameter to search for a particular ServicePlan. (You can only use one of the two License or Service filters, not specifying one gives you all the license details) Also, you can filter the results by using the -FilterUser to specify a part of the user or domain name to query for.

#Use -Filter parameter to only search for specific licenses. 
#For example .\Microsoft_365_License_Overview_per_user.ps1' -FilterLicenseSKU 'Windows 10 Enterprise E3' or
#For example .\Microsoft_365_License_Overview_per_user.ps1' -FilterServicePlan 'Universal Print'
#If -Filter is not used, #all licenses will be reported
[CmdletBinding(DefaultParameterSetName = 'All')]
param (
    [parameter(parameterSetName = "LicenseSKU")][string]$FilterLicenseSKU,
    [parameter(parameterSetName = "ServicePlan")][string]$FilterServicePlan,
    [parameter(Mandatory = $false)][string]$FilterUser
)

#Connect to MSGraph if not connected
Write-Host ("Checking MSGraph module") -ForegroundColor Green
try {
    Import-Module Microsoft.Graph.Identity.DirectoryManagement -ErrorAction Stop
    Import-Module Microsoft.Graph.Users -ErrorAction Stop
    Connect-Graph -Scopes User.ReadWrite.All, Organization.Read.All -ErrorAction Stop | Out-Null
}
catch {
    if (-not (get-module -ListAvailable | Where-Object Name -Match 'Microsoft.Graph.Identity.DirectoryManagement')) {
        Write-Host Installing Microsoft.Graph.Identity.DirectoryManagement module.. -ForegroundColor Green
        Install-Module Microsoft.Graph.Identity.DirectoryManagement, Microsoft.Graph.Users
        Install-Module Microsoft.Graph.Users
        Import-Module Microsoft.Graph.Identity.DirectoryManagement
        Import-Module Microsoft.Graph.Users
    }
    Connect-Graph -Scopes User.Read.All, Organization.Read.All
}
 
#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 -UseBasicParsing).Links | where-Object Href -Match 'CSV').href
Invoke-WebRequest -Uri $csvlink -OutFile $env:TEMP\licensing.csv 
$skucsv = Import-Csv -Path $env:TEMP\licensing.csv -Encoding Default
if ($null -eq $FilterUser) {
    $users = Get-MgUser -All | Sort-Object UserPrincipalName
}
else {
    $users = Get-MgUser -All | Where-Object UserPrincipalName -Match $FilterUser | Sort-Object UserPrincipalName
}
$UsersLicenses = foreach ($user in $users) {
    if ((Get-MgUserLicenseDetail -UserId $user.UserPrincipalname).count -gt 0) {
        Write-Host ("Processing user {0}" -f $user.UserPrincipalName) -ForegroundColor Green
        $Licenses = Get-MgUserLicenseDetail -UserId $user.UserPrincipalname
        foreach ($License in $Licenses) {
            $SKUfriendlyname = $skucsv | Where-Object String_Id -Contains $License.SkuPartNumber | Select-Object -First 1
            $SKUserviceplan = $skucsv | Where-Object GUID -Contains $License.SkuId | Sort-Object Service_Plans_Included_Friendly_Names
            foreach ($serviceplan in $SKUserviceplan) {
                if ($FilterLicenseSKU) {
                    if ("$($SKUfriendlyname.Product_Display_Name)" -match $FilterLicenseSKU) {
                        [PSCustomObject]@{
                            User               = "$($User.UserPrincipalName)"
                            LicenseSKU         = "$($SKUfriendlyname.Product_Display_Name)"
                            Serviceplan        = "$($serviceplan.Service_Plans_Included_Friendly_Names)"
                            AppliesTo          = ($licenses.ServicePlans | Where-Object ServicePlanId -eq $serviceplan.Service_Plan_Id).AppliesTo | Select-Object -First 1
                            ProvisioningStatus = ($licenses.ServicePlans | Where-Object ServicePlanId -eq $serviceplan.Service_Plan_Id).ProvisioningStatus | Select-Object -First 1
                        }
                    }
                }
                elseif ($FilterServicePlan) {
                    if ("$($serviceplan.Service_Plans_Included_Friendly_Names)" -match $FilterServicePlan) {
                        [PSCustomObject]@{
                            User               = "$($User.UserPrincipalName)"
                            LicenseSKU         = "$($SKUfriendlyname.Product_Display_Name)"
                            Serviceplan        = "$($serviceplan.Service_Plans_Included_Friendly_Names)"
                            AppliesTo          = ($licenses.ServicePlans | Where-Object ServicePlanId -eq $serviceplan.Service_Plan_Id).AppliesTo | Select-Object -First 1
                            ProvisioningStatus = ($licenses.ServicePlans | Where-Object ServicePlanId -eq $serviceplan.Service_Plan_Id).ProvisioningStatus | Select-Object -First 1
                        }
                    }
                }
                else {
                    [PSCustomObject]@{
                        User               = "$($User.UserPrincipalName)"
                        LicenseSKU         = "$($SKUfriendlyname.Product_Display_Name)"
                        Serviceplan        = "$($serviceplan.Service_Plans_Included_Friendly_Names)"
                        AppliesTo          = ($licenses.ServicePlans | Where-Object ServicePlanId -eq $serviceplan.Service_Plan_Id).AppliesTo | Select-Object -First 1
                        ProvisioningStatus = ($licenses.ServicePlans | Where-Object ServicePlanId -eq $serviceplan.Service_Plan_Id).ProvisioningStatus | Select-Object -First 1
                    }
                }
            }
        }
    }   
}
 
#Output all license information to c:\temp\userslicenses.csv and open it
if ($UsersLicenses.count -gt 0) {
    $UsersLicenses | Sort-Object User, LicenseSKU, Serviceplan | Export-Csv -NoTypeInformation -Delimiter ';' -Encoding UTF8 -Path c:\temp\userslicenses.csv
    Invoke-Item c:\temp\userslicenses.csv
}
else {
    Write-Warning ("No licenses found, check permissions and/or -Filter value")
}

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.) It also reports the provisioning status and if the license applies to the user or company.

If you used one of the -Filter parameters, the list will only include the filter that was used. If the -Filter parameter didn’t return any results, it will mention that as a warning after running the script. It will also show a warning if no licenses are found at all.

Download the script(s) from GitHub here

12 thoughts on “Microsoft 365 License overview per user

  1. Thanks for the great script! for some reason when it tries to invoke the licensing.csv file it is hanging , I replaced this portion of the code with the actual CSV file which I downloaded from the https://docs.microsoft.com/en-us/azure/active-directory/enterprise-users/licensing-service-plan-reference page , however the end is result is a CSV file with one cell filled with some weird characters . any idea how to overcome this ?

    this is the line of code I used to replace the Invoke Web Request portion :

    $skucsv=$BasePath + “licensing.csv”

    Where licensing.csv is the file I downloaded as I mentioned above.

    Appreciate if you can shed some insights.

    • Had that issue before, invoke-webrequest without -UseBasicParsing can cause the script to hang.. And.. I rewrote it to use the new MS Graph module 🙂 Could you check if this works out for you, test in my CDX tenant worked fine

  2. Great script. Works very well. Looking for a way to integrate the status of a serviceplan (Success, Disabled, Pednding) in the CSV-List. Any hint how to do this? Thanks!

    • Excellent addition! I added the AppliesTo (User or Company) and the Provisioning status to the script now 🙂 Also, an encoding issue resulted in a weird character with some license types. I fixed that too!

    • It’s a fantastic script that works well, but what if we only want to see users licensed with a specific product, like M365 E5?

    • I added a -FilterUser parameter, you can use that to specify a part of the user or Domain Name. For example, -FilterUser ‘Admin’ will output all users with Admin in their username. Specifying the Domain name, -FilterUser ‘@powershellisfun.com’, will also work.

      Updated the blog post to reflect this extra parameter together with the script in the post and on GitHub

Leave a Reply

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