Query Details
HUNT 03 ADFS High Risk Country Timeline 90d
Query
id: a1f00003-0003-4003-9003-adfs00000003
name: HUNT-03 ADFS Sign-ins from High-Risk Countries (90d)
description: |
90-day timeline of successful ADFS sign-ins from high-risk countries per
user. Use to baseline travel patterns and find users with persistent
high-risk-country activity that may indicate compromised credentials.
severity: Medium
requiredDataConnectors:
- connectorId: AzureActiveDirectory
dataTypes:
- ADFSSignInLogs
tactics:
- InitialAccess
relevantTechniques:
- T1078
query: |
let HighRiskCountries = dynamic([
"KP", "IR", "RU", "CN", "BY", "CU", "SY", "VE", "NG", "MM", "AF"
]);
ADFSSignInLogs
| invoke ExcludeAllowlistedIPs()
| where TimeGenerated > ago(90d)
| where ResultType == 0
| where Location in (HighRiskCountries)
| summarize
Signins = count(),
UniqueIPs = dcount(IPAddress),
Countries = make_set(Location, 10),
Apps = make_set(AppDisplayName, 10),
FirstSeen = min(TimeGenerated),
LastSeen = max(TimeGenerated),
ActiveDays = dcount(startofday(TimeGenerated))
by UserPrincipalName
| extend Persistence = case(
ActiveDays >= 30, "Long-term",
ActiveDays >= 5, "Repeated",
"Sporadic")
| order by Signins desc
Explanation
This query is designed to monitor and analyze successful sign-ins to Active Directory Federation Services (ADFS) from countries considered high-risk over the past 90 days. Here's a simplified breakdown:
-
Purpose: The query aims to identify users who have logged in from high-risk countries, which could indicate compromised credentials. It helps establish a baseline for travel patterns and detect unusual or persistent activity.
-
Severity: The activity is considered to have a medium level of severity.
-
Data Source: It uses data from Azure Active Directory, specifically focusing on ADFS sign-in logs.
-
High-Risk Countries: The query checks for sign-ins from a predefined list of countries considered high-risk, such as North Korea, Iran, Russia, China, and others.
-
Filtering Criteria:
- Only considers logs from the last 90 days.
- Focuses on successful sign-ins (where the result type is 0).
- Excludes any IP addresses that are on an allowlist.
-
Data Aggregation:
- Counts the total number of sign-ins per user.
- Counts the number of unique IP addresses used.
- Lists up to 10 different countries and applications involved in the sign-ins.
- Records the first and last time the user signed in from these countries.
- Counts the number of active days the user signed in.
-
Persistence Classification:
- Classifies user activity as "Long-term" if they have been active for 30 or more days, "Repeated" for 5 to 29 days, and "Sporadic" for less than 5 days.
-
Output: The results are ordered by the number of sign-ins, with users having the most sign-ins appearing first.
This query helps security teams identify potentially compromised accounts by highlighting unusual sign-in patterns from high-risk locations.
Details
David Alonso
Released: May 13, 2026
Tables
Keywords
Operators