Export of Graph query results

Nathanael Rustad 6 Reputation points
2021-12-09T16:07:57.753+00:00

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.

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
39,712 questions
0 comments No comments
{count} vote

3 answers

Sort by: Most helpful
  1. JanardhanaVedham-MSFT 3,546 Reputation points
    2021-12-16T02:59:39.047+00:00

    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:

    158075-exportusersigninreportoutput.jpg

    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).

    158183-m365-usage-reports-graph-api-in-post-man-export-to.jpg

    158176-m365-usage-reports-graph-api-in-post-man-export-to.jpg

    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".

    3 people found this answer helpful.

  2. Natan Relaz 1 Reputation point
    2022-06-13T18:09:38.337+00:00

    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>


  3. Rajesh Ramanathan 1 Reputation point
    2022-09-08T00:07:20.403+00:00

    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>


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.