← Sentinel query library

Identity · Microsoft Sentinel

Correlate successful sign-ins with preceding failure bursts

Join successful interactive sign-ins to preceding invalid-credential events for the same tenant, user and application within 30 minutes.

Before you run

Data & scope

Required tables
SigninLogs
Time window
24 hours of successes with a preceding 30-minute failure window
  • Microsoft Entra ID connector with interactive SigninLogs available for at least 24 hours and 30 minutes.
  • Microsoft Entra ID P1 or P2 for ingestion and an Analytics table/query experience supporting joins.

Documentation review by Codex (AI). No live-tenant execution or detection-performance claim. Validate schema, data coverage and expected results in your workspace.

Kusto Query Language
Download .kql
// Hunting starter. Documentation checked 2026-11-13; not tenant-tested.
let reviewStart = ago(1d);
let minimumFailures = 5;
let failures = SigninLogs
    | where TimeGenerated >= reviewStart - 30m and TimeGenerated <= now()
    | where IsInteractive == true and ResultType == "50126"
    | where isnotempty(AADTenantId) and isnotempty(UserId) and isnotempty(AppId) and isnotempty(Id)
    | distinct AADTenantId, UserId, AppId, FailureId = Id,
        FailureTime = TimeGenerated, FailureIP = IPAddress;
SigninLogs
| where TimeGenerated >= reviewStart and TimeGenerated <= now()
| where IsInteractive == true and ResultType == "0"
| where isnotempty(AADTenantId) and isnotempty(UserId) and isnotempty(AppId) and isnotempty(Id)
| project AADTenantId, UserId, AppId, SuccessId = Id,
    SuccessTime = TimeGenerated, UserPrincipalName, SuccessIP = IPAddress, CorrelationId
| join kind=inner failures on AADTenantId, UserId, AppId
| where FailureTime < SuccessTime and FailureTime >= SuccessTime - 30m
| summarize FirstFailure = min(FailureTime), LastFailure = max(FailureTime),
    FailureIPs = make_set(FailureIP, 10)
    by AADTenantId, UserId, AppId, SuccessId, SuccessTime,
        UserPrincipalName, SuccessIP, CorrelationId, FailureId
| summarize FailureEvents = count(), FirstFailure = min(FirstFailure),
    LastFailure = max(LastFailure), FailureIPGroups = make_list(FailureIPs, 20)
    by AADTenantId, UserId, AppId, SuccessId, SuccessTime,
        UserPrincipalName, SuccessIP, CorrelationId
| where FailureEvents >= minimumFailures
| order by SuccessTime desc
| take 200

Read-only query. Run in your own authorized workspace after checking prerequisites.

Use this correlation to investigate a successful interactive sign-in after several invalid-credential events for the same identity and application. It is useful for reconstructing an authentication sequence. It cannot establish whether the successful session belongs to an attacker, a user who corrected a mistake, or a different legitimate session.

Read the timeline

Every successful event in the last day is compared with failures during its preceding 30 minutes. The failure input begins 30 minutes earlier than the review interval, avoiding a blind spot at the first success boundary. Only error 50126 qualifies as an invalid-credential failure, and success is the documented zero result in SigninLogs.

The join uses the directory, user object ID and application ID. It intentionally does not require identical IP addresses: a user can move networks, and an attacker can change infrastructure. Compare the success address with the sampled failure addresses during triage. This choice also creates unrelated matches when simultaneous sessions share an identity and application.

A dedicated aggregation deduplicates failure IDs for each success before counting. Five distinct failure events is a starting threshold. One failure can contribute to multiple later successful events if their windows overlap. The output therefore describes success-centered timelines, not a unique count of attacks.

Establish who controlled the session

Check device properties, authentication details, policy evaluation and the application’s own access logs. Confirm the event with the user through an established communication channel. Password correction, expired cached credentials, application retries and account recovery can produce legitimate sequences. A successful token issuance also does not establish that a protected resource operation succeeded.

Preserve the original sign-in IDs and correlation ID. Examine subsequent credential, consent or authorization changes only after binding them to an identity and time range. Avoid assigning causality from temporal proximity alone.

Validate before scheduling

Test a success after five distinct failure IDs, repeated copies of one failure, a failure after success, and a different application or directory. Confirm that duplicate rows do not inflate FailureEvents and that wrong-tenant rows never join. An explicit inner join retains the intended event pairs; a many-to-many join can still consume considerable resources.

This documentation-checked query is not tenant-tested. The result cap does not reduce intermediate join work. Narrow both inputs consistently for a busy investigation. Scheduled detection would additionally need ingestion-delay handling, repeat-alert suppression and a tested response policy.

Verify the source

Microsoft references

  1. Microsoft Learn — SigninLogs table schema ↗
  2. Microsoft Learn — Microsoft identity platform error codes ↗
  3. Microsoft Learn — Sign-in activity details ↗
  4. Microsoft Learn — Kusto inner join ↗
Before turning a hunt into an alert

Measure normal activity, validate known test cases, account for ingestion delays, choose a threshold and entity mapping, and assign an owner. Results require investigation.

Follow the detection learning path →