Investigate repeated sign-in failures
Group failed Entra sign-ins by user, with IP addresses, applications, and result codes.
Reference query. Not tested against a live tenant.
// Reference query for Microsoft Sentinel / Log Analytics SigninLogs.// Failed sign-ins are investigation leads, not a detection verdict.let Lookback = 24h;let MinimumFailures = 5;SigninLogs| where TimeGenerated >= ago(Lookback)| where ResultType != "0"| summarize FailureCount = count(), FirstSeen = min(TimeGenerated), LastSeen = max(TimeGenerated), IPAddresses = make_set(IPAddress, 20), Applications = make_set(AppDisplayName, 20), ResultCodes = make_set(ResultType, 20) by UserPrincipalName| where FailureCount >= MinimumFailures| order by FailureCount descRun the query
Copy the query from the Code tab into the workspace’s Logs editor. Adjust Lookback and MinimumFailures.
The query returns one row per user at or above the failure threshold, sorted by event count. Each row includes first and last timestamps, IP addresses, applications, and result codes. The three sets are limited to 20 distinct values each.
Review the result
ResultType != "0" includes all non-success results. These can include MFA interruptions, stale credentials, and application problems. Inspect the individual events and result codes before treating the count as suspicious.
Shared accounts are grouped together. Events with an empty user principal name also form one group. Check those rows separately.
An empty result needs a data check: confirm ingestion, retention, and the selected time range.
Parameters
| Name | Default | Purpose |
|---|---|---|
Lookback | 24h | Time range to search. |
MinimumFailures | 5 | Minimum number of failed events per user. |
Requirements
- Requires
- Entra sign-in logs in the workspace.
- Data available for the selected time range.
- Permissions
- Read access to the Log Analytics workspace and SigninLogs data.
- Testing
- Reviewed against the documented SigninLogs fields. Not run against a live tenant.
- Version
- 1.0.0