Powershell: Export Microsoft 365 User Licenses to CSV

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.

Microsoft 365 service plan and license screenshot

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.

Accept graph permissions
  • 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"
Connect to graph and execute Powershell script to get M365 license info
  • 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.
M365 One User license Service Plan Export

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"
M365 license file exported to CSV for All users

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 M365 User License Info on Powershell console for one user

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
Display M365 User License Info on Powershell console for all users

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 *
Get M365 Consumed License Units

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

Export M365 License information from admin center
  • 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
Click Export Users

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

References

Leave a Comment