Table of Contents
Microsoft 365 Licensing Report Details Costs Per User to Find Optimizations
Recently, I released an update to my Microsoft 365 Licensing Report PowerShell script to include the ability to assign costs to user accounts. The idea is to give administrators information about how much the cumulative annual license charges are for each account. Combining cost data with insight about account activity in a tenant (generated with the user activity report script or by reference to the individual workload usage reports in the Microsoft 365 admin center), administrators can figure out if users have the right licenses they need to work and no licenses are assigned to inactive accounts.
Managing the cost of Office 365 and Microsoft 365 licenses has always been important. As Microsoft puts more focus on driving revenue through high-priced add-ons such as Teams Premium ($120/year) and Copilot for Microsoft 365 ($360/year), it’s even more essential to keep close tabs on license assignments. There’s no point in assigning a Copilot license to someone who’s inactive or whose usage pattern indicates that they might not take advantage of the license. No one is rewarded for overspending on licenses.
Adding Cost by Department and Cost by Country to the Microsoft 365 Licensing Report
Almost immediately after releasing the updated script, calls came in to ask if it was possible to generate an analysis of licensing cost by country and by department. My initial response was “sure” and I set to figuring out the best way to implement the change.
Because the report script tracks license costs per user, the simple method is to:
- Find the sets of departments and countries in user accounts.
- For each department (or country), calculate the sum of license costs.
- Include the information in the report.
The same approach works to analyze license costs for any user account property fetched by the initial Get-MgUser command at the start of the script. If the set of regular account properties don’t work for your organization, you could use an Exchange custom attribute to store the required values. For instance, you could include a cost center number in a custom attribute. Here’s how to access Exchange custom attributes with Get-MgUser. You’ll need to extract the information from the custom attribute before you can use it in the script.
The Problems Caused by Inaccurate Directory Data
The obvious problem is that sometimes the properties of user accounts don’t include a department or country. Account properties should hold accurate properties, but unfortunately this sometimes doesn’t happen because administrators fail to add properties to accounts, or a synchronization process linking a HR system to Entra ID encounters problems, or something else conspires to erode directory accuracy. The point is that inaccurate or missing user account properties result in bad license accounting.
The first order of business is therefore to validate that the account properties that you want to use for license cost reporting exist and are correct. This article explains how to detect user accounts with missing properties. Making sure that properties are accurate requires an extra level of review. The value of the country property assigned to user accounts shouldn’t change frequently, but properties like department and office might.
Reporting Licensing Costs for Country and Department
After making sure that all the necessary user account properties are in place (and accurate), the code to generate cost analyses based on department and country worked like a dream. The script also required an update to insert the new data into the output report, including warnings for administrators when costs cannot be attribute to countries or departments because of missing account properties. Figure 1 shows the result.
The code changes are in version 1.6 of the report script, which you can download from GitHub. If you haven’t run the script before, make sure that you read the previous Practical365.com articles to understand how the script works and how to generate the two (SKU and service plan) CSV files used by the script.
Remember that this script is intended to demonstrate the principles of interacting with and interpreting Entra ID user account and license information with the Microsoft Graph PowerShell SDK. It’s not intended to be a bulletproof license cost management solution. Have fun with PowerShell!
Learn how to exploit the data available to Microsoft 365 tenant administrators (like licensing information) through the Office 365 for IT Pros eBook. We love figuring out how things work.
Thanks for this! where can we find the SkuDataComplete.csv and ServicePlanDataComplete.csv files?
There are details in the script telling you how to generate the SKU and service plan CSVs. The original article (on Practical365.com) explains how by running a script (also available from GitHub). I’ve tweaked the text to make this more obvious.
There’s also details in the script:
# This step depends on the availability of some CSV files generated to hold information about the product licenses used in the tenant and
# the service plans in those licenses. See https://github.com/12Knocksinna/Office365itpros/blob/master/CreateCSVFilesForSKUsAndServicePlans.PS1
# for code to generate the CSVs. After the files are created, you need to edit them to add the display names for the SKUs and plans.
# Build Hash of Skus for lookup so that we report user-friendly display names – you need to create these CSV files from SKU and service plan
# data in your tenant.
hello Tony many thanks for your work
i added the price et Currency (EUR) for me, but the script don’t find the price value in the files SKUDATAcomplete.csv could you please provide an template CSV files with price for test
You’ve got to populate the CSV file with the prices your organization pays Microsoft for licenses. I have no knowledge of what you pay per license.
Hello Tony,
Your script is AMAZING, really thank you for that.
If I may, I have two questions. I encountered error while running PS: “Where-Object : The argument to operator ‘Imatch’ is not valid: parsing “NAME OF DEPARTMENT”. Looks like it runs versus every user account.
And in .html file under “License Cost by Department” if there is only 1 person in department html shows nothing in “Accounts” tab.
If you will find to answer, I will be really grateful, thank you for your amazing work. 🙂
What values exist in the department property for user accounts? Type $Users.Department to see…
I don’t see this error here, so it’s likely due to something specific to your tenant.
I checked and there is whole bunch of department names. What I noticed, this error occurs only if I put price in “SkuDataComplete.csv” file. Without price for MS licenses, this error does not appear.
Department name in this error is really long, might it be a problem?
The error is coming when the report script attempts to calculate the cost of licenses assigned to individual departments:
$DepartmentReport = [System.Collections.Generic.List[Object]]::new()
ForEach ($Department in $Departments) {
$DepartmentRecords = $Report | Where-Object {$_.Department -eq $Department}
$DepartmentReportLine = [PSCustomObject][Ordered]@{
Department = $Department
Accounts = $DepartmentRecords.count
Costs = (“{0} {1}” -f $Currency, (‘{0:N2}’ -f ($DepartmentRecords | Measure-Object UserCosts -Sum).Sum))
AverageCost = (“{0} {1}” -f $Currency, (‘{0:N2}’ -f ($DepartmentRecords | Measure-Object UserCosts -Average).Average))
}
$DepartmentReport.Add($DepartmentReportLine)
}
You could check that the departments are correctly captured in the $Report list. You could then check that the $DepartmentRecords list is correct. If that list is not populated, the computations can’t work.
If you want to export your $Report to a CSV file and upload it to somewhere that I can check it, I will have a look.
I’ve run piece of script you’ve pasted and I’ve checked $Reports – it returns every user with department, UPN, Title, etc.
When I run $DepartmentRecords, there are only 2 entries on this list.
The list of departments is derived from the list of users…
[array]$Departments = $Users.Department | Sort-Object -Unique
If there’s only two departments, do you have only two unique values for department names?
Hello Tony,
No, of course not. We have like ~40 different department names.
https://imgur.com/oWFZvnd – please see attachment.
When I run [array]$Departments = $Users.Department | Sort-Object -Unique there is every department listed.
Also – error screenshot – https://imgur.com/a/PBkAke5
There are lots of those errors, I guess it runs vs every licensed user account.
TRy the version I just uploaded to GitHub. I didn’t cast the variables used for $departmentreport and $Countryreport as arrays. PowerShell can be funky about counts when only a single record is returned and the receiving variable isn’t an array… I also changed the match statement to a more conventional Where-Object comparison. Everything seems to work here:
$DepartmentReport
Department Accounts Costs AverageCost
———- ——– —– ———–
Business Development 2 US $ 735.60 US $ 367.80
CEO Office 1 US $ 277.80 US $ 277.80
Cloud Engineering 1 US $ 694.08 US $ 694.08
Cocaine 1 US $ 457.80 US $ 457.80
Editorial 1 US $ 277.80 US $ 277.80
Global HQ 2 US $ 1,451.88 US $ 725.94
Group HQ 1 US $ 277.80 US $ 277.80
Information Security 1 US $ 277.80 US $ 277.80
Information Technology 11 US $ 3,790.56 US $ 344.60
IT Operations 1 US $ 277.80 US $ 277.80
Marketing 1 US $ 277.80 US $ 277.80
Planning & Action 1 US $ 277.80 US $ 277.80
Project Management 2 US $ 735.60 US $ 367.80
Research and Development 2 US $ 555.60 US $ 277.80
Sales 3 US $ 833.40 US $ 277.80
Security Research 1 US $ 457.80 US $ 457.80
Services 1 US $ 694.08 US $ 694.08
Systems Operations 3 US $ 555.60 US $ 185.20
Hello,
New version works flawless! There is no problems with departments anymore, if there is only one person in department now it shows number in “Accounts” 🙂
One last thing is that there are two errors at the end of script – https://imgur.com/QdVmOpN
They were here before – when I send you previous screenshots. They do not cause problems, but I attached them for your information.
Once more – thank you for this script, it is amazing.
You must have some accounts where sign in data is not available… In any case, this problem is in the color formatting of the inactive user column, which depends on the number of daus since the last sign-in. I’ve added a check for the condition and all is well in V1.91, available from GitHub now.
Indeed, I have 2 accounts where sign in data is not available. After your update it shows last sign in as “Unknown” and there is zero other errors. Thank you so much.
Hi Tony,
I’ve come with another question…
Is it possible for you to add extensionAttributes in script? We use certain extensionAttributes to add cost center for each user… It would be huge to have every user with cost center exported just like department is exported in .csv and .html…
What kind of extension attributes? The Entra ID directory extensions (https://practical365.com/directory-extensions-entra-d/) or the custom attributes synchronized from Exchange? In either case, I won’t add them to the script because every organization is different. However, you certainly can add whatever attribute you like by fetching the attributes in the Get-MgUser cmdlet and making sure that the data is output to the report.
Hello,
I meant extenstionAttributes synced from on-prem AD to Entra ID.
I’ll try do it myself. 🙂
I believe those are the custom attributes from Exchang. It should work fine.