KQL for sign-in hunting

Building blocks that the code pages and guides reuse.

Which failure codes dominate my tenant?

Start here. It shows which codes appear most, so you know which pages to read first.

Interactive sign-in failures by code, last 7 days

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != "0"
| summarize Events = count(), Users = dcount(UserPrincipalName) by ResultType, ResultDescription
| sort by Events desc

The same for non-interactive sign-ins

Non-interactive sign-in failures by code, last 7 days

AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(7d)
| where ResultType != "0"
| summarize Events = count(), Users = dcount(UserPrincipalName) by ResultType, ResultDescription
| sort by Events desc

Note

In AADNonInteractiveUserSignInLogs, the columns Status, DeviceDetail, LocationDetails and ConditionalAccessPolicies are strings, not dynamic. Use parse_json() before you read their fields. Non-interactive rows are also aggregated by Entra ID.

Points to remember

• ResultType is a string column. Write ResultType == "50126".
• "0" means success. Everything else is a failure or an interrupt.
• Conditional Access only runs after the first factor succeeds. Credential failures show ConditionalAccessStatus as notApplied.