I have been fiddling with licensing and Graph API and spent the better part of today morning trying to pull some licensing info via Graph queries. I feel it is time to put that out as a post so I can refer to it next time around.
One of my colleagues wanted to know why a lot of O365 Multi Geo licenses were suddenly assigned over the weekend. Another colleague came up with a list via the following Azure AD PowerShell snippet:
|
1 2 3 4 5 |
$filterDate = (Get-Date).AddDays(-7) Get-AzureADUser -All $true | Select-Object UserPrincipalName -ExpandProperty AssignedPlans | Where-Object { $_.CapabilityStatus -eq "Enabled" -and $_.ServicePlanID -eq "897d51f1-2cfa-4848-9b30-469149f5e68e" -and $_.AssignedTimestamp -ge $filterDate } | Select-Object UserPrincipalName -Unique |
The code gets a list of all Azure AD users, gets the AssignedPlans property for the user, expands it to select ones that are enabled and have a specific Service Plan Id (which you can find from this list; in this case he was searching for the Exchange Online service plan), filters those with a date of assignment timestamp in the past 7 days, and outputs the UPN. Neat and tidy!
I was of course curious if I can do the same in Graph. I had been working on licensing and Graph for something else the past few days so this was a topic of interest.
The answer is YES, but the journey wasn’t as straight forward as I had hoped (took me the whole morning after all) so here’s some notes on what I discovered along the way.
The Properties
First off, every user object in Graph has three license related properties (screenshot from the official docs). 
Note that all of these are not returned by default and we have to specifically ask for them.
The assignedLicenses property has a bunch of SKU Ids corresponding to the license assigned to the user, along with any disabled plans. For example:
|
1 2 3 4 5 6 |
# Note: I am selecting just the first license here to keep things readable ❯❯ Get-MgUser -UserId me@myfirm.local -Select assignedPlans,assignedLicenses | select -ExpandProperty AssignedLicenses | select -First 1 | fl * DisabledPlans : {} SkuId : c5928f49-12ba-48f7-ada3-0d743a3601d5 AdditionalProperties : {} |
The SKU Id is a standard one. In this case it is Visio. You can search this up in the plan Id list I linked to earlier. Remember: SKU Ids correspond to licenses.
The assignedPlans property is more useful. It has a list of plans, but more importantly also the timestamp of when it was assigned. You won’t get the license name from here, only the plan Id and name (along with time stamp and whether it is enabled). Example:
|
1 2 3 4 5 6 7 8 9 |
❯❯ Get-MgUser -UserId me@myfirm.local -Select assignedPlans,assignedLicenses | select -ExpandProperty AssignedPlans AssignedDateTime CapabilityStatus Service ServicePlanId ---------------- ---------------- ------- ------------- 26/03/2022 01:46:07 Enabled RMSOnline bea4c11e-220a-4e6d-8eb8-8ea15d019f90 02/11/2021 12:00:42 Enabled SharePoint 2bdbaf8f-738f-4ac7-9234-3c3ee2ce7d0f 02/11/2021 12:00:42 Enabled SharePoint da792a53-cbc0-4184-a10d-e544dd34b3c1 02/11/2021 12:00:42 Enabled LearningAppServiceInTeams b76fb638-6ba6-402a-b9f9-83d28acb3d86 02/11/2021 12:00:42 Enabled MicrosoftOffice 663a804f-1c30-4ff0-9915-9db84f0d1cea |
And lastly we have licenseAssignmentStates. This one tells you the licenses (SKU Ids) assigned to the user, the plans that are disabled per license, and more importantly the way the license is assigned (via a a group or direct). Example output:
|
1 2 3 4 5 6 7 |
❯ Get-MgUser -UserId me@myfirm.local -Select LicenseAssignmentStates | select -ExpandProperty LicenseAssignmentStates AssignedByGroup DisabledPlans Error LastUpdatedDateTime SkuId State --------------- ------------- ----- ------------------- ----- ----- ff73ba51-3f82-4272-bd87-e73ae3ad0d76 {} None 22/06/2021 19:07:03 a403ebcc-fae0-4ca2-8c8c-7a907fa6c235 Active 3ce9440c-8691-402d-8acb-db264bbf8d45 {} None 04/05/2021 19:07:47 87bbbc60-4754-4998-8c88-227dda164858 Active {} None 29/04/2021 15:12:16 05e9a617-0261-4cee-bb44-137d3ea5da65 Active |
Note that I can see the group Ids. If the group Id is empty it means the license was assigned directly.
This latter was what I had been working on recently. We wanted to find users that were assigned licenses directly or via one of our non-standard groups.
Searching
It is possible to do a Get-MgUser against a user object and then search within any of the properties above. But it is also possible to get Graph to only return user objects matching specific criteria for the above properties. It is not too flexible (which is where I got stuck at today morning) but it is a good start to return a filtered list via the Graph API which you can then filter further locally as needed.
The first thing to use is the any lambda operator. Since each of these properties are collections (i.e. not a single valued answer) when searching we have to expand the collection and search within it. The any operator does that. A screenshot from the official docs:
Interestingly, the docs have an example of using any to search against the assignedLicenses property:
|
1 |
GET https://graph.microsoft.com/v1.0/users?$filter=assignedLicenses/any(s:s/skuId eq 184efa21-98c3-4e5d-95ab-d07053a96e67) |
I learnt that it is possible to combine this with other properties for instance:
|
1 |
GET https://graph.microsoft.com/v1.0/users?filter=signInActivity/lastSignInDateTime le 2020-08-01T00:00:00Z&assignedLicenses/any(s:s/skuId eq 184efa21-98c3-4e5d-95ab-d07053a96e67) |
In my case since I want to focus on the assignment time I will have to go with assignedPlans. Before that though, rather than use hard coded SKU Ids like all the examples above, it would be good to find the Id via Graph itself. Turns out there’s a cmdlet for that: Get-MgSubscribedSku
If I want to find the SKU Id for the Multi Geo license for instance:
|
1 |
(Get-MgSubscribedSku | Where-Object { $_.SkuPartNumber -eq "OFFICE365_MULTIGEO" }).SkuId |
Or in this case since I want the plan Id for Exchange Online within Multi Geo:
|
1 2 3 |
((Get-MgSubscribedSku | Where-Object { $_.SkuPartNumber -eq "OFFICE365_MULTIGEO" }).ServicePlans | Where-Object { $_.ServicePlanName -eq "EXCHANGEONLINE_MULTIGEO" }).ServicePlanId |
Assume I have set the result of the above in a variable, say $exchangeId. I can now find all users with this plan assigned to this thus:
|
1 |
Get-MgUser -All -Filter "assignedPlans/any(x:x/ServicePlanId eq $exchangeId)" |
That didn’t work as expected, sadly. Got an error: Complex query on property assignedPlans is not supported. The reason and fix for that is in this StackOverflow post. This MS blog post referred to from StackOverflow is a good read too. The solution is to add a few additional switches as follows:
|
1 |
Get-MgUser -All -Filter "assignedPlans/any(x:x/ServicePlanId eq $exchangeId)" -ConsistencyLevel Eventual -CountVariable userCount |
Even better, pull the assignedPlans and UPN too as part of the output for use later on.
|
1 |
Get-MgUser -All -Filter "assignedPlans/any(x:x/ServicePlanId eq $exchangeId)" -ConsistencyLevel Eventual -CountVariable userCount -Property assignedPlans,UserPrincipalName,Id |
This works, so at this point I thought maybe I can filter on the assignedDateTime property too. As per the docs it is a timestamp of the following format:
Not a problem, I can generate a time stamp of that format easily:
|
1 |
Get-Date ((Get-Date).AddDays(-5)) -Format "yyyy'-'MM'-'dd'T'HH':'mm':'ssZ" |
This gives me something like “2022-06-02T14:10:49Z” as of writing. So I tried to search based on this:
|
1 |
Get-MgUser -All -Filter "assignedPlans/any(x:x/AssignedDateTime gt 2022-06-02T14:10:49Z)" -ConsistencyLevel Eventual -CountVariable userCount -Property assignedPlans,UserPrincipalName,Id |
This fails with the following error: An unsupported property was specified.
I am not sure why. My first thought was that the gt operator is not supported (remember the docs said only eq and not are supported) so I tried changing it to eq and also a timestamp that had the seconds zeroed out (just in case):
|
1 |
Get-MgUser -All -Filter "assignedPlans/any(x:x/AssignedDateTime eq 2022-06-02T00:00:00Z)" -ConsistencyLevel Eventual -CountVariable userCount -Property assignedPlans,UserPrincipalName,Id |
Same error!
I know its just the AssignedDateTime that has an issue because others like capatabilityStatus works fine. After fiddling with this for a lot of time I decided to give up and do the filtering based on time locally instead. Thus I do the following via Graph:
|
1 |
Get-MgUser -All -Filter "assignedPlans/any(x:x/ServicePlanId eq $exchangeId and capabilityStatus eq 'Enabled')" -ConsistencyLevel Eventual -CountVariable userCount -Property assignedPlans,UserPrincipalName,Id |
Note how I can search both properties within the assignedPlans collection within the any operator.
The filter clause is getting to be a mouthful so better to keep it separate.
|
1 2 |
$filterClause = "assignedPlans/any(x:x/ServicePlanId eq $exchangeId and capabilityStatus eq 'Enabled')" Get-MgUser -All -Filter $filterClause -ConsistencyLevel Eventual -CountVariable userCount -Property assignedPlans,UserPrincipalName,Id |
At this point the results of Get-MgUser are all the users with the Exchange Online Mutli-Geo plan assigned to them (and enabled). Now I need to filter these to capture ones where the assignment happened in the last 5 days.
|
1 2 3 4 5 6 7 |
$filterClause = "assignedPlans/any(x:x/ServicePlanId eq $exchangeId and capabilityStatus eq 'Enabled')" $filterDate = Get-Date.AddDays(-5) Get-MgUser -All -Filter $filterClause -ConsistencyLevel Eventual -CountVariable userCount -Property assignedPlans,UserPrincipalName,Id | Where-Object { $_.AssignedPlans.AssignedDateTime -gt $filterDate } | Select-Object -Property UserPrincipalName |
This will output a list of UPNs.
I can now build upon this if needed to show where the plan comes from… via the licenseAssignment property. I don’t need that info currently, so didn’t explore further.