KQL Search

Query Details

HUNT-07 ADFS New Country per User (30d vs 90d baseline)

HUNT 07 ADFS New Country Per User 30v90d

Query

let now      = ADFSSignInLogs
  | invoke ExcludeAllowlistedIPs()
  | where TimeGenerated between (ago(30d) .. now())
  | where ResultType == 0
  | summarize NowCountries = make_set(Location, 25) by UserPrincipalName;
let baseline = ADFSSignInLogs
  | invoke ExcludeAllowlistedIPs()
  | where TimeGenerated between (ago(90d) .. ago(30d))
  | where ResultType == 0
  | summarize BaselineCountries = make_set(Location, 25) by UserPrincipalName;
now
| join kind=leftouter (baseline) on UserPrincipalName
| extend NewCountries = set_difference(NowCountries, coalesce(BaselineCountries, dynamic([])))
| where array_length(NewCountries) > 0
| project UserPrincipalName, NewCountries, NowCountries, BaselineCountries
| order by array_length(NewCountries) desc

Explanation

This query is designed to detect unusual login activity for users by analyzing their sign-in locations over time. Here's a simplified explanation:

  1. Purpose: The query identifies users who have logged in from a new country within the last 30 days that they haven't logged in from during the previous 60 days. This helps in detecting potential unauthorized access or suspicious activity.

  2. Data Source: It uses data from Azure Active Directory Federation Services (ADFS) sign-in logs.

  3. Process:

    • Current Analysis: It first gathers the list of countries from which each user has logged in during the last 30 days.
    • Baseline Analysis: It then collects the list of countries from which each user logged in during the 60 days before the last 30 days.
    • Comparison: It compares these two lists for each user to find any new countries in the recent 30-day period that were not present in the earlier 60-day period.

  4. Output: The query outputs a list of users along with the new countries they have logged in from, sorted by the number of new countries detected.

  5. Severity and Context: The severity is marked as Medium, indicating a moderate level of concern. It complements another rule by highlighting all first-seen country changes, not just those flagged by anomaly scoring.

Overall, this query helps in identifying potential security threats by monitoring changes in user login patterns across different geographical locations.