Query Details
Multiple Unusual Unsynchronized Account Authentication In AD FS
Query
let query_frequency = 1h;
let query_period = 14d;
let synchronization_wait = 1h;
ADFSSignInLogs
| where TimeGenerated between (ago(query_frequency + synchronization_wait) .. ago(synchronization_wait))
| where UserId == "00000000-0000-0000-0000-000000000000"
| join kind=leftanti (
ADFSSignInLogs
| where TimeGenerated between (ago(query_period) .. ago(query_frequency + synchronization_wait))
| where UserId == "00000000-0000-0000-0000-000000000000"
| distinct UserPrincipalName
) on UserPrincipalName
| summarize arg_min(TimeGenerated, *) by UserPrincipalName
| lookup kind=leftouter (
AuditLogs
| where TimeGenerated > ago(query_frequency + synchronization_wait)
| where Category == "UserManagement" and OperationName in ("Add user", "Restore user") and Result == "success"
| mv-expand TargetResource = TargetResources
| where TargetResource["type"] == "User"
| summarize AddedUser_TimeGenerated = min(TimeGenerated) by UserPrincipalName = tostring(TargetResource["userPrincipalName"])
) on UserPrincipalName
| where not(isnotempty(AddedUser_TimeGenerated) and abs(TimeGenerated - AddedUser_TimeGenerated) between (0m .. synchronization_wait))
Explanation
This query is checking the ADFS sign-in logs for a specific user ID within a certain time frame (the past hour). It then excludes any logs where the user has signed in within the past two weeks. The query then finds the earliest sign-in time for each unique user.
Next, it checks the audit logs for successful "Add user" or "Restore user" operations within the past hour. It expands any target resources that are of type "User" and finds the earliest time these operations were performed for each unique user.
Finally, the query excludes any records where the user was added or restored within the past hour and the difference between the sign-in time and the time the user was added or restored is less than an hour.
In simpler terms, this query is looking for any unique users who have signed in within the past hour, but not within the past two weeks, and who were not added or restored within the past hour.
Details
Jose Sebastián Canós
Released: March 2, 2023
Tables
Keywords
Operators