HUNT-05 ADFS TOR / Threat-Intel IP History (90d)
HUNT 05 ADFS Threat Intel IP History 90d
Query
let TIIPs = ThreatIntelIndicators
| where TimeGenerated > ago(30d)
| where IsActive == true
| where isempty(ValidUntil) or ValidUntil > now()
| where Pattern has "ipv4-addr:value"
| extend NetworkIP = extract(@"ipv4-addr:value\s*=\s*'([^']+)'", 1, tostring(Pattern))
| where isnotempty(NetworkIP)
| summarize
ThreatType = make_set(Tags, 10),
Description = any(tostring(Data.description))
by NetworkIP;
ADFSSignInLogs
| invoke ExcludeAllowlistedIPs()
| where TimeGenerated > ago(90d)
| join kind=inner (TIIPs) on $left.IPAddress == $right.NetworkIP
| summarize
Attempts = count(),
SuccessfulAuth = countif(ResultType == 0),
UniqueUsers = dcount(UserPrincipalName),
Users = make_set(UserPrincipalName, 25),
ThreatTypes = make_set(ThreatType, 10),
FirstSeen = min(TimeGenerated),
LastSeen = max(TimeGenerated)
by IPAddress
| order by SuccessfulAuth desc, Attempts descExplanation
This query is designed to identify potentially malicious authentication attempts by comparing sign-in logs from Active Directory Federation Services (ADFS) with known threat intelligence indicators over the past 90 days. Here's a simplified breakdown:
-
Data Sources: It uses two data sources:
- ADFSSignInLogs: Logs of sign-in attempts to ADFS.
- ThreatIntelIndicators: A list of known malicious IP addresses (Indicators of Compromise or IoCs).
-
Threat Intelligence Filtering:
- It filters the threat intelligence data to include only active indicators from the last 30 days that are still valid and specifically relate to IPv4 addresses.
-
Joining Data:
- The query excludes any IPs that are on an allowlist.
- It then joins the filtered threat intelligence data with the ADFS sign-in logs based on matching IP addresses.
-
Analysis:
- For each matching IP address, it calculates:
- The total number of sign-in attempts.
- The number of successful authentications.
- The number of unique users involved.
- A list of users who attempted to sign in.
- The types of threats associated with the IP.
- The first and last time the IP was seen in the logs.
- For each matching IP address, it calculates:
-
Output:
- The results are sorted by the number of successful authentications and total attempts, highlighting IPs with the most activity.
Overall, this query helps identify historical sign-in attempts from IP addresses associated with known threats, which might not have been detected by real-time monitoring.
Details
David Alonso
Released: May 14, 2026
Tables
ADFSSignInLogsThreatIntelIndicators
Keywords
ADFSThreatIntelIPAuthenticationInfrastructureAzureActiveDirectoryThreatIntelligenceInitialAccessCommandAndControl
Operators
letwhereagoisemptynowhasextendextracttostringisnotemptysummarizemake_setanybyinvokejoinkindoncountcountifdcountminmaxorder bydesc
Severity
HighTactics
InitialAccessCommandAndControl