In this blog post, we’ll learn to export Microsoft 365 users and their assigned licenses along with the service plan info to a CSV file using PowerShell scripts. While managing the licenses from Microsoft 365 admin center, you can assign licenses to users and customize which services or apps needs to be enabled or disabled.
As an example, Let’s say you have assigned Microsoft 365 E5 license to all users but disabled Microsoft Teams app license or Exchange online service etc. You can check license information and service plan information going to Microsoft 365 admin center > Users > Active users > Click any User > License and apps.
Contents
Export Microsoft 365 User Licenses to CSV using Graph [Recommended]
There are many ways to export user license information from Microsoft 365. We will first work with Microsoft graph and then after that look at the other options.
- Launch Powershell console as an administrator and execute below commands.
# Install Microsoft Graph module if not already installed
Install-Module Microsoft.Graph -Scope CurrentUser
# Import Microsoft Graph module
Import-Module Microsoft.Graph
# Connect to Microsoft Graph with required permissions (Delegated or Application)
Connect-MgGraph -Scopes "User.Read.All", "Directory.Read.All"
If you are connecting to graph first time, you may get below pop-up. Click on Consent on behalf of your organisation checkbox and click on Accept.
- Save below powershell code in a .ps1 file and update the variable
$userPrincipalName
to user’s UPN to get the license information and execute the script. You can also download below script from my github repo: M365_License_Export_One_User_Graph.ps1.
M365_License_Export_One_User_Graph.ps1
# Specify the userPrincipalName of the user you want to check
$userPrincipalName = "[email protected]"
# Get user information using Microsoft Graph
$user = Get-MgUser -UserId $userPrincipalName
# Get assigned licenses for the specific user
$licenses = Get-MgUserLicenseDetail -UserId $user.Id
$result = @()
foreach ($license in $licenses) {
foreach ($plan in $license.ServicePlans) {
$result += [pscustomobject]@{
userPrincipalName = $user.UserPrincipalName
displayName = $user.DisplayName
skuPartNumber = $license.SkuPartNumber
servicePlanName = $plan.ServicePlanName
servicePlanStatus = $plan.ProvisioningStatus
}
}
}
# Export result to CSV
$result | Export-Csv -Path "C:\M365_User_Licenses.csv" -NoTypeInformation
Write-Host "Export completed. File saved as C:\M365_User_Licenses.csv"
- Exported license report will be stored in C:\ drive. Below screenshot shows the contents of M365_User_Licenses.csv file which includes the license details for a given user along with service plan information.
Previous script requires providing userprincipalname into a variable $userPrincipalName
, which will pull the license data for that particular user. You may have the requirement of exporting license data for all Microsoft 365 users at once. You can use below powershell script which will go through all users and export the data into a CSV file. You can also download below script from my github repo: M365_License_Export_All_User_Graph.ps1.
M365_License_Export_All_Users_Graph.ps1
# Get all users in the tenant (supports pagination)
$allUsers = Get-MgUser -All
# Create an array to store the result
$result = @()
foreach ($user in $allUsers) {
# Get assigned licenses for the current user
$licenses = Get-MgUserLicenseDetail -UserId $user.Id
foreach ($license in $licenses) {
foreach ($plan in $license.ServicePlans) {
$result += [pscustomobject]@{
userPrincipalName = $user.UserPrincipalName
displayName = $user.DisplayName
skuPartNumber = $license.SkuPartNumber
servicePlanName = $plan.ServicePlanName
servicePlanStatus = $plan.ProvisioningStatus
}
}
}
}
# Export result to CSV
$result | Export-Csv -Path "C:\M365_AllUsers_Licenses.csv" -NoTypeInformation
Write-Host "Export completed. File saved as C:\M365_AllUsers_Licenses.csv"
Display M365 User License Info on Powershell Console
Instead of exporting the users license details into CSV file, you can also display it on the powershell console for a quick check. This can be done for one user or all users at once. We will modify the script a bit to display the output on the console.
- Save below code in a .ps1 file, provide the user’s UPN in the $userPrincipalName variable and execute the script. Please note you must connect to Microsoft graph using Connect-MgGraph -Scopes “User.Read.All”, “Directory.Read.All” command before executing the script.
License_Info_PS_Console.ps1
# Specify the userPrincipalName of the user you want to check
$userPrincipalName = "[email protected]"
# Get user information using Microsoft Graph
$user = Get-MgUser -UserId $userPrincipalName
# Get assigned licenses for the specific user
$licenses = Get-MgUserLicenseDetail -UserId $user.Id
$result = @()
foreach ($license in $licenses) {
foreach ($plan in $license.ServicePlans) {
$result += [pscustomobject]@{
userPrincipalName = $user.UserPrincipalName
displayName = $user.DisplayName
skuPartNumber = $license.SkuPartNumber
servicePlanName = $plan.ServicePlanName
servicePlanStatus = $plan.ProvisioningStatus
}
}
}
$result | ft -AutoSize -wrap
Display license data for all users on powershell console using below script.
License_Info_PS_Console_All.ps1
# Get all users in the tenant (supports pagination)
$allUsers = Get-MgUser -All
# Create an array to store the result
$result = @()
foreach ($user in $allUsers) {
# Get assigned licenses for the current user
$licenses = Get-MgUserLicenseDetail -UserId $user.Id
foreach ($license in $licenses) {
foreach ($plan in $license.ServicePlans) {
$result += [pscustomobject]@{
userPrincipalName = $user.UserPrincipalName
displayName = $user.DisplayName
skuPartNumber = $license.SkuPartNumber
servicePlanName = $plan.ServicePlanName
servicePlanStatus = $plan.ProvisioningStatus
}
}
}
}
# Export result to CSV
$result | ft -AutoSize -wrap
Get M365 Consumed License Units
Below command will provide information about available license plans (SkuPartNumber), licenses you have purchased for a specific plan (Enabled) and how many licenses you have assigned (ConsumedUnits) to the users.
Get-MgSubscribedSku
Get-MgSubscribedSku | Select -Property Sku*, ConsumedUnits -ExpandProperty PrepaidUnits | Format-Table *
Find Unlicensed Users
Connect-Graph -Scopes User.Read.All, Organization.Read.All
Get-MgUser -Filter 'assignedLicenses/$count eq 0' -ConsistencyLevel eventual -CountVariable unlicensedUserCount -All
Export Microsoft 365 User Licenses to CSV from Admin Portal
- Sign in to Microsoft 365 admin center.
- Navigate to Billing > Licenses.
- Click on the License Plan under Subscriptions tab.
- It will list all the users with the selected plan. Click on Export users to export the list of users with license information to a CSV file. Exported CSV file will contain below information:
- Last Name
- State or province
- Has license (true or false)
- City
- Country or region
- Display name
- User principal name
- Licenses
- Department
- Usage location
- AssignedProductSkus
- Dir sync enabled
- Is guest user
- Object id
- Job title
- First name
- Selected
- Office
Export Microsoft 365 User Licenses to CSV using Azure AD PS module
Please note that Azure AD powershell module is planned to be deprecated, however at the time of updating this blog post, it still works. You can use below powershell script which utilizes Get-AzureADUserLicenseDetail
cmdlet. If there are any issues with exporting the license report using Azure AD module, you can refer to the previous section and export the required details using Microsoft Graph (which is our recommended method)
- Copy below code and save it in .ps1 file. Update the variable
$userPrincipalName
to user’s UPN to get the license information and execute the script. Download the script from my github repo: M365_License_Export_One_User.ps1.
M365_License_Export_One_User.ps1
# Connect to Azure AD
Connect-AzureAD
# Provide UPN of the user
$UserPrincipalName = "[email protected]"
# Get the user object for the specific user
$user = Get-AzureADUser -Filter "UserPrincipalName eq '$UserPrincipalName'"
$result = @()
$licenses = Get-AzureADUserLicenseDetail -ObjectId $user.ObjectId
foreach ($license in $licenses) {
foreach ($plan in $license.ServicePlans) {
$result += [pscustomobject]@{
UserPrincipalName = $user.UserPrincipalName
DisplayName = $user.DisplayName
SKUPartNumber = $license.SkuPartNumber
ServicePlanName = $plan.ServicePlanName
ServicePlanStatus = $plan.ProvisioningStatus
}
}
}
# Export result to CSV
$result | Export-Csv -Path "C:\M365_User_License.csv" -NoTypeInformation
Write-Host "Export completed. File saved as C:\M365_User_License.csv"
M365_License_Export_All_Users.ps1
# Connect to Azure AD
Connect-AzureAD
# Get all users
$users = Get-AzureADUser -All $true
$result = @()
foreach ($user in $users) {
$licenses = Get-AzureADUserLicenseDetail -ObjectId $user.ObjectId
foreach ($license in $licenses) {
foreach ($plan in $license.ServicePlans) {
$result += [pscustomobject]@{
UserPrincipalName = $user.UserPrincipalName
DisplayName = $user.DisplayName
SKUPartNumber = $license.SkuPartNumber
ServicePlanName = $plan.ServicePlanName
ServicePlanStatus = $plan.ProvisioningStatus
}
}
}
}
$result | Export-Csv -Path "C:\M365_Users_Licenses_All.csv" -NoTypeInformation
Write-Host "Export completed. File saved as C:\M365_Users_Licenses_All.csv"
Export Microsoft 365 User Licenses to CSV using MSOL
Please note that MSOL powershell module is planned to be deprecated, however at the time of updating this blog post, it still works. You can use below powershell script which utilizes Get-msoluser
cmdlet. If there are any issues with exporting the license report using MSOL module, you can refer to the previous section and export the required details using Microsoft Graph (which is our recommended method).
Install-module MSOnline
Connect-msolservice
Get-MsolAccountSku
Get-msoluser | ft UserPrincipalName, @{L='Licenses Assigned'; E={($_.licenses).Accountskuid}} | Out-File c:\M365_LicenseExport.csv