> ## Content Index > Fetch the complete content index at: https://www.techbyjeff.net/llms.txt > Use this file to discover other available public pages before exploring further. # Information for Microsoft Licenses in tenant - URL: https://www.techbyjeff.net/information-for-microsoft-licenses-in-tenant/ - Published: 2024-09-06T18:00:38.000Z - Updated: 2026-08-19T21:41:37.000Z - Description: Using mggraph for powershell, find all licenses in use and which users have which license. Detailing whether they are directly assigned or inherited via a group. - Author: Jeffrey Stuhr - Tags: Microsoft 365, Entra ID, PowerShell 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](https://github.com/fadwen/TechbyJeff/blob/main/Powershell/Entra/MSLicenseAssignmentMethod.ps1?ref=techbyjeff.net) 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. ![](https://storage.ghost.io/c/cb/cc/cbcc23a2-e533-4fde-820e-0d1cbb8edc67/content/images/2024/09/2024-09-03_20-42-21.jpg) exported result of the script, showing direct assigned and assigned via group Hope this was useful. If not and you've found a better way please let me know! ## Related reading - [Why Every IT Administrator Needs This Intune Browser Extension Policy Analyzer](https://www.techbyjeff.net/why-every-it-administrator-needs-this-intune-browser-extension-policy-analyzer/) - [What You Should Know About Managing OneDrive in 2025](https://www.techbyjeff.net/what-you-should-know-about-managing-onedrive-in-2025/) - [Migrating to Microsoft 365](https://www.techbyjeff.net/migrating-to-microsoft-365/)