fetch combined successful interactive and non-interactive M365 sign-ins for the last month via Graph API

script.ps1

# This script is meant to be run directly in a powershell 7 shell (parts of it are not powershell 5 compatible)
# ADJUST THE FILTER STRING TO PULL THE SIGN-IN RECORDS YOU WANT FIRST, DO NOT ASSUME IT WILL WORK OUT-OF-THE-BOX

Import-Module Microsoft.Graph.Authentication
Import-Module Microsoft.Graph.Beta.Reports

try {
    Connect-MgGraph -Scopes "AuditLog.Read.All", "Organization.Read.All", "Policy.Read.All" -ContextScope Process
    Write-Host "Successfully connected to Microsoft Graph" -ForegroundColor Green
	$tenantName = Get-MgBetaOrganization | Where-Object { $_.Id -eq $($(Get-MgContext).TenantId) } | Select-Object -ExpandProperty DisplayName
	Write-Host "Tenant name: $tenantName" -ForegroundColor Yellow
}
catch {
    Write-Error "An error occurred while connecting to Microsoft Graph: $_"
}

# ---------------------------------
# instantiate list object to store log data in, define date/time range to pull log data from
# and loop through the date range, pulling records from the Graph API / storing them in memory
#
# this is computationally inefficient, but this script is meant for real-time / interactive security auditing 
# suspicious / malicious sign-in records can be inspected in detail via list slicing (e.g. $allSignIns[index])
# where index is the line number in the resulting CSV, offset by 2 (e.g. $allSignIns[0] for the 1st record)
# because the CSV line number starts at 1 (which is the header row) and the first record is line 2; index 
# slicing starts with 0 and $aSI doesn't have the header row. It's easier to think about this by doing 
# something like $allSignIns[csvLineNumber-2] where csvLineNumber is the actual line number (you can do math 
# operations in index slices e.g. $allSignIns[7-2] will return the sign-in record on line 7 in the CSV). 
# Also, it's easier to read the output by doing something like $allSignIns[7-2] | ConvertTo-JSON.

# list is more memory efficient than array
$allSignIns = [System.Collections.Generic.List[object]]::new()

# Calculate the start and end dates
#
# one month worth of sign-in records in a production env is going to be 100,000s of records (say nothing of malicious failures, YOU HAVE BEEN WARNED!)
$startDate = (Get-Date).Date.AddDays(0)  # Today
#$startDate = (Get-Date).Date.AddDays(-1)  # Yesterday
#$endDate = (Get-Date).AddDays(-30) # 30 days ago from today
$endDate = $endDate.AddMonths(-1)  # One month ago from today
#$endDate = $endDate.AddMonths(-1).AddDays(-1)  # One month ago from yesterdasy

# Configure retry parameters
$retryCount = 3
$retryDelay = 5 # seconds


Write-Host "Fetching successful interactive and non-interactive sign-ins" -ForegroundColor Yellow
# Process each day individually, starting from the most recent
for ($currentDate = $startDate; $currentDate -ge $endDate; $currentDate = $currentDate.AddDays(-1)) {
    $dayStartDate = $currentDate
    try {
        $dayEndDate = $dayStartDate.AddDays(1).AddSeconds(-1)
    }
    catch {
        Write-Warning "Reached maximum DateTime value. Using alternative end date calculation."
        $dayEndDate = [DateTime]::MaxValue
    }
    
     # Add a safety check because the loop keeps running even when $currentDate is less than $endDate for inexplicable reasons
        if ($currentDate -lt $endDate) {
            break
        }

    # Format dates for API filter (UTC)
    $utcStart = $dayStartDate.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ss.fffZ")
    $utcEnd = $dayEndDate.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ss.fffZ")
    
    # Build filter with explicit criteria and time range
	# ADJUST TO FIT YOUR NEEDS!
	# ---------------------------------------------------

	# all interactive (implicit) sign-ins between startDate and endDate
    #$filter = "createdDateTime ge $utcStart and createdDateTime le $utcEnd"
	
	# all interactive and non-interactive successful sign-ins for all users with display names that contain 'John S' or 'Bob' between startDate and endDate
	#$filter = "status/errorCode eq 0 and signInEventTypes/any(t:t eq 'interactiveUser' or t eq 'nonInteractiveUser') and createdDateTime ge $utcStart and createdDateTime le $utcEnd and (contains(UserDisplayName, 'Bob Smith') or contains(UserDisplayName, 'John S'))"
	
	# all interactive and non-interactive successful sign-ins for all users between startDate and endDate 
    $filter = "signInEventTypes/any(t:t eq 'interactiveUser' or t eq 'nonInteractiveUser') and status/errorCode eq 0 and createdDateTime ge $utcStart and createdDateTime le $utcEnd"
	
    # this is purely for debugging and can be commented out
    if ($currentDate -eq $startDate) {
        Write-Host "fetching initial records using filter: $($filter)"
    }
    
    Write-Host "Processing date: $($dayStartDate.ToUniversalTime().ToString('o'))..." -ForegroundColor Cyan
    
    $attempt = 0
    $stopwatch = [System.Diagnostics.Stopwatch]::new()
    
    do {
        try {
            $attempt++
            $stopwatch.Restart()
            $dailySignIns = Get-MgBetaAuditLogSignIn -Filter $filter -All -PageSize 999
            $stopwatch.Stop()
            
            if ($dailySignIns) {
                $allSignIns.AddRange($dailySignIns)
                Write-Host "Retrieved $($dailySignIns.Count) records between $($dayStartDate.ToUniversalTime().ToString('o')) to $($dayEndDate.ToUniversalTime().ToString('o'))" -ForegroundColor Green
                Write-Host ("Elapsed time: {0} minutes {1} seconds" -f [math]::Floor($stopwatch.Elapsed.TotalMinutes), $stopwatch.Elapsed.Seconds) -ForegroundColor Yellow
            }
            break # Exit retry loop on success
        } catch {
            # this is probably not necessary and was only added for debugging purposes
            if ($_.Exception.Response.StatusCode -eq 'TooManyRequests' -and $attempt -le $retryCount) {
                $retryAfter = [int]($_.Exception.Response.Headers.RetryAfter?.Delta.TotalSeconds ?? $retryDelay)
                Write-Warning "Throttled on attempt $attempt/$retryCount. Retrying in $retryAfter seconds..."
                Start-Sleep -Seconds $retryAfter
            }
            else {
                Write-Error "Failed to retrieve logs for $utcStart : $_"
                break
            }
        }
    } while ($attempt -le $retryCount)
   
    # API-friendly delay between days (probably don't need this either)
    Start-Sleep -Milliseconds 250
}    

# ---------------------------------
 
# pre-allocate $output in memory to the same size as the number of objects in $allSignIns
# we're not using $output anymore, but it's being left in and commented out for posterity
# $output = [System.Collections.Generic.List[object]]::new($allSignIns.Count)

$tenantCache = @{}
Get-MgBetaOrganization | ForEach-Object { $tenantCache[$_.Id] = $_.DisplayName }

$caPolicyCache = @{}
Get-MgBetaIdentityConditionalAccessPolicy | ForEach-Object { 
    $caPolicyCache[$_.Id] = $_.DisplayName 
}

$columnOrder = @(
'Date (UTC)',
'User',
'Username',
'Sign-in Type',
'IP address',
'User type',
'Operating System',
'Browser',
'User agent',
'Application',
'Resource',
'IP address',
'Location',
'Status',
'Client app',
'Token Issuer',
'Incoming token type',
'MFA Result',
'Sign-in error code',
'Failure reason',
'Compliant',
'Authentication Requirement',
'Through Global Secure Access?',
'AS Number',
'Flagged Review',
'Cross tenant access type',
'Authentication Protocol', 
'Unique token identifier',
'Original transfer method',
'Client credential type',
'Managed',
'Join Type',
'Token Protection',
'Incoming Token Type 2',
'Device ID',
'Correlation ID',
'Request ID',
'User ID',
'Application ID',
'Resource ID',
'Resource tenant ID',
'Home tenant ID',
'Session ID',
'Sign-in identifier',
'Home tenant name',
'Latency (sec)'
)

# instantiate $daysProcessed from the total number of days between $startDate and $endDate
$daysProcessed = ($startDate - $endDate).Days + 1

# if 365 organization only has one tenant, prefix the output file name with the tenant name
$tenantName = Get-MgBetaOrganization | Where-Object { $_.Id -eq $($(Get-MgContext).TenantId) } | Select-Object -ExpandProperty DisplayName

# Define the output file path
$outputFile = Join-Path -Path (Get-Location) -ChildPath "${tenantName}-Combined_Successful_Interactive_NonInteractiveSignIns_${startDate.ToUniversalTime().ToString('o')}_Last${daysProcessed}DaysEnding${endDate.ToString('yyyyMMdd')}.csv"

# Write the header line to the output file in CSV format
'"' + ($columnOrder -join '","') + '"' | Out-File $outputFile -Encoding utf8

# Use try/finally for proper resource management
try {
    $streamWriter = [System.IO.StreamWriter]::new($outputFile, $true, [System.Text.Encoding]::UTF8)

    foreach ($signIn in $allSignIns) {
        # Cache nested objects
        $location = $signIn.Location
        $status = $signIn.Status
        $device = $signIn.DeviceDetail
        $authDetails = $signIn.AuthenticationDetails
        $networkDetails = $signIn.NetworkLocationDetails
        
        # Convert $signIn to ordered hashtable (*NOT* PowerShell 5 compatible without syntax modifications!)
        # parts of this can probably be improved further, but it works
        $record = [ordered]@{
            'Date (UTC)'                      = $signIn.CreatedDateTime.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ssZ")
            'Request ID'                      = $signIn.Id
            'User agent'                      = $device.Browser
            'Correlation ID'                  = $signIn.CorrelationId
            'User ID'                         = $signIn.UserId
            'User'                            = $signIn.UserDisplayName
            'Username'                        = $signIn.UserPrincipalName
            'User type'                       = $signIn.UserType
            'Sign-in Type'                    = ($signIn.SignInEventTypes | Out-String).TrimEnd("`r", "`n")
            'Cross tenant access type'        = $signIn.CrossTenantAccessType
            'Incoming token type'             = $signIn.IncomingTokenType
            'Authentication Protocol'         = $signIn.AuthenticationProtocol
            'Unique token identifier'         = $authDetails.AuthenticationStepResultDetail
            'Original transfer method'        = $signIn.OriginalTransferMethod
            'Client credential type'          = $signIn.ClientCredentialType
            'Token Protection'                = $signIn.TokenIssuerType
            'Application'                     = $signIn.AppDisplayName
            'Application ID'                  = $signIn.AppId
            'Resource'                        = $signIn.ResourceDisplayName
            'Resource ID'                     = $signIn.ResourceId
            'Resource tenant ID'              = $signIn.ResourceTenantId
            'Home tenant ID'                  = $signIn.HomeTenantId
            'Home tenant name'                = $(if ($tenantCache.ContainsKey($signIn.HomeTenantId)) { 
                                                   $tenantCache[$signIn.HomeTenantId] 
                                                 } else { 
                                                   'Unknown Tenant' 
                                                 })
            'IP address'                      = $signIn.IpAddress
            'Location'                        = "$($location.City), $($location.State), $($location.CountryOrRegion)"
            'Status'                          = $(if ($status.ErrorCode) { "Failed" } else { "Success" })
            'Sign-in error code'              = $(if ($status.ErrorCode) { $status.ErrorCode } else { 'N/A' })
            'Failure reason'                  = $(if ($status.FailureReason) { $status.FailureReason } else { 'N/A' })
            'Client app'                      = $signIn.ClientAppUsed
            'Device ID'                       = $signIn.DeviceId
            'Browser'                         = $device.Browser
            'Operating System'                = $device.OperatingSystem
            'Compliant'                       = $(if ($null -ne $device.IsCompliant) { [bool]$device.IsCompliant } else { $false })
            'Managed'                         = $(if ($null -ne $device.IsManaged) { [bool]$device.IsManaged } else { $false })
            'Join Type'                       = $device.JoinType
            'MFA Result'                      = ($authDetails | Where-Object { 
                                                -not [string]::IsNullOrEmpty($_.AuthMethod) -and 
                                                $_AuthMethod -ne 'none'}
                                                ).AuthMethod -join ';'
            'Authentication Requirement'      = ($_.AuthenticationRequirementPolicies | Where-Object {
                                                    -not [string]::IsNullOrEmpty($_)
                                                  }) -join ';'
            'Sign-in identifier'              = $signIn.SignInIdentifier
            'Session ID'                      = $signIn.SessionId
            'Through Global Secure Access?'   = $(if ($null -ne $signIn.IsAccessViaConditionalAccess) { 
                                                  $signIn.IsAccessViaConditionalAccess 
                                                } else { $false })
            'AS Number'                       = $( 
                                                   if ($networkDetails -and $networkDetails.AutonomousSystemNumber) { 
                                                       $networkDetails.AutonomousSystemNumber 
                                                   } else { 0 })
            'Flagged Review'                  = $signIn.RiskState -ne "none"
            'Token Issuer'                    = $signIn.TokenIssuerType
            'Incoming Token Type 2'           = ($authDetails.IncomingTokenType | Select-Object -Unique) -join ';'
            'Latency (sec)'                   = $(if ($signIn.CreatedDateTime -and $signIn.ProcessingTime) {
                                                    (New-TimeSpan -Start $signIn.CreatedDateTime -End $signIn.ProcessingTime).TotalSeconds
                                                  } else { 0 })
            }

        # Add CA policy names using cache
        [string[]]$caPolicies = @()
        if ($null -ne $_AppliedConditionalAccessPolicies) {
            foreach ($policyId in $_AppliedConditionalAccessPolicies.PolicyId) {
                if ($caPolicyCache.ContainsKey($policyId)) {
                    $caPolicies += $caPolicyCache[$policyId]
                }
                else {
                    $caPolicies += "Unknown Policy ($policyId)"
                }
            }
        }
        $record['Conditional Access'] = ($caPolicies -join ";")
        
          # Add remaining properties, with null checks
        Add-Member -InputObject $record -MemberType NoteProperty -Name "Autonomous system number" `
                   -Value $(if ($networkDetails.AutonomousSystemNumber) { 
                       $networkDetails.AutonomousSystemNumber 
                   } else { 0 })
    
         Add-Member -InputObject $record -MemberType NoteProperty -Name "Flagged for review" `
                    -Value ($signIn.RiskState -ne "none")
    
        # Manually convert $record to CSV format, then write to file
        # This implementation looks ugly, but I couldn't find a better way of writing it that is relatively fast and doesn't eat an outrageous amount of memory
        $sb = [System.Text.StringBuilder]::new()
        foreach ($col in $columnOrder) {
            if ($sb.Length -gt 0) { [void]$sb.Append(',') }
            $value = $record[$col]
            if ($null -eq $value) {
                [void]$sb.Append('""')
            } elseif ($value -is [string]) {
                [void]$sb.Append('"').Append($value.Replace('"', '""')).Append('"')
            } else {
                [void]$sb.Append('"').Append($value).Append('"')
            }
        }
        $csvLine = $sb.ToString()
        $streamWriter.WriteLine($csvLine)
    }
}
finally {
    if ($streamWriter) {
        $streamWriter.Dispose()
    }
}

# ---------------------------------

Write-Host "CSV file with $($allSignIns.Count) records has been created: $outputFile" -ForegroundColor Green