Query Details
Multiple Match ADFS Sign In Logs Unknown Errors With Identity Logon Events Events
Query
ADFSSignInLogs
| where ResultType in (300030, 90000)
// | where IPAddress has "" or UserPrincipalName has "" // Custom condition
| project ADFSSignInLogs_TimeGenerated = TimeGenerated, UserPrincipalName//, IPAddress
| join kind=inner (
IdentityLogonEvents
| where Protocol == "Adfs"
) on $left.UserPrincipalName == $right.AccountUpn//, IPAddress
| project-away UserPrincipalName
| where ADFSSignInLogs_TimeGenerated between (TimeGenerated .. (TimeGenerated + 1h))
| summarize arg_max(TimeGenerated, *) by AccountUpn, ADFSSignInLogs_TimeGenerated
| join kind=leftouter (
IdentityLogonEvents
| where not(Protocol == "Adfs")
| project
NonAdfs_TimeGenerated = TimeGenerated,
NonAdfs_Protocol = Protocol,
NonAdfs_LogonType = LogonType,
NonAdfs_FailureReason = FailureReason,
Application, ActionType, AccountUpn, DeviceName
) on Application, ActionType, AccountUpn, $left.DestinationDeviceName == $right.DeviceName
| project-away *1
| where NonAdfs_TimeGenerated <= TimeGenerated
| summarize arg_max(NonAdfs_TimeGenerated, *) by ReportId, NonAdfs_Protocol, NonAdfs_LogonType, NonAdfs_FailureReason
// | where not(NonAdfs_FailureReason in ("WrongPassword", "Policy", "AccountLocked", "AccountDisabled"))
Explanation
This query is analyzing sign-in logs from Active Directory Federation Services (ADFS). It's specifically looking for entries where the result type is either 300030 or 90000.
The query then joins these logs with Identity Logon Events where the protocol used is ADFS, based on the User Principal Name (UPN). It projects away the UserPrincipalName and filters the logs to only include those where the ADFS sign-in time is within an hour of the logon event time.
Next, it summarizes the data to get the maximum time generated for each Account UPN and ADFS sign-in time.
The query then performs a left outer join with Identity Logon Events that did not use the ADFS protocol. It projects several fields from these non-ADFS events, including the time generated, protocol, logon type, failure reason, application, action type, account UPN, and device name.
The query then projects away the first set of fields and filters the results to only include those where the non-ADFS time generated is less than or equal to the time generated.
Finally, it summarizes the data to get the maximum non-ADFS time generated for each report ID, non-ADFS protocol, non-ADFS logon type, and non-ADFS failure reason.
The query is commented out to exclude certain failure reasons like "WrongPassword", "Policy", "AccountLocked", "AccountDisabled".
Details
Jose Sebastián Canós
Released: August 31, 2023
Tables
Keywords
Operators