Microsoft Graph pagination issue

[adam_gallard]

I am trying to get a list of all my users in Azure Active Directory, I am using the Microsoft Graph API and I can get a list up to 999 using the $top=999 parameter, but my recordset will be more than 999, so I started to investigate how to use the skiptoken to bring back the other pages of data, I used this as my ref (6) Loading paged and related data from MS Graph API into Power BI using a recursive Power Query fun.... I managed to pull back a second page but no more. here is my code, can someone take a look and let me know where I am going wrong.

let
  GetUserDetail = (Path)=>
   let
       Source = Json.Document(Web.Contents(Path)),
       NextList= @Source[value],
       nextpage = Path & "&" & Text.AfterDelimiter(@Source[odata.nextLink],"?"),
       result = try @NextList & @GetUserDetail(@nextpage) otherwise @NextList
   in

result,

UserDetail = GetUserDetail("https://graph.windows.net/mydomain.com/users?api-version=1.6&$top=10"),
#"Converted to Table" = Table.FromList(UserDetail, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

#"Converted to Table"

[Gerald23 · Accepted solution]

I also tried to get this to work in the past.

And my solution was to export the data from azure to a csv file using Powershell and then connect power bi to this csv file. Then schedule the powershell script to run when needed from task scheduler. But there might be an easier way which I haven't found yet.

In my Powershell script I export signinactivity for the users. Below the script that I use for this. Maybe this can help you

<#
.SYNOPSIS
Export Azure AD SignInActivity
.DESCRIPTION
Connect to App registrations and Export Azure AD SignInActivity
#>
 
# Application (client) ID, Directory (tenant) ID, and secret
$clientID = "*****************************"
$tenantName = "************.onmicrosoft.com"
$ClientSecret = "*****************"
$resource = "https://graph.microsoft.com/"
 
$ReqTokenBody = @{
    Grant_Type    = "client_credentials"
    Scope         = "https://graph.microsoft.com/.default"
    client_Id     = $clientID
    Client_Secret = $clientSecret
} 
 
$TokenResponse = Invoke-RestMethod -Uri "https://login.microsoftonline.com/$TenantName/oauth2/v2.0/token" -Method POST -Body $ReqTokenBody
 
# Get all users in source tenant
$uri = 'https://graph.microsoft.com/beta/users?$select=displayName,userPrincipalName,signInActivity,extension_**********_ABC,identities'
 
# If the result is more than 999, we need to read the @odata.nextLink to show more than one side of users
$Data = while (-not [string]::IsNullOrEmpty($uri)) {
    # API Call
    $apiCall = try {
        Invoke-RestMethod -Headers @{Authorization = "Bearer $($Tokenresponse.access_token)"} -Uri $uri -Method Get
    }
    catch {
        $errorMessage = $_.ErrorDetails.Message | ConvertFrom-Json
    }
    $uri = $null
    if ($apiCall) {
        # Check if any data is left
        $uri = $apiCall.'@odata.nextLink'
        $apiCall
    }
}
 
# Set the result into an variable
$result = ($Data | select-object Value).Value
$Export = $result | select DisplayName,UserPrincipalName,@{n="LastLoginDate";e={$_.signInActivity.lastSignInDateTime}},@{n="Email";e={$_.identities.issuerAssignedId}},extension_**********_ABC,identities
 
## [datetime]::Parse('2020-04-07T16:55:35Z')

$Date = Get-Date

$Export | export-csv -Path C:\temp\audit1.csv -NoTypeInformation