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).

Different Forms of Cost Centers

On June 20, I announced version 1.9 of the Microsoft 365 Licensing Report. A month later, version 1.92 is available for download from GitHub. This version adds support for reporting licensing costs by cost center. Here’s how it works.

Ever since Exchange Server added a set of 15 custom attributes to mailboxes, organizations have used the attributes to hold all kinds of information. Cost center numbers come in different formats. In Digital Equipment Corporation, the numbers (or rather, designation) were values like 8ZW and 9HPE. In Compaq and HP, the values were more like 1001910. In any case, organizations often store cost center values in custom attributes to allow a more precise assignment of costs than is possible using standard Entra ID account properties like city, department, and country.

For cost center reporting to work, it’s obvious that accurate cost center numbers must be present in Exchange mailbox properties. Sometimes cost centers are added when users join an organization and receive a mailbox and are never updated afterwards. In other instances, organizations have synchronization mechanisms in place to ensure that if a change is made to an employee’s cost center (usually in a HR database), that change also happens for mailbox properties.

It might also be possible to implement cost center reporting based on managers (if managers manage cost centers). To do this, the script would have to find all the managers and assume that any direct reports are in the same cost center as the manager. I discounted this method and chose the simpler approach of using cost centers stored in a custom attribute, but it wouldn’t be difficult to code because Entra ID links stores details of the manager for each user account. Storing a manager for an account is not mandatory, so the same problem of data accuracy and availability might be present.

Microsoft 365 Licensing Report Script Changes to Support Cost Centers

The script supports cost center reporting through a variable called $CostCenterAttribute, which holds the name of the custom attribute to use. The name stored in the variable is the Entra ID property name rather than the Exchange name, so it’s a value like extensionAttribute1. If $CostCenterAttribute is not defined, the report doesn’t attempt to generate any information about licensing cost per cost center.

Exchange Online synchronizes the values of the mailbox custom attributes to the Entra ID user accounts of the mailbox owners. The custom attributes are stored in a property called OnPremisesExtensionAttributes. The Get-MgUser command to fetch user account details is amended to include OnPremisesExtensionAttributes in the set of retrieved properties. A set of cost centers found in user accounts is derived from the information retrieved by Get-MgUser.

When scanning user accounts for license information, the script extracts the cost center for each account and stores it along with other licensing data in a PowerShell list. This allows the report to later loop through the set of cost centers found in user accounts and calculate the licensing spend for each cost center, much like the licensing spend analysis done for departments and countries.

Reporting Licensing Spend by Cost Center

The script then outputs the cost center licensing spend analysis along with the other spending data in the summary part of the report (Figure 1).

Custom Attributes Open Up Lots of Opportunity

In this instance, the Microsoft 365 licensing report uses a custom attribute to store a cost center value. It is easy to see how custom attributes could be used for other analysis. For example, if a custom attribute held details of major projects, you could report the licensing spend for each project. All of this is basic PowerShell, so feel free to experiment!

Learn how to exploit the data available to Microsoft 365 tenant administrators through the Office 365 for IT Pros eBook. We love figuring out how things work.

Highlighting License Costs for Disabled and Inactive Users with Color

The Microsoft 365 Licensing report is one of the more popular scripts I’ve written. The last set of updates added analysis of licensing costs by department and country. I maintain a list of things that people have asked me to add to the script. Last week, I wanted to take a break from the work to prepare the new edition of the Office 365 for IT Pros eBook, so I fired up Visual Studio Code and got to work.

On my to-list were the following:

• Highlight disabled counts better and report the cost of licenses assigned to disabled accounts.
• Highlight the cost of licenses assigned to user accounts that haven’t signed in for 90 days or more.
• Add Excel worksheet output using the ImportExcel module.
• Categorize the license spend for individual user accounts to be under, average, or high based on the average cost for the tenant.
• Use color to highlight important points in the HTML report (Figure 1). I’m color blind, so the colors I selected to highlight different values might not be to your taste. If so, feel free to select different colors and modify the script by inserting the hex code values of those colors into the style sheet for the report.
• Fix some small bugs. There’s always a couple to clean up.

Summarizing Licensing Costs

Figure 2 shows the updated summary of costs generated at the end of the HTML report. The cost analyses by country and department were in the last update, but I fixed a bug where the report didn’t deal as well as it should do when no licenses are assigned to accounts without a department or country.

The new information is in the section for inactive user accounts and disabled user accounts. Each category lists the set of user accounts that match the criteria together with the total cost of licenses assigned. I used 90 days since the last sign-in to decide if an account is inactive. It’s easy to modify the script to use a higher or lower value, depending on how long it takes before your organization considers an account to be inactive.

Generating an Excel Worksheet for the Licensing Data

Many PowerShell scripts generate CSV files for their output. It’s natural that this should be the case. The Export-CSV cmdlet is part of base PowerShell, and the CSV file format is easy to work with and the data is easy to import back into a PowerShell array.

Some of the CSV files end up as Excel worksheets. It’s easy to do this by opening the CSV file with Excel and saving the file as a worksheet. The ImportExcel module supports the generation of worksheet in many different styles with data inserted into a table ready to be analyzed (Figure 3).

The script checks if the ImportExcel module is available. If it is, the script generates an Excel worksheet. If not, the licensing data is exported to a CSV file.

Important Note and How to Get the Script

If you haven’t run the script before, make sure to read these Practical365.com articles to understand how the script works, how to generate the two (SKU and service plan) CSV files used by the script, and how to add cost data for Microsoft 365 subscriptions. Basically, some up-front work is necessary to prepare reference data for the script to use in its analysis. The code can extract details of user accounts and their assigned licenses from Entra ID, but turning GUIDs into human-friendly product names requires some help. The cost of Microsoft 365 subscriptions differs from country to country too.

You can download V1.9 of the script from GitHub.

Microsoft 365 tenants can have large quantities of licenses to manage. This script might help as written, or inspire you to create your own version tailored to the needs of your organization

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.