Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"
Solved! Go to 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
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |