Export of Graph query results

Question

Is there a way to export the results of an MS Graph query? something equivalent to "Export-CSV" for PowerShell?

Specifically, I would like to query users last logon time (https://graph.microsoft.com/beta/users?$select=displayName,signInActivity), and then be able to sort by date to find inactive accounts.

Answer 1

Hi @Nathanael Rustad ,

Yes, Microsoft Graph REST API query results can be exported to CSV using Power Shell. With the below sample Power Shell script , GET: https://graph.microsoft.com/beta/users?$select=displayName,userPrincipalName, mail, id, CreatedDateTime, signInActivity, UserType&$top=999 Graph API query results can be exported to a CSV file. Please ensure to configure User.Read.All and AuditLog.Read.All permissions for the custom app that is registered in Azure AD to perfrom List the users & user signInActivity Graph API operations. Mentioned Micrsoft Graph API request or the below Power Shell script can be updated as per your requirement.

# A script to fetch user sign-in data from the Microsoft Graph and export it to CSV file.  
#  
CLS  
# Define the values applicable for the application used to connect to the Graph ( **ensure to change the below 3 values of AppId or Client ID, Tenant ID and Client Secret for your application of your tenant**)  
$AppId = "1111-2222-222-333-444444"  
$TenantId = "2331-rew-2224-644t-0000"  
$AppSecret = 'aPekdkandfkaiemgse='  
  
# Construct URI and body needed for authentication  
$uri = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token"  
$body = @{  
    client_id     = $AppId  
    scope         = "https://graph.microsoft.com/.default"  
    client_secret = $AppSecret  
    grant_type    = "client_credentials" }  
  
# Get OAuth 2.0 Token  
$tokenRequest = Invoke-WebRequest -Method Post -Uri $uri -ContentType "application/x-www-form-urlencoded" -Body $body -UseBasicParsing  
  
# Unpack Access Token  
$token = ($tokenRequest.Content | ConvertFrom-Json).access_token  
  
# Base URL  
$headers = @{Authorization = "Bearer $token"}  
  
# Get User sign in data  
Write-Host "Accessing the Graph to get user sign-in data..."  
$URI = "https://graph.microsoft.com/beta/users?`$select=displayName,userPrincipalName, mail, id, CreatedDateTime, signInActivity, UserType&`$top=999"  
$SignInData = (Invoke-RestMethod -Uri $URI -Headers $Headers -Method Get -ContentType "application/json")   
$Report = [System.Collections.Generic.List[Object]]::new()   
  
Foreach ($User in $SignInData.Value) {    
   If ($Null -ne $User.SignInActivity)     {  
      $LastSignInDateTime = $User.SignInActivity.LastSignInDateTime  
      $LastNonInteractiveSignInDateTime = $User.SignInActivity.LastNonInteractiveSignInDateTime}  
 Else { #No sign in data for this user account  
      $LastSignInDateTime = ""   
      $LastNonInteractiveSignInDateTime = ""}  
       
   $ReportLine  = [PSCustomObject] @{            
     UPN                = $User.UserPrincipalName  
     DisplayName        = $User.DisplayName  
     Email              = $User.Mail  
     Id                 = $User.Id  
     Created            = $User.CreatedDateTime    
     LastSignInDateTime = $LastSignInDateTime  
     LastNonInteractiveSignInDateTime = $LastNonInteractiveSignInDateTime  
     UserType           = $User.UserType }  
     $Report.Add($ReportLine)   
} # End ForEach  
  
# Do we have extra data to fetch?  
$NextLink = $SignInData.'@Odata.NextLink'  
  
While ($NextLink -ne $Null) { # We do... so process them.  
   Write-Host "Still processing..."  
   $SignInData = Invoke-WebRequest -Method GET -Uri $NextLink -ContentType "application/json" -Headers $Headers  
   $SignInData = $SignInData | ConvertFrom-JSon  
    
Foreach ($User in $SignInData.Value) {    
   If ($Null -ne $User.SignInActivity)     {  
      $LastSignInDateTime = $User.SignInActivity.LastSignInDateTime  
      $LastNonInteractiveSignInDateTime = $User.SignInActivity.LastNonInteractiveSignInDateTime}  
 Else { #No sign in data for this user account  
      $LastSignInDateTime = ""   
      $LastNonInteractiveSignInDateTime = ""}  
       
   $ReportLine  = [PSCustomObject] @{            
     UPN                = $User.UserPrincipalName  
     DisplayName        = $User.DisplayName  
     Email              = $User.Mail  
     Id                 = $User.Id  
     Created            = $User.CreatedDateTime    
     LastSignInDateTime = $LastSignInDateTime  
     LastNonInteractiveSignInDateTime = $LastNonInteractiveSignInDateTime  
     UserType           = $User.UserType }  
     $Report.Add($ReportLine) }  
  
   # Check for more data  
   $NextLink = $SignInData.'@Odata.NextLink'  
} # End While  
  
Write-Host "All done. " $Report.Count "accounts processed - output available in c:\Temp\ReportUserSignin.csv."  
  
$Report | Export-CSV -NoTypeInformation c:\Temp\ReportUserSignin.csv  

Sample output of exported report of user signInActivity after executing the Power Shell Script and as per script output CSV file shall be stored in c:\Temp\ReportUserSignin.csv folder location:

Power Shell script approach can be levaraged to export the query results into CSV file if the response content type is JSON format.

Postman API Tool can also be useful for exporting the query results into CSV and works fine with Microsoft 365 usage reports Graph APIs (as shown in the below screen shots).

Hope this helps.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

JanardhanaVedham-MSFT, can I get some assistance with getthing this to work?

This report is exactly what I need, but where do I get the AppID and App secret? When I try to run this script in power shell I get the following errors.

Invoke-WebRequest : {"error":"unauthorized_client","error_description":"AADSTS700016: Application with identifier '1111-2222-222-333-444444' was not found in the directory 'edited for safety'. This can happen if the application
e-WebRequest -Method Post -Uri $uri -ContentType "ap ...

• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
• FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand
  ConvertFrom-Json : Cannot bind argument to parameter 'InputObject' because it is null.
  At line:21 char:36
• $token = ($tokenRequest.Content | ConvertFrom-Json).access_token
• ~~~~~~~~~~~~~~~~
• CategoryInfo : InvalidData: (:) [ConvertFrom-Json], ParameterBindingValidationException
• FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ConvertFromJsonCommand

Accessing the Graph to get user sign-in data...
Invoke-RestMethod : The remote server returned an error: (401) Unauthorized.
At line:29 char:17

• ... gnInData = (Invoke-RestMethod -Uri $URI -Headers $Headers -Method Get ...
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
• FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

All done. 0 accounts processed - output available in c:\Temp\ReportUserSignin.csv.
Export-CSV : Could not find a part of the path 'C:\Temp\ReportUserSignin.csv'.
At line:85 char:12

• $Report | Export-CSV -NoTypeInformation c:\Temp\ReportUserSignin.csv
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : OpenError: (:) [Export-Csv], DirectoryNotFoundException
• FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ExportCsvCommand

PS C:\WINDOWS\system32>

Answer 3

Hello Janardhana,

When I run the script with correct parameters, i get the below error.. I suspect that it is struggling to get the oauth2 token. Can you point me in the right direction or confirm if the script is still valid pls ?

Accessing the Graph to get user sign-in data...
Invoke-RestMethod : The remote server returned an error: (400) Bad Request.
At line:29 char:17

• ... gnInData = (Invoke-RestMethod -Uri $URI -Headers $Headers -Method Get ...
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
• FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

All done. 0 accounts processed - output available in c:\Temp\ReportUserSignin.csv.

PS C:\Windows\system32>