Adversaries may be attempting to brute-force account credentials by generating a sudden increase in login attempts with a high failure rate, which is a common tactic to gain unauthorized access. SOC teams should proactively hunt for this behavior in Azure Sentinel to identify potential credential compromise attempts early and mitigate lateral movement risks.
KQL Query
let starttime = todatetime('{{StartTimeISO}}');
let endtime = todatetime('{{EndTimeISO}}');
let lookback = starttime - 14d;
let failureThreshold = 15;
let percentageChangeThreshold = 50;
SigninLogs
//Collect number of users logging in for each hour
| where TimeGenerated >= lookback
| summarize dcount(UserPrincipalName) by bin(TimeGenerated, 1h)
| extend hour = datetime_part("Hour",TimeGenerated)
| extend day = dayofweek(TimeGenerated)
//Exclude Saturday and Sunday as they skew the data, change depending on your weekend days
| where day != 6d and day != 7d
| order by TimeGenerated asc
//Summarise users trying to authenticate by each hour of the day
| summarize make_list(dcount_UserPrincipalName), make_list(TimeGenerated), avg(dcount_UserPrincipalName), make_list(day) by hour
//Find outlier hours where the number of users trying to authenticate spikes, expand and then keep only anomalous rows
| extend series_decompose_anomalies(list_dcount_UserPrincipalName)
| mv-expand list_dcount_UserPrincipalName, series_decompose_anomalies_list_dcount_UserPrincipalName_ad_flag, list_TimeGenerated, list_day
| where series_decompose_anomalies_list_dcount_UserPrincipalName_ad_flag == 1
//Calculate the percentage change between the spike and the average users authenticating
| project TimeGenerated=todatetime(list_TimeGenerated), Hour=hour, WeekDay=list_day, AccountsAuthenticating=list_dcount_UserPrincipalName, AverageAccountsAuthenticatin=round(avg_dcount_UserPrincipalName, 0), PercentageChange = round ((list_dcount_UserPrincipalName - avg_dcount_UserPrincipalName) / avg_dcount_UserPrincipalName * 100, 2)
| order by PercentageChange desc
//As an additional feature we collect successful and unsuccessful logins during the 1h windows with anomalies
| join kind=inner(
SigninLogs
| where TimeGenerated >= lookback
| where ResultType == "0"
| summarize Success=dcount(UserPrincipalName), SuccessAccounts=make_set(UserPrincipalName) by bin(TimeGenerated, 1h)
| join kind=inner(
SigninLogs
| where TimeGenerated >= lookback
//Failed sign-ins based on failed username/password combos or failed MFA
| where ResultType in ("50126", "50074", "50057", "51004")
| summarize Failed=dcount(UserPrincipalName), FailedAccounts=make_set(UserPrincipalName) by bin(TimeGenerated, 1h)
) on TimeGenerated
| project-away TimeGenerated1
| extend Total = Failed + Success
| project TimeGenerated, SuccessRate = round((toreal(Success) / toreal(Total)) *100) , round(FailureRate = (toreal(Failed) / toreal(Total)) *100), SuccessAccounts, FailedAccounts
) on TimeGenerated
| order by PercentageChange
| project-away TimeGenerated1
//Thresholds, 15% account authentication failure rate at a 50% increase in accounts attempting to authenticate by default
//Comment out line below to see all anomalous results
| where FailureRate >= failureThreshold and PercentageChange >= percentageChangeThreshold
| extend timestamp = TimeGenerated
id: 528c1708-a67e-4e2f-b76d-d5e5e88a22aa
name: Login spike with increase failure rate
description: |
'Query over SigninLogs summarizes login attempts per hour on weekdays. Kusto anomaly detection finds login spikes. Calculates percentage change between anomalous period and average logins. Determines success and failure rate for logins for 1 hour period.'
description_detailed: |
'This query over SiginLogs will summarise the total number of login attempts for each hour of the day on week days, this can be edited.
The query then uses Kusto anomaly detection to find login spikes for each hour across all days. The query will then calculate the
percentage change between the anomalous period and the average logins for that period. Finally the query will determine the success
and failure rate for logins for the given 1 hour period, if a specified % change in logins is detected alongside a specified failure rate
a result is presented.'
requiredDataConnectors:
- connectorId: AzureActiveDirectory
dataTypes:
- SigninLogs
tactics:
- InitialAccess
relevantTechniques:
- T1078
query: |
let starttime = todatetime('{{StartTimeISO}}');
let endtime = todatetime('{{EndTimeISO}}');
let lookback = starttime - 14d;
let failureThreshold = 15;
let percentageChangeThreshold = 50;
SigninLogs
//Collect number of users logging in for each hour
| where TimeGenerated >= lookback
| summarize dcount(UserPrincipalName) by bin(TimeGenerated, 1h)
| extend hour = datetime_part("Hour",TimeGenerated)
| extend day = dayofweek(TimeGenerated)
//Exclude Saturday and Sunday as they skew the data, change depending on your weekend days
| where day != 6d and day != 7d
| order by TimeGenerated asc
//Summarise users trying to authenticate by each hour of the day
| summarize make_list(dcount_UserPrincipalName), make_list(TimeGenerated), avg(dcount_UserPrincipalName), make_list(day) by hour
//Find outlier hours where the number of users trying to authenticate spikes, expand and then keep only anomalous rows
| extend series_decompose_anomalies(list_dcount_UserPrincipalName)
| mv-expand list_dcount_UserPrincipalName, series_decompose_anomalies_list_dcount_UserPrincipalName_ad_flag, list_TimeGenerated, list_day
| where series_decompose_anomalies_list_dcount_UserPrincipalName_ad_flag == 1
//Calculate the percentage change between the spike and the average users authenticating
| project TimeGenerated=todatetime(list_TimeGenerated), Hour=hour, WeekDay=list_day, AccountsAuthenticating=list_dcount_UserPrincipalName, AverageAccountsAuthenticatin=round(avg_dcount_UserPrincipalName, 0), PercentageChange = round ((list_dcount_UserPrincipalName - avg_dcount_UserPrincipalName) / avg_dcount_UserPrincipalName * 100, 2)
| order by PercentageChange desc
//As an additional feature we collect successful and unsuccessful logins during the 1h windows with anomalies
| join kind=inner(
SigninLogs
| where TimeGenerated >= lookback
| wh| Sentinel Table | Notes |
|---|---|
SigninLogs | Ensure this data connector is enabled |
Scenario: Scheduled System Maintenance or Patching
Description: A large number of login attempts may occur as part of a scheduled system maintenance or patching process that requires administrative access.
Filter/Exclusion: Exclude logins associated with known maintenance windows using the UserAgent or ClientIP fields, or use a custom tag like IsMaintenance: true in Azure Monitor Logs.
Scenario: Automated Backup or Sync Jobs
Description: Automated backup or synchronization jobs may authenticate to the system using service accounts or scheduled tasks, leading to a spike in login attempts.
Filter/Exclusion: Filter out logins from known service accounts (e.g., [email protected]) or use the IsServiceAccount field in Azure AD logs.
Scenario: User-Initiated Bulk Access Request
Description: A user may request access to a large number of resources or systems, resulting in multiple login attempts across different systems.
Filter/Exclusion: Exclude logins that occur within a short time window (e.g., 10 minutes) and are associated with the same user or user group. Use the UserPrincipalName field for correlation.
Scenario: Multi-Factor Authentication (MFA) Retry Attempts
Description: Users attempting to log in after failing MFA may trigger multiple login attempts, which could be misinterpreted as a spike.
Filter/Exclusion: Filter out logins that have a ResultStatus of MFAChallenge or MFAFailed in Azure AD logs, or use the IsMFAEnabled field to identify and exclude MFA-related attempts.
Scenario: Third-Party Integration or API Testing
Description: Third-party systems or API testing tools may generate a high volume of login attempts when testing authentication endpoints.
Filter/Exclusion: