Export Microsoft 365 Users and their Licenses to CSV with PowerShell

Microsoft 365 admin center supports the Active Users report which lists the active Office 365 users and the assigned licenses. In this post, we will explore how to export Azure AD users and assigned licenses using PowerShell script.

Summary

• Export All Microsoft 365 Users and their License details
• Export Licensed Microsoft 365 Users
• List Unlicensed Microsoft 365 Users
• Find Microsoft 365 Users with a specific license
• Find users with a specific service plan license

We can use the Get-AzureADUser cmdlet to retrieve the Azure AD user details. Before you start, install the latest Azure AD PowerShell module, and run the following command to connect the module.

Connect-AzureAD

Run the following command to view the licensed users in your organization. Lists the user accounts that have been assigned to any of your license plans.

Get-AzureADUser -All $true | Where-Object {$_.AssignedLicenses.Count -ne 0 }

The following command returns all the unlicensed users in your Azure AD tenant.

Get-AzureADUser -All $true | Where-Object {$_.AssignedLicenses.Count -eq 0 }

Export All Microsoft 365 Users and their License details to CSV

The Get-AzureADUser cmdlet supports the property AssignedLicenses. This property returns the SkuId (GUID value) of the assigned licenses. We can use the Get-AzureADSubscribedSku cmdlet to fetch all the available license data for the tenant and resolve the SkuName of the assigned licenses.

The following commands retrieve the Azure AD users with required properties (You can add more properties if needed) and resolve the applied license details for all users. Finally, export the report to a CSV file.

$Result = @() #Result array

#Get all subscribed license Skus to Microsoft services
$AllSkus= Get-AzureADSubscribedSku

#Get all Azure AD Users with the required properties
$AllUsers = Get-AzureADUser -All $true | Select DisplayName, UserPrincipalName, AssignedLicenses, AssignedPlans, ObjectId

#Iterate users one by one and resolve the license details
ForEach ($User in $AllUsers)
{

$AssignedLicenses = @();
$LicensedServices = @();

if($User.AssignedLicenses.Count -ne 0)
{
#Resolve license SKU details
ForEach($License in $User.AssignedLicenses)
{
$SkuInfo = $AllSkus | Where { $_.SkuId -eq $License.SkuId}
$AssignedLicenses += $SkuInfo.SkuPartNumber;
}

#Resolve assigned service plans
ForEach($ServicePlan in $User.AssignedPlans)
{
$LicensedServices += $ServicePlan.Service;
}
}

#Add user detail to $Result array one by one
$Result += New-Object PSObject -property $([ordered]@{
UserName = $User.DisplayName
UserPrincipalName = $User.UserPrincipalName
UserId= $User.ObjectId
IsLicensed  = if ($User.AssignedLicenses.Count -ne 0) { $true } else { $false }
Licenses = $AssignedLicenses -join ","
LicensedServices = ($LicensedServices | Sort-Object | Get-Unique)  -join ","
})

}

#Export All Microsoft 365 Users report to CSV
$Result | Export-CSV "C:\Temp\Microsoft365Users.CSV" -NoTypeInformation -Encoding UTF8

The above commands store the user details in the array object $Result. We can generate more reports by applying a filter in the $Result array using the available fields.

Export Licensed Microsoft 365 Users

The following command exports only the licensed users.

$Result | Where-Object { $_.IsLicensed -eq $true } | Export-CSV "C:\Temp\LicensedM365Users.CSV" -NoTypeInformation -Encoding UTF8

List Unlicensed Microsoft 365 Users

Use the below command to list the unlicensed users.

$Result | Where-Object { $_.IsLicensed -eq $false } | Select UserName, UserPrincipalName

Find Microsoft 365 Users with a specific license type

To filter users with the applied license, we should know the SkuPartNumber of the license. Microsoft listed the Product names (License friendly name) and SkuPartNumber in this post: Product names and service plan identifiers for licensing.  You can refer to the post and get your String ID (SkuPartNumber) and use it in the below command to find users with a specific license type.

The below command lists the users with the Office 365 E5 license.

#Provide the SkuPartNumber of the required license
$LicenseName = "ENTERPRISEPREMIUM" #This is the SkuPartNumber for the Office 365 E5 license

$Result | Where-Object { $_.Licenses -like "*$LicenseName*" } | Select UserName,UserPrincipalName,Licenses

Find users with a specific service plan license

We can also filter the users with their applied service plans (licensed apps). You can refer to this post to view the available service plans and their friendly names. The below command lists the users with the Microsoft Stream license.

#Provide the name of the required service plan (app)
$ServicePlanName = "MicrosoftStream" #This is the service plan name for the Microsoft Stream app

$Result | Where-Object { $_.LicensedServices -like "*$ServicePlanName*" } | Select UserName,LicensedServices