KQL Search

Query Details

HUNT 04 ADFS Impossible Travel Pairs 30d

Query

ADFSSignInLogs
| invoke ExcludeAllowlistedIPs()
| where TimeGenerated > ago(30d)
| where ResultType == 0
| where isnotempty(Location)
| sort by UserPrincipalName asc, TimeGenerated asc
| extend PrevTime    = prev(TimeGenerated, 1)
| extend PrevCountry = prev(Location, 1)
| extend PrevIP      = prev(IPAddress, 1)
| extend PrevUser    = prev(UserPrincipalName, 1)
| where UserPrincipalName == PrevUser
     and Location != PrevCountry
     and (TimeGenerated - PrevTime) < 4h
| project
    TimeGenerated, UserPrincipalName,
    CurrentCountry = Location, CurrentIP = IPAddress,
    PreviousCountry = PrevCountry, PreviousIP = PrevIP,
    DeltaMinutes   = datetime_diff('minute', TimeGenerated, PrevTime)
| summarize
    Occurrences = count(),
    MinDelta    = min(DeltaMinutes),
    Countries   = make_set(strcat(PreviousCountry, '->', CurrentCountry), 25),
    IPs         = make_set(CurrentIP, 25)
  by UserPrincipalName
| order by Occurrences desc

Explanation

This query is designed to detect suspicious login activities by identifying instances where the same user logs in from two different countries within a short time frame (default is 4 hours) over the past 30 days. This is often referred to as "impossible travel," as it would be unlikely for a person to physically travel between these locations in such a short period.

Here's a breakdown of the query:

  1. Data Source: It uses logs from Azure Active Directory, specifically the ADFSSignInLogs.

  2. Filtering:

    • It excludes IPs that are on an allowlist.
    • It looks at logs from the last 30 days.
    • It only considers successful logins (where ResultType is 0) and entries with a location.

  3. Processing:

    • It sorts the data by user and time.
    • For each login, it checks the previous login by the same user.
    • It identifies cases where the user logged in from a different country within 4 hours of the previous login.

  4. Output:

    • It projects relevant details such as the time of login, user, current and previous countries, and IP addresses.
    • It calculates the time difference in minutes between the two logins.
    • It summarizes the data to show the number of occurrences, the minimum time difference, and lists of countries and IPs involved for each user.

  5. Severity and Use: The query is marked with high severity and is useful for investigating users flagged by another rule (RULE-07) to find historical occurrences of such suspicious activities.

Overall, this query helps security teams identify potential unauthorized access or compromised accounts by highlighting unusual login patterns.