Information for Microsoft Licenses in tenant

I was recently working with a client that had no idea on their licensing landscape. While the license portals in Entra or 365 admin center are fine I couldn't quite find everything they wanted in a single pane/export.

MSLicenseAssignmentMethod.ps1 is where you can find the code in full. In case the comments aren't that helpful I'll break things down.

This connects via the MGGraph module, using the following scopes User.Read.All, Group.Read.All, Directory.Read.All

There are two variables that reference locally $csvPath and $filename. I've defaulted them to C:\temp but if that's illegal you'll need to modify them.

The first part deals with downloading the latest license and service plan reference from Microsoft, because for the life of me I couldn't find another way to get the readable names that are displayed in the licensing centers.

# Mapping for SKU names, bc naturally it's not the same as the license display name https://learn.microsoft.com/en-us/entra/identity/users/licensing-service-plan-reference

# Define the path to the CSV file
$csvPath = "C:\temp\licensenames.csv"
# Define the URL, if this is failing to download reference the above learn page for the correct URL
$url = "https://download.microsoft.com/download/e/3/e/e3e9faf2-f28b-490a-9ada-c6089a1fc5b0/Product%20names%20and%20service%20plan%20identifiers%20for%20licensing.csv"
# Check if the CSV file already exists
if (-Not (Test-Path -Path $csvPath)) {
    # Download the CSV file from the URL
    try {
        Invoke-WebRequest -Uri $url -OutFile $csvPath
    }
    catch {
        Write-Host "Failed to download the CSV file from $url"
        exit
    }
}
# Read the CSV file into a variable
$licenseNameCSV = Import-Csv -Path $csvPath
$skuToLicenseName = @{}

# Iterate through each row
foreach ($row in $licenseNameCSV) {
    # Use Column B as the key and Column A as the value
    $csvSkuID = $row.String_Id
    $LicenseName = $row.Product_Display_Name
    $skuToLicenseName[$csvSkuID] = $LicenseName
}

Then, I built the $skuMapList to include every license in the tenant with Get-MgSubscribedSku with the help of the csv translation of the previous part from SkuPartNumber to the actual License Name. I finish populating the array with Get-MgGroup -Filter 'assignedLicenses/$count ne 0' -ConsistencyLevel eventual -CountVariable licensedGroupCount -All -Select Id,DisplayName,AssignedLicenses to only grab the groups that have a license assigned to them.

Following that is some logic to run through each license sku I found, attaching appropriate groups that are assigning them and ending with a Get-MgGroupMember query to grab the membership of those groups. I also logic that if for some reason there's a previous/future use-to-license-group with no members that I mark Not-In-Use as the membership.

# Retrieve all subscribed SKUs and create an array of SkuId, SkuPartNumber, and SkuLicenseName, with empty fields for GroupId, GroupDisplayName, and Members
$skuMapList = foreach ($sku in (Get-MgSubscribedSku)) {
    [PSCustomObject]@{
        SkuId = $sku.SkuId
        SkuDisplayName = $sku.SkuPartNumber
        SkuLicenseName = $skuToLicenseName[$sku.SkuPartNumber]
        GroupId = ""
        GroupDisplayName = ""
        Members = @()
    }
}

# Gather groups with licenses assigned
$licensedGroupList = Get-MgGroup -Filter 'assignedLicenses/$count ne 0' -ConsistencyLevel eventual -CountVariable licensedGroupCount -All -Select Id,DisplayName,AssignedLicenses

# Extend the mapping to include the group ID, display name, and members
foreach ($group in $licensedGroupList) {
    foreach ($assignedLicense in $group.AssignedLicenses) {
        foreach ($skuMap in ($skuMapList | Where-Object SkuID -eq $assignedLicense.SkuId)) {
            $skuMap.GroupId = $group.Id
            $skuMap.GroupDisplayName = $group.DisplayName            
            $members = (Get-MgGroupMember -GroupID $group.Id -All).id
            if ($null -eq $members -or $members.Count -eq 0) {
                $skuMap.Members = "Not-In-Use"
            } else {
                $skuMap.Members = $members
            }
        }
    }
}

Next is the meat and potatoes in my foreach cursed hellscape. Grabbing all users that have a license assigned with $licensedUsersList = Get-MgUser -Filter 'assignedLicenses/$count ne 0' -ConsistencyLevel eventual -CountVariable licensedUserCount -All -Select Id,UserPrincipalName,DisplayName,AssignedLicenses The next bit then drills down each user, drill further each to each license, and begin checking against the $skuMapList previously made. If license+group matches, it's marked as inherited, if not then it's marked as directly assigned and for both properties are assigned to variables used to export the data.

# Retrieve licensed users
$licensedUsersList = Get-MgUser -Filter 'assignedLicenses/$count ne 0' -ConsistencyLevel eventual -CountVariable licensedUserCount -All -Select Id,UserPrincipalName,DisplayName,AssignedLicenses

# Find users with SKUs not part of the corresponding groups
$usersNotInherited = [System.Collections.Generic.List[Object]]::new()
$inheritedSkus = [System.Collections.Generic.List[Object]]::new()
foreach ($user in $licensedUsersList) {
    foreach ($assignedLicense in $user.AssignedLicenses) {
        foreach ($skuMap in ($skuMapList | Where-Object SkuID -eq $assignedLicense.SkuId)) {
            $isInGroup = $false
            foreach ($groupId in $skuMap.GroupId) {
                $groupMemberList = $skuMap.Members
                foreach ($groupMember in $groupMemberList) {
                    #If this is assigned via group membership, populate the inheritedSkus array for use in Excel worksheet tab
                    if ($groupMember -eq $user.Id) {
                        $isInGroup = $true
                        $inheritedObj = [PSCustomObject]@{
                            SkuID = $skuMap.SkuID
                            GroupID = $skuMap.GroupId
                            GroupDisplayName = $skuMap.GroupDisplayName
                            UserID = $user.ID
                            UserPrincipalName = $user.UserPrincipalName
                            UserDisplayName = $user.DisplayName
                        }
                        $inheritedSkus.Add($inheritedObj)
                        break
                    }
                }
                # If the user is not part of any group assignation for that sku, add them to the list
                if (-not $isInGroup) {
                    $usersWithSkuNotInGroupObj = [PSCustomObject]@{
                        SkuID = $skuMap.SkuId
                        SkuPartName = $skuMap.SkuDisplayName
                        SkuLicenseName = $skuMap.SkuLicenseName
                        UserID = $user.ID
                        UserPrincipalName = $user.UserPrincipalName
                        UserDisplayName = $user.DisplayName
                    }
                    $usersNotInherited.Add($usersWithSkuNotInGroupObj)
                }
            }
        }
    }
}

Finally, start building the excel workbook. Making the first sheet show everyone who has a license assigned to them directly. Then making additional worksheets with the name of each license that has a group assigning it, with the membership and which group is doing the assigning. There's a character limit on the tabs but everything is still readable.

# Get the current date and time in the format MM-DD-HH-MM
$timestamp = (Get-Date).ToString("MM-dd-HH-mm")
# Construct the filename with the timestamp
$filename = "C:\temp\MicrosoftLicenses_$timestamp.xlsx"
# Export the users who have the SKU but are not part of the corresponding groups
$usersNotInherited | Export-Excel -Path $filename -WorkSheetname 'DirectAssigned' -AutoSize -AutoFilter
# Add a new sheet for each SKU and export the data
foreach ($subscribedSku in $skuMapList) {
    # We only want tabs for SKUs that are assigned via a group
    if ($subscribedSku.GroupID -ne "" -and $subscribedSku.Members -ne "Not-In-Use") {
        # We don't want empty tabs if a group is assigned and not populated
        # We want to trust that MS updates their CSV when adding a new product, but just in case we'll use the skupartname if the license name is null
        if ( $null -eq $subscribedSku.SkuLicenseName ) {
            $sheetName = $subscribedSku.SkuDisplayName
        } else {
            $sheetName = $subscribedSku.SkuLicenseName
        }
        $users = $inheritedSkus | Where-Object { $_.SkuID -eq $subscribedSku.SkuId }
        $users | Select-Object -Property * -ExcludeProperty SkuId | Export-Excel -Path $filename -WorkSheetname $sheetName -AutoSize -AutoFilter
    }
}

Disconnect-MgGraph

As a side note, when I added that Disconnect-MgGraph for this, Copilot for Github set my hopes up by suggesting I complete it with -NoGoodbye and I can't tell you how hurt I was that it didn't exist.

Now if you go to wherever you set your export path you'll find a MicrosoftLicenses xlsx which should look similar to below, except with many tabs and real licenses/people not like the developer sandbox I have.

Hope this was useful. If not and you've found a better way please let me know!