METHOD 1
This method is a very small script that will output all Office 365 users and the licenses that are assigned to them to a .csv file
- Connect to Office 365 via PowerShell. Use the guide below to connect
- http://itwalkthroughs.com/knowledge-base/connect-to-office-365-via-powershell-with-mfa-enabled-account/
- Once connected via PowerShell type the following command
Get-MSOLUser -All | where {$_.isLicensed -eq $true} |select DisplayName,userprincipalname,islicensed,{$_.Licenses.AccountSkuId}| Export-Csv "filename.csv" -NoTypeInformation
- Press Enter
- PowerShell will now export a list of Office 365 users and all licenses they have assigned to them
- “filename.csv” is a variable for where you want to save the file. In the example below I am saving the file to the C:\Temp folder
- Once the export is complete you will find the CSV file in your chosen location
- Open the .CSV file and you will see the list of Office 365 users and their assigned licenses.
- The following export example has two Office 365 Users and both are assigned Exchange Online licenses
METHOD 2
The example above is a very basic script which will just export the users and license information using Office Account SKU ID’s which is not very user friendly or easy to read.
When searching for a way to export license info with easy to understand license names I came across Brad Wyatt’s blog where he has created a PowerShell script with a hash table that converts the SKU ID’s to a more friendly license name. There is a link to his script and blog below
https://www.thelazyadministrator.com/2018/03/19/get-friendly-license-name-for-all-users-in-office-365-using-powershell/
$CSV = "C:\Export\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"
"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
}
}
}
This script will output the .csv file to the following location –
“C:\Export\Office_365_User_Licensing.csv”. You will need to make sure the C:\Export folder exists before running the script – “
As you can see from the Output below it is a lot easier to read the licenses assigned to each user
