Query Details
HUNT 02 SP Brute Force History 30d
Query
// Hunt : Workload Identity - Service Principal Brute Force History (30d)
// Tactics : CredentialAccess
// MITRE : T1110.003, T1078.004
// Purpose : Full 30-day brute force / credential stuffing history against SPs.
// Shows per-SP failure volume, error code distribution, IP diversity,
// and whether any failures were followed by a success (spray succeeded).
// Use to investigate incidents and baseline normal auth failure rates.
//==========================================================================================
let SuccessfulSignins = (AADServicePrincipalSignInLogs | invoke ExcludeAllowlistedIPs())
| where TimeGenerated > ago(30d)
| where ResultType == "0"
| summarize SuccessCount = count(), LastSuccess = max(TimeGenerated)
by ServicePrincipalId;
(AADServicePrincipalSignInLogs | invoke ExcludeAllowlistedIPs())
| where TimeGenerated > ago(30d)
| where ResultType != "0"
| where ResultType !in ("50076", "50079", "50058", "70044")
| extend GeoInfo = geo_info_from_ip_address(IPAddress)
| extend Country = tostring(GeoInfo.country_iso_code)
| summarize
FailedAttempts = count(),
UniqueIPs = dcount(IPAddress),
UniqueCountries = dcount(Country),
IPList = make_set(IPAddress, 20),
Countries = make_set(Country, 10),
ErrorCodes = make_set(ResultType, 10),
ErrorDescriptions = make_set(ResultDescription, 5),
Resources = make_set(ResourceDisplayName, 10),
FirstAttempt = min(TimeGenerated),
LastAttempt = max(TimeGenerated)
by ServicePrincipalName, ServicePrincipalId, AppId
| where FailedAttempts > 20 or UniqueIPs > 5
| join kind=leftouter SuccessfulSignins on ServicePrincipalId
| extend SpraySucceeded = SuccessCount > 0
| project-away ServicePrincipalId1
| order by FailedAttempts desc
Explanation
This KQL query is designed to analyze the history of failed sign-in attempts to service principals over the past 30 days, focusing on potential brute force or credential stuffing attacks. Here's a simplified breakdown:
-
Purpose: The query aims to identify service principals that have experienced a high volume of failed sign-in attempts, indicating possible brute force attacks. It also checks if any of these failed attempts were followed by a successful sign-in, suggesting a successful credential spray attack.
-
Successful Sign-ins: The query first creates a dataset of successful sign-ins (where
ResultTypeis "0") for service principals, excluding any IPs that are on an allowlist. It counts the number of successful sign-ins and notes the last successful sign-in time for each service principal. -
Failed Sign-ins: It then focuses on failed sign-in attempts, excluding certain error codes that are not relevant to brute force detection. It gathers information on:
- The total number of failed attempts.
- The diversity of IP addresses and countries from which these attempts originated.
- Lists of unique IPs, countries, error codes, error descriptions, and resources targeted.
- The time of the first and last failed attempt.
-
Filtering: The query filters for service principals with more than 20 failed attempts or more than 5 unique IP addresses involved, indicating unusual activity.
-
Joining Data: It joins the failed attempts data with the successful sign-ins data to determine if any service principal had a successful sign-in after multiple failed attempts.
-
Output: The final output includes details about each service principal, such as the number of failed attempts, IP diversity, and whether a credential spray attack succeeded (i.e., if there was a successful sign-in after failures). The results are sorted by the number of failed attempts in descending order.
This query helps security analysts investigate potential credential access attacks and establish a baseline for normal authentication failure rates.
Details
David Alonso
Released: April 21, 2026
Tables
Keywords
Operators