Adding Detailed License Assignment Reporting

The Microsoft 365 licensing report script is possibly the most popular PowerShell project I’ve published. Given the amount of money that organizations can lay out on Microsoft 365 licenses, I guess people like keeping an eye on where the pennies get spent.

Over the past few months, I’ve responded to several requests for enhancements, such as highlighting license costs for disabled accounts, adding support for a cost center analysis, and so on. More requests keep on coming in, the latest being the desire to be able to report license costs per company within a tenant that supports several operating companies.

Previously, I’d been asked to include a line-by-line (or rather license-by-license) report per user. At the time, I didn’t see much point in doing this, but further reflection made me think that it would be good to output a list of individual license assignments that administrators could slice and dice to meet their needs.

Detailed License Assignment Report

V1.94 of the Microsoft 365 licensing report script does just that. Each license assignment, direct or via group-based licensing, is captured in an array called $DetailedLicenseReport. The report script creates the array automatically. To illustrate how the information can be used, if the $DetailedCompanyAnalyis variable is $true (the variable is set to $true in the script), the report script generates a detailed report about license assignments for each company found in the tenant (Figure 1). Each license is listed along with its assignees and the monthly cost of the licenses.

Obviously, creating such a report depends on accurate values in the company property of user accounts. If you didn’t want to report by company, it would be easy to change the code to create a detailed report of license assignments by department or cost center.

Removing Expired Licenses

The new version of the Microsoft 365 Licensing Report script also addresses expired licenses. When a subscription for a license comes to an end, it’s possible that some accounts have assignments for expired licenses. Eventually, Microsoft 365 removes the expired subscription and the expired licenses, but until this happens it’s possible that the report would include detailed of expired licenses, which is not what anyone would want.

I discovered this possibility when my tenant replaced some Office 365 E5 licenses with Office 365 with Office 365 E5 EEA (No Teams) licenses. This is one of the licenses created without a Teams service plan in an attempt to satisfy anti-competition concerns of the European Union. Microsoft subsequently decided to decouple Teams from all Office 365 and Microsoft 365 products, and new customers can no longer buy licenses that include Teams.

In any case, my tenant has some of the Office 365 E5 EEA (No Teams) licenses and separate Microsoft Teams EEA licenses, but some of the licenses from the older Office 365 E5 subscription still turned up on the report. This doesn’t happen anymore because the script now checks licenses against current subscriptions to remove any trace of expired subscriptions.

Downloading and Using the Microsoft 365 Licensing Report Script

V1.94 of the Microsoft 365 licensing report script is available from GitHub. If you haven’t used the script before, I encourage you to read the original article that launched me on this path and another article describing how the script learns about the costs of individual licenses. Things will become much clearer when you understand the basics of Microsoft 365 licensing, SKUs, products, service plans, and how to generate the CSV files used by the script.

Remember, this is all done with PowerShell and some calls to the Microsoft Graph to find information about the licenses assigned to users. No black magic is used. A script that’s over 700 lines long might seem intimidating, but many of the lines are blank or comments. Like any other PowerShell script, have fun amending the code to meet your needs!

Support the work of the Office 365 for IT Pros team by subscribing to the Office 365 for IT Pros eBook. Your support pays for the time we need to track, analyze, and document the changing world of Microsoft 365 and Office 365 (and write scripts like the Microsoft 365 Licensing report).