Get Friendly License Name for all Users in Office 365 Using PowerShell
Table of Contents
When you want to look up a users license in Office 365 using PowerShell you are presented with a unfriendly Sku. Sometimes the Sku and the actual license name are similar but sometime it’s hard to distinguish the name from the Sku.
Using a hash table we can convert the Sku to its friendly name an have PowerShell do a lookup.
The script will first lookup all users that are currently licensed so it does not attempt to look up a null value. It will then go through each licensed user, take all of their licenses and put it in an array, do a lookup on each license they have and export the user’s Display Name and friendly license name to a CSV file. It will append the results to the CSV so it does not overwrite the file.
Single Tenant:
The results will display the user and their friendly license name. This is extremely helpful if a client requests a report of user licenses and doesn’t want to keep looking up the SKU’s manually.
The Script
$CSV = "C:\Scripts\Office_365_User_Licensing.csv"
$Sku = @{
"O365_BUSINESS_ESSENTIALS" = "Office 365 Business Essentials"
"O365_BUSINESS_PREMIUM" = "Office 365 Business Premium"
"DESKLESSPACK" = "Office 365 (Plan K1)"
"DESKLESSWOFFPACK" = "Office 365 (Plan K2)"
"LITEPACK" = "Office 365 (Plan P1)"
"EXCHANGESTANDARD" = "Office 365 Exchange Online Only"
"STANDARDPACK" = "Enterprise Plan E1"
"STANDARDWOFFPACK" = "Office 365 (Plan E2)"
"ENTERPRISEPACK" = "Enterprise Plan E3"
"ENTERPRISEPACKLRG" = "Enterprise Plan E3"
"ENTERPRISEWITHSCAL" = "Enterprise Plan E4"
"STANDARDPACK_STUDENT" = "Office 365 (Plan A1) for Students"
"STANDARDWOFFPACKPACK_STUDENT" = "Office 365 (Plan A2) for Students"
"ENTERPRISEPACK_STUDENT" = "Office 365 (Plan A3) for Students"
"ENTERPRISEWITHSCAL_STUDENT" = "Office 365 (Plan A4) for Students"
"STANDARDPACK_FACULTY" = "Office 365 (Plan A1) for Faculty"
"STANDARDWOFFPACKPACK_FACULTY" = "Office 365 (Plan A2) for Faculty"
"ENTERPRISEPACK_FACULTY" = "Office 365 (Plan A3) for Faculty"
"ENTERPRISEWITHSCAL_FACULTY" = "Office 365 (Plan A4) for Faculty"
"ENTERPRISEPACK_B_PILOT" = "Office 365 (Enterprise Preview)"
"STANDARD_B_PILOT" = "Office 365 (Small Business Preview)"
"VISIOCLIENT" = "Visio Pro Online"
"POWER_BI_ADDON" = "Office 365 Power BI Addon"
"POWER_BI_INDIVIDUAL_USE" = "Power BI Individual User"
"POWER_BI_STANDALONE" = "Power BI Stand Alone"
"POWER_BI_STANDARD" = "Power-BI Standard"
"PROJECTESSENTIALS" = "Project Lite"
"PROJECTCLIENT" = "Project Professional"
"PROJECTONLINE_PLAN_1" = "Project Online"
"PROJECTONLINE_PLAN_2" = "Project Online and PRO"
"ProjectPremium" = "Project Online Premium"
"ECAL_SERVICES" = "ECAL"
"EMS" = "Enterprise Mobility Suite"
"RIGHTSMANAGEMENT_ADHOC" = "Windows Azure Rights Management"
"MCOMEETADV" = "PSTN conferencing"
"SHAREPOINTSTORAGE" = "SharePoint storage"
"PLANNERSTANDALONE" = "Planner Standalone"
"CRMIUR" = "CMRIUR"
"BI_AZURE_P1" = "Power BI Reporting and Analytics"
"INTUNE_A" = "Windows Intune Plan A"
"PROJECTWORKMANAGEMENT" = "Office 365 Planner Preview"
"ATP_ENTERPRISE" = "Exchange Online Advanced Threat Protection"
"EQUIVIO_ANALYTICS" = "Office 365 Advanced eDiscovery"
"AAD_BASIC" = "Azure Active Directory Basic"
"RMS_S_ENTERPRISE" = "Azure Active Directory Rights Management"
"AAD_PREMIUM" = "Azure Active Directory Premium"
"MFA_PREMIUM" = "Azure Multi-Factor Authentication"
"STANDARDPACK_GOV" = "Microsoft Office 365 (Plan G1) for Government"
"STANDARDWOFFPACK_GOV" = "Microsoft Office 365 (Plan G2) for Government"
"ENTERPRISEPACK_GOV" = "Microsoft Office 365 (Plan G3) for Government"
"ENTERPRISEWITHSCAL_GOV" = "Microsoft Office 365 (Plan G4) for Government"
"DESKLESSPACK_GOV" = "Microsoft Office 365 (Plan K1) for Government"
"ESKLESSWOFFPACK_GOV" = "Microsoft Office 365 (Plan K2) for Government"
"EXCHANGESTANDARD_GOV" = "Microsoft Office 365 Exchange Online (Plan 1) only for Government"
"EXCHANGEENTERPRISE_GOV" = "Microsoft Office 365 Exchange Online (Plan 2) only for Government"
"SHAREPOINTDESKLESS_GOV" = "SharePoint Online Kiosk"
"EXCHANGE_S_DESKLESS_GOV" = "Exchange Kiosk"
"RMS_S_ENTERPRISE_GOV" = "Windows Azure Active Directory Rights Management"
"OFFICESUBSCRIPTION_GOV" = "Office ProPlus"
"MCOSTANDARD_GOV" = "Lync Plan 2G"
"SHAREPOINTWAC_GOV" = "Office Online for Government"
"SHAREPOINTENTERPRISE_GOV" = "SharePoint Plan 2G"
"EXCHANGE_S_ENTERPRISE_GOV" = "Exchange Plan 2G"
"EXCHANGE_S_ARCHIVE_ADDON_GOV" = "Exchange Online Archiving"
"EXCHANGE_S_DESKLESS" = "Exchange Online Kiosk"
"SHAREPOINTDESKLESS" = "SharePoint Online Kiosk"
"SHAREPOINTWAC" = "Office Online"
"YAMMER_ENTERPRISE" = "Yammer Enterprise"
"EXCHANGE_L_STANDARD" = "Exchange Online (Plan 1)"
"MCOLITE" = "Lync Online (Plan 1)"
"SHAREPOINTLITE" = "SharePoint Online (Plan 1)"
"OFFICE_PRO_PLUS_SUBSCRIPTION_SMBIZ" = "Office ProPlus"
"EXCHANGE_S_STANDARD_MIDMARKET" = "Exchange Online (Plan 1)"
"MCOSTANDARD_MIDMARKET" = "Lync Online (Plan 1)"
"SHAREPOINTENTERPRISE_MIDMARKET" = "SharePoint Online (Plan 1)"
"OFFICESUBSCRIPTION" = "Office ProPlus"
"YAMMER_MIDSIZE" = "Yammer"
"DYN365_ENTERPRISE_PLAN1" = "Dynamics 365 Customer Engagement Plan Enterprise Edition"
"ENTERPRISEPREMIUM_NOPSTNCONF" = "Enterprise E5 (without Audio Conferencing)"
"ENTERPRISEPREMIUM" = "Enterprise E5 (with Audio Conferencing)"
"MCOSTANDARD" = "Skype for Business Online Standalone Plan 2"
"PROJECT_MADEIRA_PREVIEW_IW_SKU" = "Dynamics 365 for Financials for IWs"
"STANDARDWOFFPACK_IW_STUDENT" = "Office 365 Education for Students"
"STANDARDWOFFPACK_IW_FACULTY" = "Office 365 Education for Faculty"
"EOP_ENTERPRISE_FACULTY" = "Exchange Online Protection for Faculty"
"EXCHANGESTANDARD_STUDENT" = "Exchange Online (Plan 1) for Students"
"OFFICESUBSCRIPTION_STUDENT" = "Office ProPlus Student Benefit"
"STANDARDWOFFPACK_FACULTY" = "Office 365 Education E1 for Faculty"
"STANDARDWOFFPACK_STUDENT" = "Microsoft Office 365 (Plan A2) for Students"
"DYN365_FINANCIALS_BUSINESS_SKU" = "Dynamics 365 for Financials Business Edition"
"DYN365_FINANCIALS_TEAM_MEMBERS_SKU" = "Dynamics 365 for Team Members Business Edition"
"FLOW_FREE" = "Microsoft Flow Free"
"POWER_BI_PRO" = "Power BI Pro"
"O365_BUSINESS" = "Office 365 Business"
"DYN365_ENTERPRISE_SALES" = "Dynamics Office 365 Enterprise Sales"
"RIGHTSMANAGEMENT" = "Rights Management"
"PROJECTPROFESSIONAL" = "Project Professional"
"VISIOONLINE_PLAN1" = "Visio Online Plan 1"
"EXCHANGEENTERPRISE" = "Exchange Online Plan 2"
"DYN365_ENTERPRISE_P1_IW" = "Dynamics 365 P1 Trial for Information Workers"
"DYN365_ENTERPRISE_TEAM_MEMBERS" = "Dynamics 365 For Team Members Enterprise Edition"
"CRMSTANDARD" = "Microsoft Dynamics CRM Online Professional"
"EXCHANGEARCHIVE_ADDON" = "Exchange Online Archiving For Exchange Online"
"EXCHANGEDESKLESS" = "Exchange Online Kiosk"
"SPZA_IW" = "App Connect"
"WINDOWS_STORE" = "Windows Store for Business"
"MCOEV" = "Microsoft Phone System"
"VIDEO_INTEROP" = "Polycom Skype Meeting Video Interop for Skype for Business"
"SPE_E5" = "Microsoft 365 E5"
"SPE_E3" = "Microsoft 365 E3"
"ATA" = "Advanced Threat Analytics"
"MCOPSTN2" = "Domestic and International Calling Plan"
"FLOW_P1" = "Microsoft Flow Plan 1"
"FLOW_P2" = "Microsoft Flow Plan 2"
"CRMSTORAGE" = "Microsoft Dynamics CRM Online Additional Storage"
"SMB_APPS" = "Microsoft Business Apps"
"MICROSOFT_BUSINESS_CENTER" = "Microsoft Business Center"
"DYN365_TEAM_MEMBERS" = "Dynamics 365 Team Members"
"STREAM" = "Microsoft Stream Trial"
"EMSPREMIUM" = "ENTERPRISE MOBILITY + SECURITY E5"
}
$Cred = Get-Credential
Connect-MsolService -Credential $Cred
$Users = Get-MsolUser -All | Where-Object { $_.isLicensed -eq "TRUE" } | Sort-Object DisplayName
Foreach ($User in $Users)
{
Write-Host "Working on $($User.DisplayName)..." -ForegroundColor Yellow
#Gets users license and splits it at the semicolon
Write-Host "Getting all licenses for $($User.DisplayName)..." -ForegroundColor White
$Licenses = ((Get-MsolUser -UserPrincipalName $User.UserPrincipalName).Licenses).AccountSkuID
If (($Licenses).Count -gt 1)
{
Foreach ($License in $Licenses)
{
Write-Host "Finding $License in the Hash Table..." -ForegroundColor White
$LicenseItem = $License -split ":" | Select-Object -Last 1
$TextLic = $Sku.Item("$LicenseItem")
If (!($TextLic))
{
Write-Host "Error: The Hash Table has no match for $LicenseItem for $($User.DisplayName)!" -ForegroundColor Red
$LicenseFallBackName = $License.AccountSkuId
$NewObject02 = $null
$NewObject02 = @()
$NewObject01 = New-Object PSObject
$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$LicenseFallBackName"
$NewObject02 += $NewObject01
$NewObject02 | Export-CSV $CSV -NoTypeInformation -Append
}
Else
{
$NewObject02 = $null
$NewObject02 = @()
$NewObject01 = New-Object PSObject
$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$TextLic"
$NewObject02 += $NewObject01
$NewObject02 | Export-CSV $CSV -NoTypeInformation -Append
}
}
}
Else
{
Write-Host "Finding $Licenses in the Hash Table..." -ForegroundColor White
$LicenseItem = ((Get-MsolUser -UserPrincipalName $User.UserPrincipalName).Licenses).AccountSkuID -split ":" | Select-Object -Last 1
$TextLic = $Sku.Item("$LicenseItem")
If (!($TextLic))
{
Write-Host "Error: The Hash Table has no match for $LicenseItem for $($User.DisplayName)!" -ForegroundColor Red
$LicenseFallBackName = $License.AccountSkuId
$NewObject02 = $null
$NewObject02 = @()
$NewObject01 = New-Object PSObject
$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$LicenseFallBackName"
$NewObject02 += $NewObject01
$NewObject02 | Export-CSV $CSV -NoTypeInformation -Append
}
Else
{
$NewObject02 = $null
$NewObject02 = @()
$NewObject01 = New-Object PSObject
$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$TextLic"
$NewObject02 += $NewObject01
$NewObject02 | Export-CSV $CSV -NoTypeInformation -Append
}
}
}
Multi-Tenant:
(Multi-Tenant with individual reports)
Now let’s say you want to run this against all your tenants using delegated admin. And for each client you want to have it in their own CSV file, so client A results would be its own CSV file than client B. Using the script below we can use delegated admin to run this against all of our tenants and have the results save to each client specific CSV file.
CSV File
In the first line I told it to store the CSV files at a folder at C:\Clients. Note that I do not finish the full path with the filename. This is because the script will take the path we specify and name the CSV file using the client name.
The Shell
The Shell will display the same information but also include the client it’s currently working on every time it goes to a new client.
The Script
$CSV = "C:\Clients\"
$Sku = @{
"O365_BUSINESS_ESSENTIALS" = "Office 365 Business Essentials"
"O365_BUSINESS_PREMIUM" = "Office 365 Business Premium"
"DESKLESSPACK" = "Office 365 (Plan K1)"
"DESKLESSWOFFPACK" = "Office 365 (Plan K2)"
"LITEPACK" = "Office 365 (Plan P1)"
"EXCHANGESTANDARD" = "Office 365 Exchange Online Only"
"STANDARDPACK" = "Enterprise Plan E1"
"STANDARDWOFFPACK" = "Office 365 (Plan E2)"
"ENTERPRISEPACK" = "Enterprise Plan E3"
"ENTERPRISEPACKLRG" = "Enterprise Plan E3"
"ENTERPRISEWITHSCAL" = "Enterprise Plan E4"
"STANDARDPACK_STUDENT" = "Office 365 (Plan A1) for Students"
"STANDARDWOFFPACKPACK_STUDENT" = "Office 365 (Plan A2) for Students"
"ENTERPRISEPACK_STUDENT" = "Office 365 (Plan A3) for Students"
"ENTERPRISEWITHSCAL_STUDENT" = "Office 365 (Plan A4) for Students"
"STANDARDPACK_FACULTY" = "Office 365 (Plan A1) for Faculty"
"STANDARDWOFFPACKPACK_FACULTY" = "Office 365 (Plan A2) for Faculty"
"ENTERPRISEPACK_FACULTY" = "Office 365 (Plan A3) for Faculty"
"ENTERPRISEWITHSCAL_FACULTY" = "Office 365 (Plan A4) for Faculty"
"ENTERPRISEPACK_B_PILOT" = "Office 365 (Enterprise Preview)"
"STANDARD_B_PILOT" = "Office 365 (Small Business Preview)"
"VISIOCLIENT" = "Visio Pro Online"
"POWER_BI_ADDON" = "Office 365 Power BI Addon"
"POWER_BI_INDIVIDUAL_USE" = "Power BI Individual User"
"POWER_BI_STANDALONE" = "Power BI Stand Alone"
"POWER_BI_STANDARD" = "Power-BI Standard"
"PROJECTESSENTIALS" = "Project Lite"
"PROJECTCLIENT" = "Project Professional"
"PROJECTONLINE_PLAN_1" = "Project Online"
"PROJECTONLINE_PLAN_2" = "Project Online and PRO"
"ProjectPremium" = "Project Online Premium"
"ECAL_SERVICES" = "ECAL"
"EMS" = "Enterprise Mobility Suite"
"RIGHTSMANAGEMENT_ADHOC" = "Windows Azure Rights Management"
"MCOMEETADV" = "PSTN conferencing"
"SHAREPOINTSTORAGE" = "SharePoint storage"
"PLANNERSTANDALONE" = "Planner Standalone"
"CRMIUR" = "CMRIUR"
"BI_AZURE_P1" = "Power BI Reporting and Analytics"
"INTUNE_A" = "Windows Intune Plan A"
"PROJECTWORKMANAGEMENT" = "Office 365 Planner Preview"
"ATP_ENTERPRISE" = "Exchange Online Advanced Threat Protection"
"EQUIVIO_ANALYTICS" = "Office 365 Advanced eDiscovery"
"AAD_BASIC" = "Azure Active Directory Basic"
"RMS_S_ENTERPRISE" = "Azure Active Directory Rights Management"
"AAD_PREMIUM" = "Azure Active Directory Premium"
"MFA_PREMIUM" = "Azure Multi-Factor Authentication"
"STANDARDPACK_GOV" = "Microsoft Office 365 (Plan G1) for Government"
"STANDARDWOFFPACK_GOV" = "Microsoft Office 365 (Plan G2) for Government"
"ENTERPRISEPACK_GOV" = "Microsoft Office 365 (Plan G3) for Government"
"ENTERPRISEWITHSCAL_GOV" = "Microsoft Office 365 (Plan G4) for Government"
"DESKLESSPACK_GOV" = "Microsoft Office 365 (Plan K1) for Government"
"ESKLESSWOFFPACK_GOV" = "Microsoft Office 365 (Plan K2) for Government"
"EXCHANGESTANDARD_GOV" = "Microsoft Office 365 Exchange Online (Plan 1) only for Government"
"EXCHANGEENTERPRISE_GOV" = "Microsoft Office 365 Exchange Online (Plan 2) only for Government"
"SHAREPOINTDESKLESS_GOV" = "SharePoint Online Kiosk"
"EXCHANGE_S_DESKLESS_GOV" = "Exchange Kiosk"
"RMS_S_ENTERPRISE_GOV" = "Windows Azure Active Directory Rights Management"
"OFFICESUBSCRIPTION_GOV" = "Office ProPlus"
"MCOSTANDARD_GOV" = "Lync Plan 2G"
"SHAREPOINTWAC_GOV" = "Office Online for Government"
"SHAREPOINTENTERPRISE_GOV" = "SharePoint Plan 2G"
"EXCHANGE_S_ENTERPRISE_GOV" = "Exchange Plan 2G"
"EXCHANGE_S_ARCHIVE_ADDON_GOV" = "Exchange Online Archiving"
"EXCHANGE_S_DESKLESS" = "Exchange Online Kiosk"
"SHAREPOINTDESKLESS" = "SharePoint Online Kiosk"
"SHAREPOINTWAC" = "Office Online"
"YAMMER_ENTERPRISE" = "Yammer for the Starship Enterprise"
"EXCHANGE_L_STANDARD" = "Exchange Online (Plan 1)"
"MCOLITE" = "Lync Online (Plan 1)"
"SHAREPOINTLITE" = "SharePoint Online (Plan 1)"
"OFFICE_PRO_PLUS_SUBSCRIPTION_SMBIZ" = "Office ProPlus"
"EXCHANGE_S_STANDARD_MIDMARKET" = "Exchange Online (Plan 1)"
"MCOSTANDARD_MIDMARKET" = "Lync Online (Plan 1)"
"SHAREPOINTENTERPRISE_MIDMARKET" = "SharePoint Online (Plan 1)"
"OFFICESUBSCRIPTION" = "Office ProPlus"
"YAMMER_MIDSIZE" = "Yammer"
"DYN365_ENTERPRISE_PLAN1" = "Dynamics 365 Customer Engagement Plan Enterprise Edition"
"ENTERPRISEPREMIUM_NOPSTNCONF" = "Enterprise E5 (without Audio Conferencing)"
"ENTERPRISEPREMIUM" = "Enterprise E5 (with Audio Conferencing)"
"MCOSTANDARD" = "Skype for Business Online Standalone Plan 2"
"PROJECT_MADEIRA_PREVIEW_IW_SKU" = "Dynamics 365 for Financials for IWs"
"STANDARDWOFFPACK_IW_STUDENT" = "Office 365 Education for Students"
"STANDARDWOFFPACK_IW_FACULTY" = "Office 365 Education for Faculty"
"EOP_ENTERPRISE_FACULTY" = "Exchange Online Protection for Faculty"
"EXCHANGESTANDARD_STUDENT" = "Exchange Online (Plan 1) for Students"
"OFFICESUBSCRIPTION_STUDENT" = "Office ProPlus Student Benefit"
"STANDARDWOFFPACK_FACULTY" = "Office 365 Education E1 for Faculty"
"STANDARDWOFFPACK_STUDENT" = "Microsoft Office 365 (Plan A2) for Students"
"DYN365_FINANCIALS_BUSINESS_SKU" = "Dynamics 365 for Financials Business Edition"
"DYN365_FINANCIALS_TEAM_MEMBERS_SKU" = "Dynamics 365 for Team Members Business Edition"
"FLOW_FREE" = "Microsoft Flow Free"
"POWER_BI_PRO" = "Power BI Pro"
"O365_BUSINESS" = "Office 365 Business"
"DYN365_ENTERPRISE_SALES" = "Dynamics Office 365 Enterprise Sales"
"RIGHTSMANAGEMENT" = "Rights Management"
"PROJECTPROFESSIONAL" = "Project Professional"
"VISIOONLINE_PLAN1" = "Visio Online Plan 1"
"EXCHANGEENTERPRISE" = "Exchange Online Plan 2"
"DYN365_ENTERPRISE_P1_IW" = "Dynamics 365 P1 Trial for Information Workers"
"DYN365_ENTERPRISE_TEAM_MEMBERS" = "Dynamics 365 For Team Members Enterprise Edition"
"CRMSTANDARD" = "Microsoft Dynamics CRM Online Professional"
"EXCHANGEARCHIVE_ADDON" = "Exchange Online Archiving For Exchange Online"
"EXCHANGEDESKLESS" = "Exchange Online Kiosk"
"SPZA_IW" = "App Connect"
}
$Cred = Get-Credential
Connect-MsolService -Credential $Cred
$clients = Get-MsolPartnerContract -All
ForEach ($client in $clients)
{
$ClientName = $client.Name
Write-Host "Working on $ClientName" -ForegroundColor Yellow
$Users = Get-MsolUser -TenantId $client.TenantId | Where-Object { $_.isLicensed -eq "TRUE" } | Sort-Object DisplayName
Foreach ($User in $Users)
{
Write-Host "Working on $($User.DisplayName)..." -ForegroundColor Yellow
#Gets users license and splits it at the semicolon
Write-Host "Getting all licenses for $($User.DisplayName)..." -ForegroundColor White
$Licenses = ((Get-MsolUser -TenantId $client.TenantId -UserPrincipalName $User.UserPrincipalName).Licenses).AccountSkuID
If (($Licenses).Count -gt 1)
{
Foreach ($License in $Licenses)
{
Write-Host "Finding $License in the Hash Table..." -ForegroundColor White
$LicenseItem = $License -split ":" | Select-Object -Last 1
$TextLic = $Sku.Item("$LicenseItem")
If (!($TextLic))
{
Write-Host "Error: The Hash Table has no match for $LicenseItem for $($User.DisplayName)!" -ForegroundColor Red
$LicenseFallBackName = $License.AccountSkuId
$CSVName = "$CSV" + "$ClientName.csv"
$NewObject02 = $null
$NewObject02 = @()
$NewObject01 = New-Object PSObject
$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$LicenseFallBackName"
$NewObject02 += $NewObject01
$NewObject02 | Export-CSV $CSVName -NoTypeInformation -Append
}
Else
{
$CSVName = "$CSV" + "$ClientName.csv"
$NewObject02 = $null
$NewObject02 = @()
$NewObject01 = New-Object PSObject
$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$TextLic"
$NewObject02 += $NewObject01
$NewObject02 | Export-CSV $CSVName -NoTypeInformation -Append
}
}
}
Else
{
Write-Host "Finding $Licenses in the Hash Table..." -ForegroundColor White
$LicenseItem = ((Get-MsolUser -TenantId $client.TenantId -UserPrincipalName $User.UserPrincipalName).Licenses).AccountSkuID -split ":" | Select-Object -Last 1
$TextLic = $Sku.Item("$LicenseItem")
If (!($TextLic))
{
Write-Host "Error: The Hash Table has no match for $LicenseItem for $($User.DisplayName)!" -ForegroundColor Red
$LicenseFallBackName = $License.AccountSkuId
$LicenseFallBackName = $License.AccountSkuId
$CSVName = "$CSV" + "$ClientName.csv"
$NewObject02 = $null
$NewObject02 = @()
$NewObject01 = New-Object PSObject
$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$LicenseFallBackName"
$NewObject02 += $NewObject01
$NewObject02 | Export-CSV $CSVName -NoTypeInformation -Append
}
Else
{
$CSVName = "$CSV" + "$ClientName.csv"
$NewObject02 = $null
$NewObject02 = @()
$NewObject01 = New-Object PSObject
$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$TextLic"
$NewObject02 += $NewObject01
$NewObject02 | Export-CSV $CSVName -NoTypeInformation -Append
}
}
}
}
Multi-Tenant:
(Multi-Tenant with a single report)
If we want to save the multi-tenant results to a single CSV file instead of individual client files we can accomplish that as well. All we have to do is delete the CSVName variable and change the Export-CSV back to the CSV variable. This will allow it to save to a single file instead of multiple files.
The Script
$CSV = "C:\Clients\Office_365_User_Licensing.csv"
$Sku = @{
"O365_BUSINESS_ESSENTIALS" = "Office 365 Business Essentials"
"O365_BUSINESS_PREMIUM" = "Office 365 Business Premium"
"DESKLESSPACK" = "Office 365 (Plan K1)"
"DESKLESSWOFFPACK" = "Office 365 (Plan K2)"
"LITEPACK" = "Office 365 (Plan P1)"
"EXCHANGESTANDARD" = "Office 365 Exchange Online Only"
"STANDARDPACK" = "Enterprise Plan E1"
"STANDARDWOFFPACK" = "Office 365 (Plan E2)"
"ENTERPRISEPACK" = "Enterprise Plan E3"
"ENTERPRISEPACKLRG" = "Enterprise Plan E3"
"ENTERPRISEWITHSCAL" = "Enterprise Plan E4"
"STANDARDPACK_STUDENT" = "Office 365 (Plan A1) for Students"
"STANDARDWOFFPACKPACK_STUDENT" = "Office 365 (Plan A2) for Students"
"ENTERPRISEPACK_STUDENT" = "Office 365 (Plan A3) for Students"
"ENTERPRISEWITHSCAL_STUDENT" = "Office 365 (Plan A4) for Students"
"STANDARDPACK_FACULTY" = "Office 365 (Plan A1) for Faculty"
"STANDARDWOFFPACKPACK_FACULTY" = "Office 365 (Plan A2) for Faculty"
"ENTERPRISEPACK_FACULTY" = "Office 365 (Plan A3) for Faculty"
"ENTERPRISEWITHSCAL_FACULTY" = "Office 365 (Plan A4) for Faculty"
"ENTERPRISEPACK_B_PILOT" = "Office 365 (Enterprise Preview)"
"STANDARD_B_PILOT" = "Office 365 (Small Business Preview)"
"VISIOCLIENT" = "Visio Pro Online"
"POWER_BI_ADDON" = "Office 365 Power BI Addon"
"POWER_BI_INDIVIDUAL_USE" = "Power BI Individual User"
"POWER_BI_STANDALONE" = "Power BI Stand Alone"
"POWER_BI_STANDARD" = "Power-BI Standard"
"PROJECTESSENTIALS" = "Project Lite"
"PROJECTCLIENT" = "Project Professional"
"PROJECTONLINE_PLAN_1" = "Project Online"
"PROJECTONLINE_PLAN_2" = "Project Online and PRO"
"ProjectPremium" = "Project Online Premium"
"ECAL_SERVICES" = "ECAL"
"EMS" = "Enterprise Mobility Suite"
"RIGHTSMANAGEMENT_ADHOC" = "Windows Azure Rights Management"
"MCOMEETADV" = "PSTN conferencing"
"SHAREPOINTSTORAGE" = "SharePoint storage"
"PLANNERSTANDALONE" = "Planner Standalone"
"CRMIUR" = "CMRIUR"
"BI_AZURE_P1" = "Power BI Reporting and Analytics"
"INTUNE_A" = "Windows Intune Plan A"
"PROJECTWORKMANAGEMENT" = "Office 365 Planner Preview"
"ATP_ENTERPRISE" = "Exchange Online Advanced Threat Protection"
"EQUIVIO_ANALYTICS" = "Office 365 Advanced eDiscovery"
"AAD_BASIC" = "Azure Active Directory Basic"
"RMS_S_ENTERPRISE" = "Azure Active Directory Rights Management"
"AAD_PREMIUM" = "Azure Active Directory Premium"
"MFA_PREMIUM" = "Azure Multi-Factor Authentication"
"STANDARDPACK_GOV" = "Microsoft Office 365 (Plan G1) for Government"
"STANDARDWOFFPACK_GOV" = "Microsoft Office 365 (Plan G2) for Government"
"ENTERPRISEPACK_GOV" = "Microsoft Office 365 (Plan G3) for Government"
"ENTERPRISEWITHSCAL_GOV" = "Microsoft Office 365 (Plan G4) for Government"
"DESKLESSPACK_GOV" = "Microsoft Office 365 (Plan K1) for Government"
"ESKLESSWOFFPACK_GOV" = "Microsoft Office 365 (Plan K2) for Government"
"EXCHANGESTANDARD_GOV" = "Microsoft Office 365 Exchange Online (Plan 1) only for Government"
"EXCHANGEENTERPRISE_GOV" = "Microsoft Office 365 Exchange Online (Plan 2) only for Government"
"SHAREPOINTDESKLESS_GOV" = "SharePoint Online Kiosk"
"EXCHANGE_S_DESKLESS_GOV" = "Exchange Kiosk"
"RMS_S_ENTERPRISE_GOV" = "Windows Azure Active Directory Rights Management"
"OFFICESUBSCRIPTION_GOV" = "Office ProPlus"
"MCOSTANDARD_GOV" = "Lync Plan 2G"
"SHAREPOINTWAC_GOV" = "Office Online for Government"
"SHAREPOINTENTERPRISE_GOV" = "SharePoint Plan 2G"
"EXCHANGE_S_ENTERPRISE_GOV" = "Exchange Plan 2G"
"EXCHANGE_S_ARCHIVE_ADDON_GOV" = "Exchange Online Archiving"
"EXCHANGE_S_DESKLESS" = "Exchange Online Kiosk"
"SHAREPOINTDESKLESS" = "SharePoint Online Kiosk"
"SHAREPOINTWAC" = "Office Online"
"YAMMER_ENTERPRISE" = "Yammer for the Starship Enterprise"
"EXCHANGE_L_STANDARD" = "Exchange Online (Plan 1)"
"MCOLITE" = "Lync Online (Plan 1)"
"SHAREPOINTLITE" = "SharePoint Online (Plan 1)"
"OFFICE_PRO_PLUS_SUBSCRIPTION_SMBIZ" = "Office ProPlus"
"EXCHANGE_S_STANDARD_MIDMARKET" = "Exchange Online (Plan 1)"
"MCOSTANDARD_MIDMARKET" = "Lync Online (Plan 1)"
"SHAREPOINTENTERPRISE_MIDMARKET" = "SharePoint Online (Plan 1)"
"OFFICESUBSCRIPTION" = "Office ProPlus"
"YAMMER_MIDSIZE" = "Yammer"
"DYN365_ENTERPRISE_PLAN1" = "Dynamics 365 Customer Engagement Plan Enterprise Edition"
"ENTERPRISEPREMIUM_NOPSTNCONF" = "Enterprise E5 (without Audio Conferencing)"
"ENTERPRISEPREMIUM" = "Enterprise E5 (with Audio Conferencing)"
"MCOSTANDARD" = "Skype for Business Online Standalone Plan 2"
"PROJECT_MADEIRA_PREVIEW_IW_SKU" = "Dynamics 365 for Financials for IWs"
"STANDARDWOFFPACK_IW_STUDENT" = "Office 365 Education for Students"
"STANDARDWOFFPACK_IW_FACULTY" = "Office 365 Education for Faculty"
"EOP_ENTERPRISE_FACULTY" = "Exchange Online Protection for Faculty"
"EXCHANGESTANDARD_STUDENT" = "Exchange Online (Plan 1) for Students"
"OFFICESUBSCRIPTION_STUDENT" = "Office ProPlus Student Benefit"
"STANDARDWOFFPACK_FACULTY" = "Office 365 Education E1 for Faculty"
"STANDARDWOFFPACK_STUDENT" = "Microsoft Office 365 (Plan A2) for Students"
"DYN365_FINANCIALS_BUSINESS_SKU" = "Dynamics 365 for Financials Business Edition"
"DYN365_FINANCIALS_TEAM_MEMBERS_SKU" = "Dynamics 365 for Team Members Business Edition"
"FLOW_FREE" = "Microsoft Flow Free"
"POWER_BI_PRO" = "Power BI Pro"
"O365_BUSINESS" = "Office 365 Business"
"DYN365_ENTERPRISE_SALES" = "Dynamics Office 365 Enterprise Sales"
"RIGHTSMANAGEMENT" = "Rights Management"
"PROJECTPROFESSIONAL" = "Project Professional"
"VISIOONLINE_PLAN1" = "Visio Online Plan 1"
"EXCHANGEENTERPRISE" = "Exchange Online Plan 2"
"DYN365_ENTERPRISE_P1_IW" = "Dynamics 365 P1 Trial for Information Workers"
"DYN365_ENTERPRISE_TEAM_MEMBERS" = "Dynamics 365 For Team Members Enterprise Edition"
"CRMSTANDARD" = "Microsoft Dynamics CRM Online Professional"
"EXCHANGEARCHIVE_ADDON" = "Exchange Online Archiving For Exchange Online"
"EXCHANGEDESKLESS" = "Exchange Online Kiosk"
"SPZA_IW" = "App Connect"
}
$Cred = Get-Credential
Connect-MsolService -Credential $Cred
$clients = Get-MsolPartnerContract -All
ForEach ($client in $clients)
{
$ClientName = $client.Name
Write-Host "Working on $ClientName" -ForegroundColor Yellow
$Users = Get-MsolUser -TenantId $client.TenantId | Where-Object { $_.isLicensed -eq "TRUE" } | Sort-Object DisplayName
Foreach ($User in $Users)
{
Write-Host "Working on $($User.DisplayName)..." -ForegroundColor Yellow
#Gets users license and splits it at the semicolon
Write-Host "Getting all licenses for $($User.DisplayName)..." -ForegroundColor White
$Licenses = ((Get-MsolUser -TenantId $client.TenantId -UserPrincipalName $User.UserPrincipalName).Licenses).AccountSkuID
If (($Licenses).Count -gt 1)
{
Foreach ($License in $Licenses)
{
Write-Host "Finding $License in the Hash Table..." -ForegroundColor White
$LicenseItem = $License -split ":" | Select-Object -Last 1
$TextLic = $Sku.Item("$LicenseItem")
If (!($TextLic))
{
Write-Host "Error: The Hash Table has no match for $LicenseItem for $($User.DisplayName)!" -ForegroundColor Red
$LicenseFallBackName = $License.AccountSkuId
$NewObject02 = $null
$NewObject02 = @()
$NewObject01 = New-Object PSObject
$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$LicenseFallBackName"
$NewObject02 += $NewObject01
$NewObject02 | Export-CSV $CSV -NoTypeInformation -Append
}
Else
{
$NewObject02 = $null
$NewObject02 = @()
$NewObject01 = New-Object PSObject
$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$TextLic"
$NewObject02 += $NewObject01
$NewObject02 | Export-CSV $CSV -NoTypeInformation -Append
}
}
}
Else
{
Write-Host "Finding $Licenses in the Hash Table..." -ForegroundColor White
$LicenseItem = ((Get-MsolUser -TenantId $client.TenantId -UserPrincipalName $User.UserPrincipalName).Licenses).AccountSkuID -split ":" | Select-Object -Last 1
$TextLic = $Sku.Item("$LicenseItem")
If (!($TextLic))
{
Write-Host "Error: The Hash Table has no match for $LicenseItem for $($User.DisplayName)!" -ForegroundColor Red
$LicenseFallBackName = $License.AccountSkuId
$LicenseFallBackName = $License.AccountSkuId
$NewObject02 = $null
$NewObject02 = @()
$NewObject01 = New-Object PSObject
$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$LicenseFallBackName"
$NewObject02 += $NewObject01
$NewObject02 | Export-CSV $CSV -NoTypeInformation -Append
}
Else
{
$NewObject02 = $null
$NewObject02 = @()
$NewObject01 = New-Object PSObject
$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$TextLic"
$NewObject02 += $NewObject01
$NewObject02 | Export-CSV $CSV -NoTypeInformation -Append
}
}
}
}
My name is Bradley Wyatt; I am a 5x Microsoft Most Valuable Professional (MVP) in Microsoft Azure and Microsoft 365. I have given talks at many different conferences, user groups, and companies throughout the United States, ranging from PowerShell to DevOps Security best practices, and I am the 2022 North American Outstanding Contribution to the Microsoft Community winner.
42 thoughts on “Get Friendly License Name for all Users in Office 365 Using PowerShell”
How difficult would it be to modify this to run the report for each customer in our partner portal, with a different csv for each customer
Hi Andrew, it wouldn’t be hard at all! I have updated the post to give you what you’d like to accomplish.
This site is absolutely fabulous!
Keep up the great work guyz.
whoah this webloց іѕ magnificent i like studying your articⅼes.
Stay up thе good woгk! You know, lots of peoplе are ⅼօoking around
for this information, you cⲟuld help them greatⅼy.
Tried your script since it looked like what I was looking for at it takes me forever to create them myself. It launches fine and completes the login but after a short while it throws the following back at me:
WARNING: More results are available. Please specify one of the All or MaxResults parameters.
Fixed! I needed to add the All parameter at Get-MSOLUser
Nice response time, thank you for that. I tried the new script but sadly it still throws this back at me:
cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
Credential
WARNING: More results are available. Please specify one of the All or MaxResults parameters.
Think i read somewhere there is a fault with the -all parametre but I am unsure.
Looks like I missed another All param. Try the above script again
$Users = Get-MsolUser -All | Where-Object { $_.isLicensed -eq “TRUE” } | Sort-Object DisplayName
Where did you get the friendly names in the first place?
Hi – I’m developing the Periodic Table of Office 365 together with @thatmattwade – http://www.jumpto365.com. We are working on a feature where each user can logon and get a report of which licenses they have. I use the Office Graph to query that, and might use your mapping as a good starting point.
Wonder what your source is for the mapping?
Anything that we can work together on keeping the list fresh??
I have just been using a PowerShell hashtable that I keep updated. We could always keep a shared list in GitHub and reference it? Thoughts?
i need visio users and Ms Project users list only with user name to using power shell ,please help me on that
Got this error message
Get-MsolUser : Parameter set cannot be resolved using the specified named parameters.
At line:1 char:3
+ ((Get-MsolUser -All -UserPrincipalName $User.UserPrincipalName).Licen …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Get-MsolUser], ParameterBindingException
+ FullyQualifiedErrorId : AmbiguousParameterSet,Microsoft.Online.Administration.Automation.GetUser
Anyone known how to resolve it?
Fixed!
getting this error:
Get-MsolUser : Parameter set cannot be resolved using the specified named parameters.
At C:\Users\Josh\Documents\WindowsPowerShell\licensereport.ps1:128 char:16
+ … icenses = ((Get-MsolUser -All -UserPrincipalName $User.UserPrincipalN …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Get-MsolUser], ParameterBindingException
+ FullyQualifiedErrorId : AmbiguousParameterSet,Microsoft.Online.Administration.Automation.GetUser
Thanks josh, I believe I fixed it, try it again
Dear Brad Wyatt,
Can you make a script to know, whom was it assigned to and who assigned it?
Thanks in advance.
hmm I will have to see if the API exposes that kind of information. e-mail me [email protected]
Hi Brad,
As Microsoft is always changing things in the background i guess that the friendly names and accountsku names are also added and subject to change.
Where did you find the overview of all the friendly license names and powershell sku names?
Online and I work with a lot of clients with a lot of different SKUs. So I try and keep it updated on GitHub
Hello Brad,
I have some more AccountSkuId’s to add to the hash table:
“CRMSTORAGE” = “Microsoft Dynamics CRM Online Additional Storage”
“SMB_APPS” = “Microsoft Business Apps”
“MICROSOFT_BUSINESS_CENTER” = “Microsoft Business Center”
“FLOW_FREE” = “Microsoft Flow Free”
“DYN365_TEAM_MEMBERS” = “Dynamics 365 Team Members”
“STREAM” = “Microsoft Stream Trial”
thanks! added!
Hi Brad,
How do we add company and department in the report? And Did tried your above script but it was keeps looping non-stop.
$NewObject01 | Add-Member -MemberType NoteProperty -Name “Company” -Value $User.Company
$NewObject01 | Add-Member -MemberType NoteProperty -Name “Department” -Value $User.Department
make sure company is a valid property.
Great little Script, I am bringing back department, mobile phone etc. I am looking to combine with output from Get-mailbox forwardingsmtpaddress, I can return these separately but need to tie it with UserprincipalName from get-msoluser, Any tips would be great
You will have to do some PowerShell matching, can you shoot me an email we can work on it
This looks like exactly what I am looking for.
Although, I think that the array of skuids in it don’t account for O365 E5 licenses.
I get errors for all my users that say:
Finding bmgf:ENTERPRISEPREMIUM in the Hash Table…
Finding bmgf:EMSPREMIUM in the Hash Table…
Error: The Hash Table has no match for EMSPREMIUM for UserX!
thanks I added the SKU and friendly name. updated on the website
Is there a way that the license options can also be shown to see what is enabled and disabled for each user? For example, I have users who have been assigned Project Online Premium licenses, but disabled the license option for Project Online due to an application constraint.
if you make a github request i can add it to my todo list
lol no one commented on ….
“YAMMER_ENTERPRISE” = “Yammer for the Starship Enterprise”
yet?
Thank you! This is exactly what I’m looking for
Thanks for this it’s brilliant.
Is there a way to get the licenses per user on the same line?
Such as:
User – UPN – Licenses
TESTUS – [email protected] – Lic1,Lic2,Lic3?
Hi,
Can we create an csv report per license?
Regards
For others simply looking for the list of SKUs and descriptions…
https://docs.microsoft.com/en-us/azure/active-directory/users-groups-roles/licensing-service-plan-reference
I know this might be a bit late to the original post, but I want to either “count” or add another column with incremental numbers to show totals, I’ve tried creating a new var $count in a new column and adding its not incrementing the rows, any help will be welcome. Great blog by the way!
Would it be possible to update this to put all the licenses on a single line with the user, instead of putting each license on a new line?
I join the request if it is possible to update this script to put all licenses on one line with the user
Not sure you are still reading the comments here, Great script and very useful as you scale up in size or clean up after a migration. Question is, how would one modify the script to abandon the manually created hash table and use the downloadable csv linked in the the learn doc here https://learn.microsoft.com/en-us/entra/identity/users/licensing-service-plan-reference
near the top of the list there is a link to download the csv, assuming you have the csv could you not just import that and generate a table from it (have not worked with hash tables so I am not sure)?
I recently cast-off this purlieus to allot repiping near me , and I couldn’t be happier with the results. The search was straightforward, and I appreciated the comprehensive profiles and buyer reviews on the side of each contractor. It мейд comparing options and reading nearly other clients’ experiences easy. The contractors I contacted were willing, professional, and offered competitive quotes. This milieu is a fantastic resource for anyone needing reliable territory repair services. Greatly recommended for its explicit interface and eminence listings!