Report on partial license assignments via the Graph SDK for PowerShell

The topic “how much we pay for Microsoft 365” is always a relevant one, especially when Microsoft tries some new tactics to syphon additional $$$ from customers (such as the recent MDO-related “true ups”). There are many things to consider once you start dealing with licensing costs, such as inactive users, users with “duplicate” licenses or even overprovisioned licenses.

Licenses, and service plans in particular, are also used for controlling access to a given app. For example if you want to stop a given user from accessing the Bookings app, you can disable the corresponding service plan. Exchange Online mailboxes are generally provisioned by assigning a matching license. For other services, Microsoft might not enforce licensing requirement or this is independent of provisioning. Regardless of whether you have all services for specific SKU enabled however, you are still paying the full price.

While there are scenarios where you do want to have a given service plan disabled, to minimize licensing costs you should be periodically reviewing such assignments and potentially replacing them with more appropriate (standalone) licenses, where possible. This is nothing new, and if fact most reporting solutions and scripts out there already cover such scenarios, though they often do so by leveraging the usage reports and in some cases the audit logs. Nothing wrong with that, but let us explore what can we cover by leveraging only the Entra bits, and in the fastest way possible.

Now, there are several methods and properties on Entra side that cover licensing. The AssignedLicenses property lists all SKUs assigned to the user, along with any disabled service plans. The AssignedPlans blob gives you the list of service plans and their status, but Microsoft decided to include historical data therein, thus the set of service plan values you get might not be representative of the current set of SKUs assigned to the user. Here’s an example using my own account:

Get-MgUser -UserId vasil@michev.info -Property AssignedPlans | select -ExpandProperty AssignedPlans

AssignedDateTime  CapabilityStatus Service                                 ServicePlanId
----------------  ---------------- -------                                 -------------
08/12/21 14:13:03 Deleted          SharePoint                              c7699d2e-19aa-44de-8edf-1736da088ca1
08/12/21 14:13:03 Deleted          SharePoint                              13696edf-5a08-49f6-8134-03083ed8ba30
14/08/19 06:57:22 Enabled          DynamicsNAV                             39b5c996-467e-4e60-bd62-46066f572726
...

The first entry in the output above corresponds to the SharePoint Online Plan 1 service plan (SHAREPOINTSTANDARD). A license that contains said plan is not currently assigned to my user account, so if we were to rely solely on the values returned by the AssignedPlans property, we would be reporting incorrect data. On the other hand, this historical snapshot allows us to get some idea of which services were enabled on the account previously, without having to tap into the audit logs.

The Get-MgUserLicenseDetail cmdlet is a better alternative for reporting on license assignments, as it returns only service plans that belong to the currently assigned SKUs, along with the service plan name. Sadly, it has some downsides, too. Most importantly, you cannot fetch said data for all your users via single query and you have to query it for each user individually instead. So while this method is the best one to use, it just doesn’t cut it for the purposes of this scenario.

Long story short, for our purpose it is sufficient to fetch AssignedLicenses and perform the checks client-side. We can also be a bit more thorough and include the AssignedPlans blob, mainly to cover service plans with status other than Enabled. A single cmdlet is sufficient to get us all this data, and we can further optimize the query by using a filter to only return users with at least one SKU assigned. As a best practice, we can also select just the properties we are interested in even though it makes a little difference in this case. Here’s an example cmdlet:

Get-MgUser -Filter 'assignedLicenses/$count ne 0' -ConsistencyLevel eventual -CountVariable licensedUserCount <span class="pl-k">-</span>PageSize <span class="pl-c1 pl-token">999 </span>-All -Property UserPrincipalName,DisplayName,AssignedLicenses,AssignedPlans | Select-Object -Property UserPrincipalName,DisplayName,AssignedLicenses,AssignedPlans

The filter we are using above is considered an advanced query, so we include the -ConsistencyLevel and the -CountVariable parameters. As the Graph SDK takes care of pagination and throttling, this single cmdlet should be sufficient for our needs.

As the goal is to check how many “underprovisioned” license assignments we have, we can define some criteria that will help us in the process. For example, we might want to exclude any SKUs that are free to use, such as the TEAMS_FREE SKU for example (with SKUid of 16ddbbfc-09ea-4de2-b1d7-312db6112d70). On the other hand, we want to make sure that “critical”  service plans are enabled across all relevant SKUs. For example, you do not want to have assignments of the Microsoft 365 Apps for Business SKU for which the OFFICE 365 BUSINESS (094e7854-93fc-4d55-b2c0-3ab5369ebdc1) service plan is in disabled state.

Of course, the definition of “critical” service plan will likely vary from organization to organization. Best thing to do here is to review the list of supported SKUs and service plans and figure out which are the ones most relevant to you. As mentioned in the beginning, you should also consider the aspect of controlling access to specific services and functionalities. While you’re at it, preparing a list of “free” SKUs relevant to your organization is also something the linked document can help with.

The last building block we need is a function that incorporates all the logic used for license checks. Once we fetch the data for all our users, we will pass it to said function to parse the licensing details and return a simple $true/$false value, depending on whether the license is “fully provisioned” or not. We can then output the data in a convenient format.

Without further ado, get your copy of the script from my GitHub repo. Before running it, make sure to review the set of SKUs to exclude (aka the “free” ones, or ones you don’t care about) and the plans the script will look at, all found in the “Variables” region at the top. Make any amendments as needed, then run the script with a user with sufficient permissions to read user’s and company license data. If prompted, you need to consent to the User.Read.All and LicenseAssignment.Read.All scopes. If needed, update the connectivity cmdlet to use your preferred method instead, for example a Managed Identity for automated execution.

There are no parameters that you need to provide, so running the script is as simple as:

.\GraphSDK_Partial_SKU_report.ps1

Should you need to make any changes to the “license detection” logic the script uses, the bulk of it can be found as part of the CheckFullLicense helper function. I’ve covered most relevant details already, but here are few more you might want to know about. We deliberately filter out any service plans that do not correspond to the currently assigned set of licenses. Those will still be visible in the output via the Assigned Plans column but are not used in determining whether the license is assigned in full. The -eq operator is used for checking the status of a given service plan ($plan.CapabilityStatus -eq “Enabled”), as we want to highlight any scenario where said status does not match the Enabled value.

We track the status of each license via the $FullLicense variable, which is set to $false by default. Thus, the CheckFullLicense function will only ever return a $true value if all the service plans we check against are in the Enabled state, which is why it’s  important to make sure you review and amend the corresponding variables. In the context of the script, “full” license means a SKU for which all of the specified service plans are in the Enabled state, not necessarily a SKU for which all service plans are in the Enabled state.

Here is how the script’s output looks like. The CSV file will only feature the users with partial license assignments, as those are the ones you want to focus on. A semi-colon separated list is used for both the AssignedLicenses and AssignedPlans data columns, as both can have multiple values. For the latter, one might also want to be able to quickly see the status, which will be prefixed in front of each service plan entry, like shown below:

[Enabled]4495894f-534f-41ca-9d3b-0ebf1220a423;[Enabled]4a82b400-a79f-41a4-b4e2-e94f5787b113;[Deleted]57ff2da0-773e-42df-b2af-ffb7a2317929

A more comprehensive HTML output is also generated. Therein, three tables give us the set of users with only free licenses, those with paid licenses that are “fully” assigned, and those with partial license assignments. The latter scenario is what you want to focus on, thus we expose some additional details in that table, along with properly sorting the set of plans available. The screenshot below illustrates the HTML output:

Again, we rely solely in the AssignedLicenses data to determine whether the license is fully provisioned and we only take into consideration the service plans specified in the script configuration. Listing the assigned plans in the HTML output allows us to quickly perform additional client-side filtering, if needed. And, as mentioned above, it also allows us to get some historical plan assignment, which might or might not be relevant to your scenario.

And there you have it, a simple script that can be used to report on “partial” license assignments in your organization, that is licenses for which you are paying the full price, but might have some “important” services disabled. Obviously the value to extract here will greatly vary from tenant to tenant, but I though I’d share this somewhat interesting work-related exercise with the general public 🙂

To be clear, this is not a full licensing report, neither a report that will address “inactive” licenses. If you are looking for such, try this script by Tony.