Filter only licensed users when using Graph API queries

Are you sick and tired of being told that you cannot do a simple task when using the Graph API, such as filter only users that have a license applied? I shall deliver you from your misery!

For years, and years, and years, the filtering capabilities of the Graph have remained crap. Sorry MS folks, no other way of putting this. The advanced query capabilities bring some improvements, in a totally confusing fashion (having to use $count=true on every query, while returning the full set instead of just the count doesn’t really make sense to me), but at this point I’ll take whatever I can use. Even with such queries though, you can see that simple stuff, such as filtering against null values of the assignedLicenses property, is not supported.

Luckily, there seems to be a workaround. The ‘count’ operator seems to work against collections and in turn the resulting value can be compared via simple eq/ne statements. So we can take the assignedLicenses collection and apply the method to filter out any zero (or non-zero, depending on what you’re trying to achieve) values. In other words, something like this should work:$filter=assignedLicenses/$count ne 0&$count=true

Note that this still requires you to use the ConsistencyLevel = eventual header, and the $count=true operator. But it does produce the desired result, so I can live with that. I’ve used the /beta endpoint above, but the same query seems to work fine against /v1.0 too. Here’s a pimped out version that should be more useful:$filter=assignedLicenses/$count ne 0&$count=true&$select=displayName,mail,userPrincipalName,id,userType,assignedLicenses&$top=999

And here’s the whole thing packaged in a PowerShell code snippet:

$authHeader = @{'Authorization'="Bearer $token"}

$users = @()
$uri = "`$filter=assignedLicenses/`$count ne 0&`$count=true&`$select=displayName,mail,userPrincipalName,id,userType,assignedLicenses&`$top=999"

#needs ConsistencyLevel = eventual
$authHeader["ContentType"] = "application/x-www-form-urlencoded"
$authHeader["ConsistencyLevel"] = "eventual"

do {
$result = Invoke-WebRequest -Uri $uri -Verbose:$VerbosePreference -ErrorAction Stop -Headers $authHeader
$uri = $($result | ConvertFrom-Json).'@odata.nextLink'
#If we are getting multiple pages, best add some delay to avoid throttling
Start-Sleep -Milliseconds 500
$users += ($result | ConvertFrom-Json).Value
} while ($uri)

where you’ve already used your preferred method for obtaining an access token and stored it in the $token variable. The result of the query matches the number of entries returned from good old Get-MsolUser, so there you go. And in case you care about unlicensed users only, change the “$count ne 0” statement to “$count eq 0“.

5 thoughts on “Filter only licensed users when using Graph API queries

  1. Egor Emeliyanov says:

    This is brilliant, with just few minor performance suggestions:

    1) Use Invoke-RestMethod instead, thus you save one JSON conversion on each page iteration.
    2) You Start-Sleep even when getting the first page, which makes no sense if there’s only one page. Worth adding $pass = 0 counter, increment it inside do…while and start-sleep only on non-zero value
    3) $users += $somearray is an antipattern. If the pages are large enough, you’re wasting a lot of memory as PS creates a second array, then copies items to it, so your total memory usage is your data size * 2. Instead, use $users = New-Object System.Collections.Generic.List[System.Object], then $users.Add($newData).

    Thanks for the write up!

    1. Eric M says:

      On your point 3) I’ll do you one better (simpler). Just assign the output of the do/while to $users and return the output of $results in the script block. So, $users = do { ($result | ConvertFrom-Json).Value }. Same performant result of creating the list object and adding to it, but doesn’t require creating the list object or adding to it within the script block.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.