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.
Hi Tony,
thank you for your great work and that you publish scripts like these for the public to use! I am trying to run your “ReportUserAssignedLicenses-MgGraph.PS1” Script and getting the “Detailed Company Analysis” xlsx File as Output too.
As detailed within the script, I added the two columns “Price” and “Currency” to the “SkuDataComplete.csv” file, and added a price to every SKU lne as well as “EUR€” within the first line of the “Currency” column.
The data seemingly gets processed by the script successfully, since I get the .html + the .csv output file generated without any problem, but no “Detailed Microsoft 365 Licensing Report.xlsx” file gets generated, and I do not receive any information, why this might be the case.
I also set the variable “DetailedCompanyAnalysis” to “$true”.
Could you maybe help out here and tell me why the xlsx file is not being generated?
Did you use a comma or period separator for the price data? Commas won’t work…
I read pretty much all of the comments and information I was able to gather on your blogpost(s) about this License Report, including the fact that I need to use periods instead of commas as a separator for the price data. I am based in Europe, but all of the License Prices I gathered I added to “SkuDataComplete.csv” with a Period as a separator. Example entries within “Price” Column of “SkuDataComplete.csv”:
28.08
4.72
Any other idea, why the Detailed Report as xlsx is not being generated? I have several license entries where I had to add “0” as price, since it is currently unknown. Might that be the problem?
Is the pricing information in the $Report list? If you look at $Report[0], do you see a value for ‘annual license costs’?
What’s the value of the $PricingInfoAvailable variable? It should be $true.
Not entirely sure, what you mean by “$Report list”. The “Microsoft 365 Licensing Report.csv” as well as the report in .html format “Microsoft 365 Licensing Report.html” both show, that the pricing information I provided within the “SkuDataComplete.csv” file is being used within the script. The .csv and .html files show a populated “Annual License costs” column for every user. .html Report also shows “License Cost Analysis”, “License Costs by Country”, “License Costs by Department” – all of it is showing data and is being populated.
$PricingInfoAvailable within the PS script itself is set to true as well.
I am confused, since the script doesn’t give me any pointers as to why this is happening and its not generating the .xlsx file.
Any other idea?
$Report is the PowerShell list created after scanning all mailboxes. If the script detects that pricing information is available, there should be a property in $Report called “annual license costs.” The fact that there is in the CSV and HTML files indicates that there is, so that is at least a good sign. I assume that the $PricingHashTable (hash table) is populated with SKU identifiers and prices. For example, if you list the table, you see values like:
$PricingHashTable
Name Value
—- —–
093e8d14-a334-43d9-93e3-30589… 0
05e9a617-0261-4cee-bb44-138d3… 36
a403ebcc-fae0-4ca2-8c8c-7a907… 0
The pricing hash table is used to lookup the price for a license (SKU). If it doesn’t get the right information, the script can’t calculate the prices in this code:
[int]$Costs = 0
ForEach ($License in $Licenses) {
Try {
[string]$LicenseCost = $PricingHashTable[$License]
# Convert monthly cost to 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)
}
}
I can only reply to your previous comment, unsure why. Theres no “Reply” button there for your latest comment for me.
THANK YOU for the explanation! I just double checked what you’re asking for and ran the script once more; after running the script, running “$PricingHashTable” gives me the output in the format you mentioned, couple lines as example:
Name Value
—- —–
6470687e-a428-4b7a-bef2-8a291… 0.00
e612d426-6bc3-4181-9658-91aa9… 37.44
a36cdaa2-a806-4b6e-9ae0-28dbd… 11.80
a36cdaa2-a806-4b6e-9ae0-28dbd… 11.80
Every Line of that output has a corresponding Value entry in there – even though many of the license prices are unknown to me currently, so I set the value to “0.00”.
If I understood you correctly, by “$Report” you mean these lines within the script:
$Report = [System.Collections.Generic.List[Object]]::new()
$DetailedLicenseReport = [System.Collections.Generic.List[Object]]::new()
Running the command “$Report” after the script ran through does not give me any output however. Same goes for “$DetailedLicenseReport”.
Does that point us in the right direction of whats going wrong here?
$Report is an output list that’s created by processing the license information for accounts. Running those lines only creates the list(s).
Before the data for a user is written into the report, the script calls the Get-LicenseCosts function to calculate the costs of the licenses assigned to a user. You can test that by doing something like this:
$SkuId = ‘6fd2c87f-b296-42f0-b197-1e91e994b900’
[float]$UserCosts = Get-LicenseCosts -License $SkuId
$UserCosts
277.8
Select any of the SKUs listed in the pricing hash table and test that (preferably one with a non-zero price) and see what happens when you look at the $UserCosts variable. It should have a value in it if the cost calculation function is working.
Unsure if this is relevant context, but here we go:
I am running the script by starting Powershell 7 as a local administrator, and NOT the user I am currently logged on to the machine; with a tiered admin account that specifically has local admin rights.
I then navigate to where I saved the Powershell script, directly running it from within the Shell.
The script starts with the output “Finding licensed user accounts…”, continuing to find all the user accounts with licenses assigned to them within our tenant. It then proceeds to process each user license data one by one.
The script completes with the message “Microsoft 365 Licensing Report complete”, giving me the information that a HTML as well as CSV report was created within the Downloads folder of the local administrator user, with that I started PowerShell.
Now, after running the script, when I try to follow your instructions, I did the following within the opened PowerShell session:
$SkuId = ‘6fd2c87f-b296-42f0-b197-1e91e994b900’
[float]$UserCosts = Get-LicenseCosts -License $SkuId
That command gave me an error, stating that the term “Get-LicenseCosts” is not recognized. I then manually copy-pasted the relevant Function code from the Script within the ongoing PS Session:
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) {
Try {
[string]$LicenseCost = $PricingHashTable[$License]
# Convert monthly cost to 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)
}
}
Catch {
Write-Host (“Error finding license {0} in pricing table – please check” -f $License)
}
}
# Return
Return ($Costs / 100)
}
But that didnt exactly work. Why does the script’s “Get-LicenseCosts” function apparently work during the script process, but after the script ran through, trying to run the lines you provided, shows me that theres no function called “Get-LicenseCosts”?
You’ve got to add a function to a PowerShell session before it can run. Pasting the code into your current session does that.
The only permissions required by the script are the Graph scopes to read users, groups, and the organization. It doesn’t seem that you have a problem there because you’ve observed users being processed.
To check, you can run Get-MgUser, Get-MgGroup, and Get-MgOrganization. If all of those work, then you have the permissions.
You could also run Get-MgContext to see what account is being used to run the code.
To be a little more specific, when you run Connect-MgGraph in PowerShell, you connect using whatever delegated permissions have been consented to for the Microsoft Graph PowerShell SDK app PLUS any administrative roles are held by the signed in account (for example, Exchange administrator). The script needs the scopes mentioned in the code, like Directory.Read.All and AuditLog.Read.All.
The alternative is to use an Entra ID app to run the script (app-only mode). In this scenario, you create an app, grant the necessary application permissions to the app, and pass the app details (including an app secret or certificate) to authenicate with Entra ID. See https://practical365.com/connect-microsoft-graph-powershell-sdk/ for more information. A script like this is a good candidate to be run in app-only mode because it removes the need to hold any administrative role.
Sorry for the delayed response; as it stands, it doesnt look like to be a permission issue, or that the script cannot read the license cost information I provided. I tried running the script from a non-admin PowerShell, with my own default user account credentials – which worked as well as it did before. Which confirms what you already said, that its not a permission issue.
Do you have any other pointer in the right direction, as to why the xlsx file is not being generated? I am currently clueless as to why the script gives me “just” the .HTML and .CSV reports.
It completes with this text, not giving me any kind of feedback as to why the .xlsx Report is not being generated:
Microsoft 365 Licensing Report complete
—————————————
An HTML report is available in c:\privat\downloads\Microsoft 365 Licensing Report.html
A CSV report is available in c:\privat\downloads\Microsoft 365 Licensing Report.CSV
ClientId :
TenantId :
Scopes : {Agreement.ReadWrite.All, Application.Read.All, AppRoleAssignment.ReadWrite.All, AuditLog.Read.All…}
AuthType : Delegated
TokenCredentialType : InteractiveBrowser
CertificateThumbprint :
CertificateSubjectName :
SendCertificateChain : False
Account :
AppName : Microsoft Graph Command Line Tools
ContextScope : CurrentUser
Certificate :
PSHostVersion : 7.4.6
ManagedIdentityId :
ClientSecret :
Environment : Global
Do you have the ImportExcel module installed for PowerShell? If not, that’s one reason why the script wouldn’t generate a worksheet.
Sorry if I double post!
But I tried to answer to your comment from yesterday 29.09.2024 but it did not display my message?!
Thank you for your fast answer!
1st I tried it now like this:
SkuId,”SkuPartNumber”,”DisplayName”,”Price”,”Currency”
c5928f49-12ba-48f7-ada3-0d743a3601d5,”VISIOCLIENT”,”Visio Online Plan 2″,”11.22″,”EUR”
2nd like this:
SkuId SkuPartNumber DisplayName Price Currency
c5928f49-12ba-48f7-ada3-0d743a3601d5 VISIOCLIENT Visio Online Plan 2 11.22 EUR
both with the same output:
User UPN Country Department Title Company Direct assigned licenses Disabled Plans Group based licenses Annual License Costs Last license change Account created Last Signin Days since last signin Duplicates detected Status Account status UserCosts Cost Center
User UPN Country Dep. Title Company f30db892-07e9-47e9-837c-80727f46fd3d, c5928f49-12ba-48f7-ada3-0d743a3601d5, 639dec6b-bb19-468b-871c-c5c441c4b0cb, 06ebc4ee-1bb5-47dd-8120-11324bc54e06 EUR 0,00 datelastlicchange dateacccreated datelastsignin 0 N/A OK Enabled 0
Maybe I need to change my Windows default settings?!
My default OS settings are:
Decimal Symbol “,”
List Seperator “;”
Thanks,
Hi, Sorry about not being able to post a comment. The site gets an incredible number of spam comments and sometimes the good stuff is intercepted.
Try putting this statement at the start of the script (see https://stackoverflow.com/questions/2379514/powershell-formatting-values-in-another-culture). It will force PowerShell to use U.S. English for formatting strings etc.
[cultureinfo]::currentculture = ‘en-US’
I suspect that there are several places in the script where non-English locales will encounter difficulties. In this case, I suspect that the calculation of the monthly and yearly license costs for a user fail because of locale.
Hi Tony!
adding [cultureinfo]::currentculture = ‘en-US’ to your script didn´t change anything.
I added it to Line 26 in your script before “function get-licensecost”. Maybe the wrong place?
But now you guided me to the solution with that.
I also changed the “;” to “,” in the “skudatacomple.csv” and now the Script run´s through without any errors!
Thanks, Philip
Good catch!
Interesting that the culture fix didn’t work. I shall look into that. Might make a good article.
TR
Hi Tony,
thank you for this great script! Using V1.94
But I have troubles with the pricing.
I set the “$PricingInfoAvailable = $True” and added in the SkuDataComplete.csv 2 Collums “Price and Currency”
But then I get a lot of errors and all price information in the reports are 0,00.
——————————————————————————————————————-
Line |
540 | $LicenseCostSKU = $PricingHashTable[$Sku]
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Cannot index into a null array.
——————————————————————————————————————-
In my opinion it looks the same as in your screenshot “Figure 1”.
Any suggestions?
Thanks!
It looks as if the pricing information was not read in by the script. If it was, the $PricingHashTable would be filled with data (and it’s null in your example). Can you check the data file and make sure that information is being imported into $PricingHash?
Hi,
If I check $$PricingHash no output.
Do you mean with data file “SkuDataComplete.csv”?
There I entered the pricing information.
1st try:
I made text to collum added 2 collum’s “Price and Currecy” and added the in the price collum prices where I have on and let the rest empty or entered “0”
SkuId SkuPartNumber DisplayName Price Currency
c5928f49-12ba-48f7-ada3-0d743a3601d5 VISIOCLIENT Visio Online Plan 2 11,22 EUR
1f2f344a-700d-42c9-9427-5cea1d5d7ba6 STREAM Microsoft Stream 0
2nd try:
I added the price and and currency directly in the line because I thought maybe the script cannot read the whole information if the text is in collum´s. But same output
SkuId,”SkuPartNumber”,”DisplayName”,”Price”,”Currency”
c5928f49-12ba-48f7-ada3-0d743a3601d5,”VISIOCLIENT”,”Visio Online Plan 2″,”11,22″,”EUR”
1f2f344a-700d-42c9-9427-5cea1d5d7ba6,”STREAM”,”Microsoft Stream”
alle the same.
Also, I do not see friendly names of licenses in the final reports just SKUID in “Direct assigned”, but this is maybe as designed?
Thanks,
Aha. You’re using a comma as a separator for the price. I use a full stop
SkuId SkuPartNumber DisplayName Price Currency
6fd2c87f-b296-42f0-b197-1e91e994b900 ENTERPRISEPACK Office 365 E3 23.15 US $
Try changing the seperator to use a full stop. I’m afraid that I didn’t internationalize this script too well.
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 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.
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.
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
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
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 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?
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 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 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.
This should be very easy. Give me a couple of days and I will look at updating the script.
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/