Know How Much User License Costs Are and How Much Unassigned Licenses Absorb
The articles I’ve written about using the Microsoft Graph PowerShell SDK to create a tenant licensing report are very popular. The October 2021 article laid the foundation and explains the basics of extracting licensing information for Entra ID user accounts. Two years later, the follow-up article describes how to add support for group-based license assignments. I now want to address a common request and add support for cost reporting for both individual users and the tenant.
Few like to rewrite code. I am no different. With that in mind, I decided to make as few changes as possible when incorporating the new code to report license costs. Apart from anything else, this means that people who used the previous versions of the script to create their own licensing report should find it easier to update their version. At least, that’s the plan.
Licenses and Subscriptions
As a refresher, let’s cover the basics of Microsoft 365 licensing. Tenants pay for licenses through subscriptions. A subscription is for a product like Microsoft 365 E3. Some subscriptions are free, either unlimited or for a trial period. Subscriptions have unique SKU identifiers. The Get-MgSubscribedSku cmdlet returns the details of current subscriptions for a tenant.
Paid subscriptions have an associated monthly cost that differs from country to country in accordance with local taxation and other factors. For instance, the monthly cost for Office 365 E3 is $23 in the U.S. and EUR23.10 (without Teams) in Ireland. Because of its complexity, I don’t attempt to handle multi-country pricing here. The script assigns the same price to all licenses for an SKU.
Including Costs with License Subscription Data
Get-MgSubscribedSku tells us what subscriptions exist in a tenant. It doesn’t tell us anything about the price. I use a script (downloadable from GitHub) to take the CSV file published by Microsoft on the licensing and service plan reference page and use it to build CSV files containing subscription and service plan information. The data includes ‘friendly names’ for subscriptions and service plans to allow the script to report those names instead of GUIDs. By loading the data into hash tables, the script can quickly translate a GUID (like 06ebc4ee-1bb5-47dd-8120-11324bc54e06) to a product or service plan name (in this case, Microsoft 365 E5).
To add costs to the equation, I edit the CSV file holding SKU information so that the line for each product includes a price column. I then insert the price that I pay for each license into that column. Companies are free to negotiate prices with Microsoft and the cost paid for a subscription depends on how successful that negotiation is.
I also inserted a currency column, but only populate this column for the first product (because the script uses the same currency everywhere). Figure 1 shows what the SKU information CSV file looks like for my tenant.
To make the pricing information quick to access, the script reads the CSV file and creates a hash table comprising key-value pairs of the SKU identifier (key) and the monthly price (value).
Calculating License Costs
Previous versions of the script create an array of licenses (direct and group-based) assigned to user accounts. Calculating the cost of the licenses assigned to individual user accounts is a matter of:
- Computing the annual cost for a license (monthly cost * 12).
- Adding the annual cost for each license to compute the total cost.
The script does this in a simple PowerShell function. You can see that all prices are reckoned in cents to accommodate prices like $17.70 or EUR16.40.
Function Get-LicenseCosts {
# Function to calculate the annual costs of the licenses assigned to a user account
[cmdletbinding()]
Param( [array]$Licenses )
[int]$Costs = 0
ForEach ($License in $Licenses) {
[string]$LicenseCost = $PricingHashTable[$License]
# Monthly cost in cents (because some licenses cost sums like 16.40)
[float]$LicenseCostCents = [float]$LicenseCost * 100
If ($LicenseCostCents -gt 0) {
# Compute annual cost for the license
[float]$AnnualCost = $LicenseCostCents * 12
# Add to the cumulative license costs
$Costs = $Costs + ($AnnualCost)
# Write-Host ("License {0} Cost {1} running total {2}" -f $License, $LicenseCost, $Costs)
}
}
# Return
Return ($Costs/100)
}
Reporting License Costs
Before the script processes any user account, it checks if pricing information is available. It does this by checking the Price column for the first product in the SKU list. The script also fetches the currency (string prefix) from the currency column. If this data is available, the $PricingInfoAvailable variable is set to true to control whether the script computes cost information for assigned licenses and includes this in the output report. If the variable is false, no pricing information is included.
Figure 2 shows an example of the licensing report with cost information. The costs of all licenses assigned to a user appears under “Annual License Costs.”
After reporting data for all users, the report includes some summary information (Figure 3) including the total cost for each subscription (annual cost multiplied by number of licenses). You could cut and dice this information multiple ways. For instance, you could compute an annual cost for users in each country, each office, or each department.
I do like being able to compute an overall percentage of used license costs. Right now, my tenant spends $13,212. However, the cost of assigned licenses is $12,024, so my license assignment effectiveness is only 91.01% and Microsoft receives $1,188 annually for unused licenses.
Of course, this is test data, and I certainly do not leave that amount on the table for Microsoft to add to the $134.8 billion annual run rate for the Microsoft Cloud announced in Microsoft’s FY24 Q2 results.
Update: V1.6 of the script includes cost analyses by country and department. This article explains the code changes necessary to implement these features.
Not a License Cost Management Solution
It’s important to say that the script (which you can download from GitHub) is not a full-fledged license cost management solution. Instead, it’s a demonstration of how to access and use the license information available to tenants. Because the code is PowerShell, it’s easy to amend to suit specific requirements. If anyone finds an issue, please log it in GitHub. Better again, fix the problem and create a pull request so that everyone benefits from your contribution.
The Real Person!
The Real Person!
Tony, your script is awesome! Would it be possible to have a vision by companies? How many licenses of each product (SKU) do we have in each company? And also what is the cost of each product license per company? Thank you!
The Real Person!
The Real Person!
The current version of the report supports analysis by departments and cost centers. https://office365itpros.com/2024/07/23/microsoft-365-licensing-report-192/ Reporting by company should be a matter of making sure that the company property is populated and then amending the script to report by company as another level.
The Real Person!
The Real Person!
Tony, thanks for the reply! Our challenge as a holding with many companies is to quantify each type of license by company and, with that, distribute the monthly cost correctly. The company field is populated, but creating a table where we have the amount of product licenses versus the companies changes the logic of the current script.
The Real Person!
The Real Person!
Would this work?
License Costs by Company
Company Accounts Costs AverageCost
Cafe Rene 1 US $ 516.00 US $ 516.00
Contoso 1 US $ 277.80 US $ 277.80
CoreView 1 US $ 277.80 US $ 277.80
Drugs R’ Us 1 US $ 277.80 US $ 277.80
French Resistance 1 US $ 277.80 US $ 277.80
LinkedIn 1 US $ 277.80 US $ 277.80
Office 365 for IT Pros 4 US $ 1,111.20 US $ 277.80
R&A 1 US $ 457.80 US $ 457.80
R&A Austria 1 US $ 694.08 US $ 694.08
R&AA Operations 1 US $ 277.80 US $ 277.80
RA Germany 1 US $ 516.00 US $ 516.00
RA Ireland 2 US $ 1,151.88 US $ 575.94
Redmond & Associates 3 US $ 2,145.96 US $ 715.32
License costs for users without a department: US $ 5,441.88
If so, grab a copy of V1.93 of the report script from https://github.com/12Knocksinna/Office365itpros/blob/master/ReportUserAssignedLicenses-MgGraph.PS1
The Real Person!
The Real Person!
Tony, thanks for the help! We need something more detailed to help apportionment the monthly cost.
Example:
SKU Name Company1 Company2 Company 3 …. Total
Office 365 E3 12 1 23 36
Office 365 E5 1 10 39 50
The Real Person!
The Real Person!
That’s pnly a matter of formatting. The information is generated by the script. You just need to format it the way you want.
The Real Person!
The Real Person!
The layout you suggest is more difficult to implement than it seems. I’ve created V1.94, which lists the individual licenses consumed by companies in the report if the $DetailedCompanyAnalyis variable is true.
Do this before running the script
$DetailedCompanyAnalyis = $true
And see what you think
Hi! This script is great!!! Just having an issue with anything to do with the cost :), as I’m getting US $ 0.00 everywhere :/. Any ideas?
The Real Person!
The Real Person!
Have you included any currency information in the SKU CSV file?
Hi Tony,
Very helpful script. How can I get Microsoft365LicensesReport.html as excel?
Also like to know if I can get Microsoft365LicensesReport.html with filtering option.
Thank you
The Real Person!
The Real Person!
The script generates a CSV file that can be loaded into Excel. If you want to generate a workbook, update the script to use the ImportExcel module. https://office365itpros.com/2022/05/10/importexcel-powershell/
The Real Person!
The Real Person!
The PSWriteHTML module is a good way of generating a HTML file with filtering options. See this article for an example: https://office365itpros.com/2022/07/20/sharepoint-external-users-report/
Hi Tony, I Love the update. Is there a way to break the costs down even further for departments as well? We have multiple departments in our tenant and would like to look at costs for each department as well. Any thoughts or ideas would be appreciated. My basic thought is…. Department name | Total licensed Users | Total Department Cost … All of the data is there; I just don’t know how to group and total it and then add it to the output report.
The Real Person!
The Real Person!
This should be very easy. Give me a couple of days and I will look at updating the script.
The Real Person!
The Real Person!
I published a new version (1.6) of the script which includes analyses by department and by country. The steps required to generate this data are explained in this article: https://office365itpros.com/2024/02/14/microsoft-365-licensing-report/