KQL Search

Query Details

09 SIGNIN Distributed Coordinated Attack

Query

id: e9f0a1b2-c3d4-4e5f-6a7b-8c9d0e1f2a3b
name: "SigninLogs — Distributed Coordinated Attack (Botnet, 10+ IPs per User)"
version: 1.0.0
kind: Scheduled
description: |
  Detects highly distributed attacks where a single user is targeted from 10 or more unique IP addresses over 30 days. This pattern indicates botnet infrastructure or a coordinated campaign targeting high-value accounts. MITRE ATT&CK: T1110 (Brute Force).
severity: Medium
requiredDataConnectors:
  - connectorId: AzureActiveDirectory
    dataTypes:
      - SigninLogs
queryFrequency: PT12H
queryPeriod: P14D
triggerOperator: gt
triggerThreshold: 0
tactics:
  - CredentialAccess
relevantTechniques:
  - T1110
query: |

    // ---- Network Allowlist (exclude trusted IPs / CIDR / ranges) --------------
    let _allow = materialize(union isfuzzy=true (print R="" | take 0), (_GetWatchlist('NetworkAllowlist') | project R = tostring(IPOrRange)) | where isnotempty(R));
    let _allowCIDR  = toscalar(_allow | where not(R matches regex @'^\d+\.\d+\.\d+\.\d+-\d+\.\d+\.\d+\.\d+$') | extend R = iff(R has '/', R, strcat(R, '/32')) | summarize make_list(R));
    let _allowRange = toscalar(_allow | where R matches regex @'^\d+\.\d+\.\d+\.\d+-\d+\.\d+\.\d+\.\d+$' | summarize make_list(R));
    let _ExcludeAllowlistedIPs = (T:(IPAddress:string)) {
        T
        | extend IPAddress = tostring(IPAddress)
        | where array_length(_allowCIDR) == 0 or isnull(ipv4_is_in_any_range(IPAddress, _allowCIDR)) or not(ipv4_is_in_any_range(IPAddress, _allowCIDR))
        | mv-apply _r = _allowRange to typeof(string) on (
            extend _lo = tostring(split(_r,'-')[0]), _hi = tostring(split(_r,'-')[1])
            | extend _inRange = ipv4_compare(IPAddress, _lo) >= 0 and ipv4_compare(IPAddress, _hi) <= 0
            | summarize _anyInRange = max(toint(_inRange)))
        | where isnull(_anyInRange) or _anyInRange == 0
        | project-away _anyInRange
    };
    // ---------------------------------------------------------------------------
    let ipThreshold = 10;
    SigninLogs
    | invoke _ExcludeAllowlistedIPs()
    | where TimeGenerated > ago(14d)
    | where ResultType != "0"
    | summarize
        UniqueIPs      = dcount(IPAddress),
        FailedAttempts = count(),
        SourceIPs      = make_set(IPAddress, 100),
        StartTime      = min(TimeGenerated),
        EndTime        = max(TimeGenerated),
        Locations      = make_set(Location, 20)
      by UserPrincipalName
    | where UniqueIPs >= ipThreshold
    | project
        TimeGenerated  = StartTime,
        UserPrincipalName, UniqueIPs,
        FailedAttempts, SourceIPs,
        EndTime, Locations
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: UserPrincipalName
customDetails:
  UniqueIPs: UniqueIPs
  FailedAttempts: FailedAttempts
alertDetailsOverride:
  alertDisplayNameFormat: "Coordinated Attack — {{UserPrincipalName}} targeted from {{UniqueIPs}} IPs"
  alertDescriptionFormat: "{{FailedAttempts}} failures from {{UniqueIPs}} distinct IPs — botnet or coordinated campaign."
incidentConfiguration:
  createIncident: true
  groupingConfiguration:
    enabled: true
    reopenClosedIncident: false
    lookbackDuration: PT1H
    matchingMethod: AllEntities
    groupByEntities:
  - Account

Explanation

This query is designed to detect potential coordinated attacks on user accounts by analyzing sign-in logs. Here's a simplified breakdown of what it does:

  1. Purpose: The query identifies situations where a single user account is accessed from 10 or more different IP addresses within a 30-day period. This pattern suggests a possible botnet attack or a coordinated effort to breach high-value accounts.

  2. Data Source: It uses sign-in logs from Azure Active Directory to gather information about login attempts.

  3. Exclusion of Trusted IPs: The query first filters out any IP addresses that are considered trusted or are on an allowlist. This is done to focus only on suspicious activities.

  4. Time Frame: It looks at login attempts over the past 14 days.

  5. Criteria for Detection:

    • The user account must have login attempts from at least 10 unique IP addresses.
    • Only failed login attempts are considered (ResultType != "0").

  6. Output: For each user account that meets the criteria, the query provides:

    • The number of unique IP addresses involved.
    • The total number of failed login attempts.
    • A list of source IP addresses.
    • The time range during which these attempts occurred.
    • Locations associated with the login attempts.

  7. Alerting: If the criteria are met, an alert is generated with details about the user account and the nature of the attack. The alert includes the number of unique IPs and failed attempts, suggesting a botnet or coordinated campaign.

  8. Incident Management: The query is configured to create incidents in a security management system, allowing for further investigation and response.

Overall, this query helps security teams identify and respond to potential brute force attacks on user accounts by detecting unusual patterns of login attempts from multiple IP addresses.

Details

David Alonso profile picture

David Alonso

Released: April 20, 2026

Tables

SigninLogs

Keywords

SigninLogsAzureActiveDirectoryAccountUserPrincipalNameIPAddressLocationTimeGenerated

Operators

unionprinttake_GetWatchlistprojectwhereisnotemptymatchesregexextendhasstrcatsummarizemake_listtoscalariffmaterializetostringarray_lengthisnullipv4_is_in_any_rangemv-applysplitipv4_comparemaxtointagodcountcountmake_setminmaxbyproject-awayinvoke

Actions