Today i learned that querying the Microsoft Graph for the all users with manager reference is slow

What should be a fairly straight forward select is not straight forward. Talking to the product group, this is on the horizon, but right now, this must be done through slow means.

Start by using the follow PowerShell code to get an access token (if you found this blogpost you probably already know about how to do this).

function Get-AccessTokenFromGraphExplorerUrlOnClipboard
        $first = $true
        do {
            if(!$first) {
                Sleep -Seconds 1   
            $first = $false
            Write-Verbose "Trying to get Graph Explorer URL from clipboard"
            $url = Get-Clipboard
            if($url -ne $null -and $url.StartsWith("")) {
                $token = $url -split "[=&]" | Select -Index 1
        } while($token -eq $null -or !$token.StartsWith("ey"))

Write-Host -ForegroundColor Green "Sign into graph explorer and copy the url with the access token -"
$token = Get-AccessTokenFromGraphExplorerUrlOnClipboard -Verbose
$restparams = @{headers = @{Authorization = "Bearer $token"}}

Method 1

Use the users endpoint with the $expand parameter, to get the manager. The caveat here is that $top is limited to 100 when using $expand, and that this is currently only possible using the beta endpoint.

# Get users through the users endpoint - only for beta - top limited to 100 in API
$users = @()
$url = "`$expand=manager&`$top=100"
do {
    $result = Invoke-RestMethod $url @restparams
    $users += $result.value | select Id, @{Label="ManagerId";Expression={$_.Manager.Id}}
    $url = $result.'@odata.nextLink'
} while ($url)
$users | fl

Method 2

The users/delta endpoint provides the reference value a bit differently than the regular users endpoint, which can help us out. The delta endpoint has $top of 200 as max.

# Get users through the delta users endpoint - top limited to 200 in API
$users = @()
$url = "`$select=id,manager&`$top=200"
do {
    $result = Invoke-RestMethod $url @restparams
    $users += $result.value | select Id, @{Label="ManagerId";Expression={$_.'manager@delta'.id}}
    $url = $result.'@odata.nextLink'
} while ($url)
$users | fl

Method 3

Yet another method could be to follow manager trail and then back down through DirectReports like the following. This works only if most users have manager, or it will be very slow.

# Get all users into array
$users = @()
$url = "`$top=999"
do {
    $result = Invoke-RestMethod $url @restparams
    $users += $result.value | select Id, @{Label="ManagerId";Expression={$null}}
    $url = $result.'@odata.nextLink'
} while ($url)

# Create map
$usersMap = @{};
$users | Foreach{$usersMap[$_.Id] = $_}

$inc = 1

Write-Verbose "Starting manager traversal" -Verbose
$usersMap.Keys | Foreach {
    Write-Verbose "$inc / $($users.Count)" -Verbose; $inc += 1

    $user = $usersMap[$_]

    # If user does not have a fetched manager
    if(!$user.ManagerId) {
        Write-Verbose "Finding manager for $($user.Id)" -Verbose

        # Request the user object of the manager
        $url = "$($user.Id)/Manager"
        $manager = Invoke-RestMethod $url @restparams

        # If a manager was found
        if($manager.Id) {
            $usersMap[$user.Id].ManagerId = $manager.Id
            Write-Verbose "Found manager, diving into DirectReports: $($manager.Id)" -Verbose

            # Request the direct reports of the manager
            $url = "$($manager.Id)/DirectReports"
            $directReports = Invoke-RestMethod $url @restparams
            # If direct reports exists
            if($directReports.Value) {
                $directReports.Value | Where{$usersMap[$]} | Foreach {
                    $usersMap[$].ManagerId = $manager.Id

Other methods

  • You could request all users without manager, and use the batch API to expand the manager. Problem is that batch API only gives you 20 requests per batch, so this is super slow with many users.
  • You can run tasks / parallell processes where each process gets users starting with a, b, c etc. or something else to put them into buckets. However, you will run into backoff-algoritm issues.
  • You can “already know who is a manager”, through a group membership or something, and query those for direct reports. This might be the quickest solution today, but requires additional components.
  • The get directory objects by id request seems promising? Nope, it does not return manager as shown here, and $expand is not supported:
# Get users through the regular users endpoint, using batch requests to expand the manager
$users = @()
$url = "`$select=id&`$top=999"
do {
    $result = Invoke-RestMethod $url @restparams
    $body = @{
        "ids" = $
        "types" = @("user")
    } | ConvertTo-Json
    $result2 = Invoke-RestMethod "" -Method Post -Body $body @restparams -ContentType 'application/json'
    $users += $result2.value | select Id, @{Label="ManagerId";Expression={$_.Manager.Id}}
    $url = $result.'@odata.nextLink'
} while ($url)
$users | fl

So yeah, there you have it – difficult / slow. Let’s hope for OData 4.0 compliant select=manager at some point 🙂

One thought on “Today i learned that querying the Microsoft Graph for the all users with manager reference is slow

  1. When you GET an object by its id you have to specify a select on manager if you want it returned

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s