Updated 1 month ago

Export Office 365 User License Report With PowerShell

by Kathy Cooper

5 min read

1 Comment

As an Office 365 Administrator, often you are in the situation to get all licensed users with their assigned licenses and services. Using the O365 admin portal, you can get user’s license information, but you need to click each user to know about their license subscriptions and service status which is a cumbersome task. To manage Office 365 licenses, you can use PowerShell commands. Get-MsolUser will get the details of all Office 365 users and Where condition used to filter out licensed users alone.

Do you think it’s easy? Definitely not! Because we need to consider following things before jumping right away.

  1. Msol PowerShell module is under deprecation. So, you need to switch to MS Graph PowerShell and the Get-MgUser cmdlet. If the required data is not available in the cmdlet, you need to use Microsoft Graph beta module cmdlet Get-MgBetaUser.
  2. A user can have multiple licenses.
  3. Each license gives access to a set of services.
  4. A user might not have access to all services. Some services might be enabled or disabled based on user needs.
  5. License subscription names are not user-friendly by default.

We have created a PowerShell script that will get you the detailed license usage report covering all the above cases. It reports all licensed users with their country, assigned licenses, services, and its status. So! Let’s jump into the script right away.

Download Script: O365UserLicenseReport

Script Highlights:

  • The script uses MS Graph PowerShell and installs MS Graph PowerShell SDK (if not installed already) upon your confirmation.
  • It can be executed with certificate-based authentication (CBA) too.
  • Exports Office 365 user license report to CSV file.
  • You can choose to either “export license report for all office 365 users” or pass an input file to get license report of specific users alone.
  • License Name is shown with its friendly name like ‘Office 365 Enterprise E3’ rather than ‘ENTERPRISEPACK’.
  • The script can be executed with MFA enabled account too.
  • The script gives 2 output files. One with the detailed report of O365 Licensed users another with the simple details.

Office 365 License Report – Sample Outputs:

Exported report shows list of licensed users and their licenses assigned via Microsoft365DSC, PowerShell, or manual methods.

Detailed Office 365 User License Report:

Office 365 User License report

Detailed Office 365 users’ assigned license report contains user’s Display Name, User Principal Name, License Plan, Friendly-name of License Plan, Service Name, Friendly-name of Service, and Service Status.

Simple Office 365 User License Report:

Office 365 user License report to csv

The simple Office 365 user license report contains user’s Display Name, User Principal Name, and Friendly-name of assigned license with their respective services.

Note: As an admin, it’s essential to know when your subscriptions are going to expire or when Microsoft will charge for renewal. If you are not sure about the answer, you can check Office 365 subscription expiry date report.

Office 365 User License Report with MS Graph – Execution Methods:

Admins prefer various methods to assign licenses to Microsoft 365 users, such as through the admin center, bulk assignment using CSV, group-based licensing, etc. Regardless of the method you’ve chosen, this PowerShell script exports all licensed users in your Office 365 environment. You can choose any one of the below methods based on your need.

Note: If you specifically need to check user licenses assigned through group membership, you can use the PowerShell script to export group-based license assignments.

On the other hand, if you want to compare between directly assigned/group-based license assignments, you can use the PowerShell script to find and export user license assignment paths.

1.Export all Licensed Users in Office 365:

Tracking licensed users in M365 is crucial for compliance and to identify cost spent on unused licenses. Based on this report, admins can assign or remove licenses to optimize service usage, enhancing productivity.

To export all licensed users in Office 365, run the script as follows.

While this script helps you manage licensed users, it’s equally important to keep track of unlicensed users to ensure compliance and optimize your subscriptions.

2.Get License Report for List of Office 365 Users:

For organizations with diverse projects or departments, generating reports for specific user lists allows a more focused examination of license usage. To achieve this, you can pass the input through a CSV file and generate a license report for those users.

Utilize the ‘-UserNamesFile’ parameter to pass the CSV file and retrieve license reports for users from the input list. For example,

The UserNamesFile must follow the format below: UPN separated by new line without header.

Office 365 User License report PowerShell

3.Execute the Script with Certificate (Scheduler-friendly):

To automate the script execution, you can use certificates for authentication. Depending on your requirements, you can choose to use a certificate authority (CA) or create a self-signed certificate, which is more cost-effective.

The script can be executed with Certificate-based Authentication(CBA) by specifying the TenantId, ClientId, and CertificateThumbprint parameters in the following format:

This format can also be used to schedule the PowerShell script as a scheduled task in the Windows Task Scheduler.

However, it’s important to note that before using certificate-based authentication, you must register an app in Azure AD. To register an Azure app and obtain certificates, you can refer to the connect MS Graph with certificate blog, which provides detailed instructions for manual or automated setup depending on your needs.

Get Office 365 License Reports for Free with AdminDroid:

Why AdminDroid is top choice for Microsoft 365 license reporting?
  • Schedules and sends reports to email
  • Exports data in various formats, such as CSV, HTML, PDF, etc.
  • Filters data to generate fine-grained reports
  • Visualizes report data to charts/AI generated graphs
  • Manages multiple tenants
  • User friendly UI
  • License and other 120+ Azure AD reports available in Free Edition itself.

AdminDroid offers a free Office 365 license reporting tool that provides detailed license usage reports and smart dashboards. License reports are categorized based on

  • Regainable licenses
  • Users and Licenses
    • Licensed users
    • Unlicensed users
    • Users with specific subscription
    • Users with free subscription
    • Users with trial subscription
    • User license changes
  • License expiry
    • Purchased subscription expiring users
    • Purchased subscription expired users
    • Trial expiring users
    • Trial expired users
    • Users with suspended subscription
  • Subscription
    • Subscription usage report
    • Expired subscription
    • Unused subscription
    • Purchased subscription
    • Trial subscription

Azure AD dashboard by AdminDroid

Microsoft 365 subscription dashboard by AdminDroid

Office 365 licensed users report by AdminDroid

Download Free Microsoft 365 reporting tool by AdminDroid to get access to 120+ reports and dashboards on users, groups, group membership details, licenses, user logins, password changes, etc.

Additionally, AdminDroid Office 365 reporting tool provides 1800+ pre-built reports and 30+ smart visually appealing dashboards to know about your Office 365 environment at a glance. This tool provides reports on Office 365 reporting, auditing, analytics, usage statistics, security & compliance, etc.

I hope export Office 365 license report using PowerShell blog was helpful. If you modify the script and use it for other use cases, then please leave your idea in the comment section and help more admins.

Share article