Reporting the Number of Assigned Licenses and How Many Licenses Remain
A reader question for the article about reporting group-based license assignments asked if it is possible to get a list of groups with pending license assignments. Essentially, the request is to create an overview of group-based licensing so that new licenses can be ordered when necessary.
The Microsoft 365 admin center doesn’t include such a report, but information about the groups used for license assignments is available through the Microsoft Graph, so it should be possible to build a script to deliver the desired overview. A basic version of what the reader requested is described in this article, but let’s see if we can do better.
Finding Groups Used for License Assignments
The first issue is to find the groups used for license assignments. This command does the trick by filtering the assignedLicenses property to find any group with any assigned license. Normally, groups don’t have licenses, so the presence of a license is indicative that a group is used for license assignments. In this instance, the command looks for groups with a count of licenses greater than zero. In Entra ID terms, this is a complex query, so the command specifies that the consistency level is eventual (in effect, redirect the query to a data store used to service these kind of transactions):
[array]$Groups = Get-MgGroup -All -Filter "assignedLicenses/`$count ne 0" -ConsistencyLevel Eventual -CountVariable Count -Property Id, DisplayName, AssignedLicenses, LicenseProcessingState
If you want to find a group used to assist a specific license, you could use a slightly different command. For instance, this command finds the group(s) that assign the Teams EEA license to user accounts. It’s not a complex query because the filter looks for matches against the SkuId sub-property of assignedLicenses instead of counting licenses:
Get-MgGroup -filter "assignedLicenses/any(s:s/skuId eq 7e74bd05-2c47-404e-829a-ba95c66fe8e5)" -All | Format-Table Id, DisplayName Id DisplayName -- ----------- 90bbfa24-4413-4ffe-8e0a-2c7f10cea873 Teams EEA License Holders
Dealing with Product Identifiers
The SkuId referenced above is a GUID called the product identifier. Microsoft documents the set of product identifiers and refreshes the data monthly. You can download a CSV file containing this information from the page or download the information directly to use within a script. In this example, the script downloads the CSV file and imports it into an array. The array is then sorted to make sure that the product identifiers are unique before being loaded into a hash table that can be used to resolve product identifiers into human-readable names:
Write-Host "Attempting to download Microsoft licensing data..."
[array]$ProductData = Invoke-RestMethod -Method Get -Uri "https://download.microsoft.com/download/e/3/e/e3e9faf2-f28b-490a-9ada-c6089a1fc5b0/Product%20names%20and%20service%20plan%20identifiers%20for%20licensing.csv" | ConvertFrom-CSV
If ($ProductData) {
[array]$ProductInfo = $ProductData | Sort-Object GUID -Unique
$ProductInfoHash = @{}
ForEach ($P in $ProductInfo) {
$ProductInfoHash.Add([string]$P.GUID, [string]$P.Product_Display_Name)
}
} Else {
# if a local copy exists, you could plug it in here
Write-Host "Unable to retrieve product data"
Break
}
For example, if we resolve the product identifier used in the groups filter above, the result is a product name of “Microsoft Teams EEA.”
$ProductInfoHash['7e74bd05-2c47-404e-829a-ba95c66fe8e5'] Microsoft Teams EEA
The Microsoft 365 licensing report script uses a variation of this technique to translate product identifiers into names. Before running the script, an administrator downloads the file and amends the product names to make them more understandable. They can also add the cost for each license to allow the script to compute the costs of licenses assigned to users and departments.
Fetching Subscription Data
Before reporting anything, we need to know what subscriptions exist within the tenant. A subscription is a paid, free, or trial acquisition of a product. The subscription data tells us how many licenses are available for a product and how many are used (assigned). The difference between the two is the number of available licenses. The Get-MgSubscribedSku cmdlet fetches the subscription data:
[array]$TenantSkus = Get-MgSubscribedSku -All | Select-Object SkuId, SkuPartNumber, ServicePlans, ConsumedUnits, PrepaidUnits
Reporting Information
With all the necessary information gathered, we can create the foundation for our report. The next step is to create two lists. One holds details of the licenses assigned by groups, such as the number of assigned licenses. The other holds information about the groups, including the group members, and license processing state (like “ProcessingComplete”, meaning that the group has finished processing all license assignments).
After processing the groups, the script checks users for license assignment errors. When the background job that processes requests for group-based assignments attempts to assign a license, it can encounter problems.
Write-Host "Checking for user accounts with license assignment errors..."
[array]$UsersWithErrors = Get-MgUser -All -PageSize 500 -Property AssignedLicenses, LicenseAssignmentStates, DisplayName | Select-Object DisplayName, AssignedLicenses -ExpandProperty LicenseAssignmentStates | Select-Object DisplayName, AssignedByGroup, State, Error, SkuId | Where-Object {$_.Error -ne 'None'}
# Remove errors that aren’t associated with group-based licensing
$UsersWithErrors = $UsersWithErrors | Where-Object {$_.AssignedByGroup -ne $null}
If any users with license assignment errors are found, their details are noted. This example is for a user where a license could not be assigned because none were available (the most common problem).
DisplayName : Ben James (BusDev) AssignedByGroup : 90bbfa24-4413-4ffe-8e0a-2c7f10cea873 State : Error Error : CountViolation SkuId : 7e74bd05-2c47-404e-829a-ba95c66fe8e5
Microsoft publishes a page of example PowerShell commands for use with group-based licensing. You might get some ideas there.
Spreading the News
The last step is to share the information acquired by the script. A variety of methods exist to do this including creating a document in a SharePoint site, populating data in a SharePoint list, posting a message in a Teams channel, or by simply sending email. I went with the last option and used the Send-MgUserMail cmdlet to send the message. As written, the script sends email from the signed-in user’s mailbox. If you want to use a different mailbox, you’ll need to run the script in app-only mode (or in an Azure Automation runbook) after granting consent for the required application permissions:
- Mail.Send: Send email (consider using RBAC for Applications to restrict access to specific mailboxes).
- Group.Read.All: Read group information.
- GroupMember.Read.All: Read member information for groups, like the display name of group members.
- User.Read.All: Read user information, including license data.
The first part of the message contains details of each of the groups used for group-based licensing including the name of the group used for assignments, the accounts who are members of the group, and the number of prepaid, assigned, and available licenses. If any error conditions are detected, they’re highlighted in warning messages.
The end of the message contains a section describing accounts with license assignment errors (Figure 2).
The full script is available from GitHub.
Another Aspect of License Management for Administrators
Making sure that group-based license assignments remain healthy is a good idea, especially if the job of adding user accounts to groups is delegated and the people who maintain the groups might not be aware of the licensing situation. Like anything to do with licensing in a Microsoft 365 tenant, attention to detail saves money, and that’s always a good thing.
