KQL Search

Query Details

HUNT 05 ADFS Threat Intel IP History 90d

Query

id: a1f00005-0005-4005-9005-adfs00000005
name: HUNT-05 ADFS TOR / Threat-Intel IP History (90d)
description: |
  Joins ADFSSignInLogs against ThreatIntelIndicators (active IP IoCs, STIX 2.1 -
  replaces deprecated ThreatIntelligenceIndicator) over 90 days. Surfaces
  historical authentication attempts from known-bad infrastructure that may not
  have triggered a real-time analytic rule.
severity: High
requiredDataConnectors:
  - connectorId: AzureActiveDirectory
    dataTypes:
      - ADFSSignInLogs
  - connectorId: ThreatIntelligence
    dataTypes:
      - ThreatIntelIndicators
tactics:
  - InitialAccess
  - CommandAndControl
relevantTechniques:
  - T1090.003
  - T1078
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 desc

Explanation

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:

  1. 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).

  2. 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.

  3. 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.

  4. 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.

  5. 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 profile picture

David Alonso

Released: May 14, 2026

Tables

ADFSSignInLogsThreatIntelIndicators

Keywords

ADFSThreatIntelIPAuthenticationInfrastructureAzureActiveDirectoryThreatIntelligenceInitialAccessCommandAndControl

Operators

letwhereagoisemptynowhasextendextracttostringisnotemptysummarizemake_setanybyinvokejoinkindoncountcountifdcountminmaxorder bydesc

Actions