Microsoft.Graph Entra ID user list with licenses using Get-MgUser

PowerShell Help
1

In the Active user list (Active users - Microsoft 365 admin center) you can also find a column Licenses. This gives all licenses assigned to the user like this way: Microsoft 365 E3,PowerBiPro,Defender for Endpoint P1. Currently I have this code to get a list of the different properties, and this works fine. But I miss the object “Licenses”.

$UserLastEntraIDLoginData = Get-MgUser -All -Property 'AccountEnabled','City','Country','Department','DisplayName','JobTitle','UserPrincipalName','CreatedDateTime','SignInActivity' `
  | Select-Object @{N='AccountEnabled';E={$_.AccountEnabled}}, `
                  @{N='City';E={$_.City}}, `
                  @{N='Country';E={$_.Country}}, 
                  @{N='Department';E={$_.Department}}, 
                  @{N='DisplayName';E={$_.DisplayName }}, `
                  @{N='JobTitle';E={$_.JobTitle }}, `
                  @{N='UserPrincipalName';E={$_.UserPrincipalName}}, `
                  @{N='CreatedDateTime';E={$_.CreatedDateTime}}, `
                  @{N='LastInteractiveSignInDate';E={$_.SignInActivity.LastSignInDateTime}}, `
                  @{N='LastNonInteractiveSignInDate';E={$_.SignInActivity.LastNonInteractiveSignInDateTime}}

$UserLastEntraIDLoginData | Export-Csv -Path $env:USERPROFILE\users.csv -NoTypeInformation

I found this Get statement:
Get-MgUserLicenseDetail

But with MGUserLicenseDetail, you get the Identifier and not as you get from the active user list.

Id                                          SkuId                                SkuPartNumber
--                                          -----                                -------------
gGD2lRZB1E-ucAiX8wQDO66cK8RP6rdKlxeBV2I1zKw c42b9cae-ea4f-4ab7-9717-81576235ccac DEVELOPERPACK_E5

This is what I want to get extracted from the user table:

image
image852×430 17.5 KB

Any suggestions?

2

Graph is kind of a beast. There’s a concept of relationship properties, and while I can’t find the exact github, in some cases, relationship properties are on certain objects, but are always null, even if you ask for them.

Get-MgUser has an ‘assignedlicenses’ property you could add to your original call. That means you wouldn’t need the additional lookup for Get-MGUserLicenseDetail, unless you wanted those details. That doesn’t give you the pretty names you’re looking for though. There’s a translation table that MS provides that can help you translate though on the fly. Here’s a medium article that talks about it: Translate Microsoft 365 License GUIDs to Product Names in PowerShell | by Martin Heusser | M365 Apps & Services MVP | Medium. The ‘GUID’ is likely what you’re looking for on that. You could then maybe use that to translate using a calculated property.

So I might do something like this based on that article and getting the translation table for just a single account (my $UserLastEntraIDLoginData only has my single account in it as an output from Get-MgUser with the additional assignedLicenses property).

$UserLastEntraIDLoginData | Select-Object @{Name ='License'; Expression = {$translationtable | Where-Object {$_.GUID -eq $UserLastEntraIDLoginData.AssignedLicenses.SkuId[-1]} | Select-Object -ExpandProperty Product_Display_Name -First 1}

which output:

License
-------
Microsoft 365 A5 for Faculty

Not you might have to loop through if someone has multiple licenses and adjust as needed. I just grabbed the last licnese I had, which happened to be A5 faculty license. Since the translation table shows specific product offerings (called service plans) , you’ll get multiple hits for a license, so the way I dealt with it, is just doing the Select-Object -First 1, as the license shouldn’t change, since its under the same SKUID/GUID. Another quick note is that i often use UPN for Get-MGUser, but if you get certain properties, it’ll error out, and you need to pass it the actual ID of the user.

Hopefully that at least gives you a place to start, if it’s not exactly what you’re looking for.

A couple other notes:

  • Get rid of thoes backtics :slight_smile:
  • I don’t think some of your calculated properties are needed: (E.G. City, AccountEnabled, etc.?) The only time you should have to do that is when actually ‘creating’ or translating a property on the fly. I’d remove those where necessary.
1 Like
3

Add this to your Select-Object command

@{N='License';E={(Get-MgUserLicenseDetail -All -UserId $_.id).SkuPartNumber -join ';'}}

That will look up the licenses for that user, extract the friendly name, and join them with a semicolon for those with multiple licenses assigned. This allows easy export to CSV/Excel. If you wanted to keep it as an array for use with JSON for example, just remove the -join ';' portion

1 Like
4

The SkuPartNumber still isn’t quite the friendly name, it’s like a modified version of it. Seemed like OP wanted the ‘Full display name’ he finds in the admin center. That said, skupartnumber is what my team typically uses, as it’s enough info to know what license it is. If he does use it, I’d probably remove the -All from the LicenseDetail call. Don’t think it hurts anything being there, but it’s not necessary either. The littel translation table that article i posted mentioned is pretty nifty though, I didn’t know that existed until I googled around today.

2 Likes
5

You are correct, this sku part number was sufficient for IT. You could use a lookup table with the guid/skupartnumber which is what I do for other situations. I have code the builds a hash table based on the information here

While I’m not able to share the code that creates the lookup table, I am happy to share a simplified version of the table, as it is today 1/24/2024

OP you can incorporate it into your code like this.

$producttable = Invoke-RestMethod 'https://gist.githubusercontent.com/krzydoug/84aa5af47335c219f42a820c84c1371d/raw/13de15e8768adb73595ce226259af96510906337/Product%2520Table.psd1' | Invoke-Expression

$UserLastEntraIDLoginData = Get-MgUser -All -Property 'ID','AccountEnabled','City','Country','Department','DisplayName','JobTitle','UserPrincipalName','CreatedDateTime','SignInActivity' `
  | Select-Object @{N='AccountEnabled';E={$_.AccountEnabled}}, `
                  @{N='City';E={$_.City}}, `
                  @{N='Country';E={$_.Country}}, 
                  @{N='Department';E={$_.Department}}, 
                  @{N='DisplayName';E={$_.DisplayName }}, `
                  @{N='JobTitle';E={$_.JobTitle }}, `
                  @{N='UserPrincipalName';E={$_.UserPrincipalName}}, `
                  @{N='CreatedDateTime';E={$_.CreatedDateTime}}, `
                  @{N='LastInteractiveSignInDate';E={$_.SignInActivity.LastSignInDateTime}}, `
                  @{N='LastNonInteractiveSignInDate';E={$_.SignInActivity.LastNonInteractiveSignInDateTime}},
                  @{N='License';E={$producttable[(Get-MgUserLicenseDetail -UserId $_.id).skuid] -join '; '}}

$UserLastEntraIDLoginData | Export-Csv -Path $env:USERPROFILE\users.csv -NoTypeInformation
3 Likes
6

I’m guessing you didn’t see my first post haha :smiley: The article I posted had a way to pull the data from MS:

$translationTable = 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

Assuming that data stays up to date, it’s a nifty way to do it without the need to maintain your own table, which is annoying cuz of how often they add/remove skus from their offerings. Thanks for sharing that gist though, I might see if there’s a place we can use it in some of our reporting, some of our systems are super locked down and a static table could be useful potentially.

1 Like
7

I didn’t read it all the way, no. I see now they’ve added a csv of the table I parse. It was not available back when I wrote the parsing code. Glad to see they did this, as parsing web pages is fragile. Good info all around in this thread!

2 Likes
8

Thanks @dotnVo for your support. Also @krzydoug :grinning:
@dotnVo , I have now following code to first get the AssignedLicenses.

$UserLastEntraIDLoginData = Get-MgUser -All -Property 'AccountEnabled','City','Country','Department','DisplayName','JobTitle','UserPrincipalName','CreatedDateTime','SignInActivity', 'AssignedLicenses'
  | Select-Object @{N='AccountEnabled';E={$_.AccountEnabled}},
                  @{N='City';E={$_.City}},
                  @{N='Country';E={$_.Country}},
                  @{N='Department';E={$_.Department}},
                  @{N='DisplayName';E={$_.DisplayName }},
                  @{N='JobTitle';E={$_.JobTitle }},
                  @{N='UserPrincipalName';E={$_.UserPrincipalName}},
                  @{N='CreatedDateTime';E={$_.CreatedDateTime}},
                  @{N='LastInteractiveSignInDate';E={$_.SignInActivity.LastSignInDateTime}},
                  @{N='LastNonInteractiveSignInDate';E={$_.SignInActivity.LastNonInteractiveSignInDateTime}},
                  @{N='License';E={$_.AssignedLicenses}}

But the output is as follows:

image
image895×468 6.27 KB

So, for eachlicense he find, I got this output:

Microsoft.Graph.PowerShell.Models.MicrosoftGraphAssignedLicense

I found an article that the API needs a $select in order to get the AsssignedLicense info. But I can’t get it done to merge it in my current script. Help :grinning:

https://github.com/microsoftgraph/msgraph-sdk-powershell/issues/547#issuecomment-776263579

9

Assigned Licenses is an object, with more properties, and it’s also an array. What information do you want from it? Probably the SkuID? You can’t really represent a object with multiple properties, and what happens in you export to CSV is whatever your sending in the pipeline essentially is casted to a string, so if you took a property and did a ‘ToString’ method on it, you’d get the object name like your seeing in your output.
Also I think you wanted the friendly names as which would mean another call and another property. The other complication you are going to have is folks can have more than one license, so how are you going to line up the SKUID with the proper ‘friendly’ name if they do have multiple licenses that you want to track? With PS it’s easy, but you’ll have to work around that in a CSV file. Note my example below is for one user, not ‘all’ users my environment is too big :smiley:

$Properties = @('AccountEnabled','City','Country','Department','DisplayName','JobTitle','UserPrincipalName','CreatedDateTime',
                    'SignInActivity', 'AssignedLicenses')
                  $UserLastEntraIDLoginData = Get-MgUser -UserID $ID -Property $Properties
                  $UserLastEntraIDLoginData | Select-Object AccountEnabled, 
                  City, 
                  Country, 
                  Department, 
                  DisplayName, 
                  JobTitle, 
                  UserPrincipalName,
                  CreatedDateTime,
                  @{N='LastInteractiveSignInDate';E={$_.SignInActivity.LastSignInDateTime}},
                  @{N='LastNonInteractiveSignInDate';E={$_.SignInActivity.LastNonInteractiveSignInDateTime}},
                  @{N='License';E={$($_.AssignedLicenses.SkuId)}} | Export-CSV .\Desktop\test.csv

This will punt out the SKUid. in that field. However, if you need to translate it you’ll want to do what i mentioned in my first post. You’ll likely have to figure out what licenses you care about and represent those in a CSV n however you see fit. As mentioned it’s hard to represent in an array of licenses, but maybe that’s ok for you use case. Maybe you just need to translate them, into ‘friendly’ strings and have them represented as concatenated display name strings in your license column, as it’s just a report. just depends on how you plan on using the data. Sometimes we use logic and add columns for each of the licenses we want to check and just have a true or false for the license type, for example, because we may use that data in another process, or we just keep it in PS object for use later.