Query Details
HUNT 07 ADFS New Country Per User 30v90d
Query
id: a1f00007-0007-4007-9007-adfs00000007
name: HUNT-07 ADFS New Country per User (30d vs 90d baseline)
description: |
Detects users whose 30-day country footprint includes a country never seen
in the prior 60 days of ADFS sign-ins. Complements RULE-08 by surfacing
every first-seen country shift, not only ones with anomaly scoring.
severity: Medium
requiredDataConnectors:
- connectorId: AzureActiveDirectory
dataTypes:
- ADFSSignInLogs
tactics:
- InitialAccess
relevantTechniques:
- T1078
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:
-
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.
-
Data Source: It uses data from Azure Active Directory Federation Services (ADFS) sign-in logs.
-
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.
-
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.
-
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.
Details
David Alonso
Released: May 13, 2026
Tables
Keywords
Operators