Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adam_gallard
Frequent Visitor

Microsoft Graph pagination issue

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"

1 ACCEPTED SOLUTION
Gerald23
Helper I
Helper I

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

 

View solution in original post

1 REPLY 1
Gerald23
Helper I
Helper I

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.