Export Office 365 User License Report With PowerShell

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 ease your work, you can use PowerShell commands. Using PowerShell commands, you can get O365 licensed users and export report to CSV file.

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

  1. A user can have multiple licenses.
  2. Each license gives access to a set of services.
  3. A user might not have access to all services. Some services might be enabled or disabled based on user needs.
  4. 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 assigned licenses, services, and its status. So! Let’s jump into the script right away.

You can Download the script from TechNet Gallery

Script Highlights:

  • Exports Office 365 user license report to CSV file.
  • 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
  • The script gives 2 output files. One with the detailed report of O365 Licensed users another with the simple details.
  • Output file stores most required attributes like Display Name, User Principal Name, Licenses, License Friendly Name, Service, Service Friendly Name, and Service Status. You can include more attributes by easily modifying the script.

 

Sample Outputs:

Script execution steps available in our  O365 users’ last logon time blog. Please refer the blog.

Exported report shows list of licensed users and their assigned licenses.

Detailed Office 365 User License Report:

Office 365 User License report

Simple Office 365 User License Report:

Office 365 user License report to csv

 

Let’s Check Export Office 365 Users License Script in Detail:

You must have MSOnline PowerShell module installed for the this script to work. If you do not have it already, please install by executing the below command-let in PowerShell.

To get Office 365 license report using PowerShell, we need to create a session between PowerShell and O365.

 

Set output file to store the result. Result files will be stored in the current directory.

The friendly name of the license plan and services are stored in an external file:  LicenseFriendlyName.txt has license plans and its friendly names.  ServiceFriendlyName.txt has service names and its friendly names.

 

Get-Msoluser will get the details of all O365 users. Where condition used to filter out licensed users alone. Below loop( ie, User loop) executes for all licensed users one by one.

 

Add another foreach-loop(ie, License loop) inside of user loop to get all Office 365 licenses that assigned to a current user. This loop gets services belongs to a particular license and store it in a services variable.

 

License subscription names are not user-friendly by default. We need to convert them to comprehensible format. LicenseFriendlyName.txt has license subscriptions and its Friendly names. Below lines convert license subscription to its friendly name. If the friendly name is not found in the hash table, the original name is used. Administrators can quickly add new SKU’s to the script by editing the LicenseFriendlyName.txt.

 

Another foreach-loop(ie, Service loop) added inside of license loop. It converts service name to its friendly name.

 

For each service, user’s name, License, service and its status get stored in the hash table. The script produces two output files. One with the detailed O365 licensed user report another with the simple detail. Detailed Office 365 user assigned license report contains user’s display name, upn, license plan, friendly name of license plan, service name, friendly name of service, and service status.

 

The simple Office 365 user license report contains user’s display name, upn, and friendly name of assigned license with their respective services.

 

Opens the output file after getting confirmation from administrator.

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.