Practical Graph: Including Costs in a Microsoft 365 Licensing Report

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.