azureblog.co.uk
← cd ~/posts

Six KQL queries for Entra sign-in logs every admin should keep

Once sign-in logs flow into Log Analytics, a few queries answer most of the questions you'll get. Copy these into a workbook or saved searches.

2 min read⚠ checked 11 Mar 2026Entra ID · Azure · Security
Part 7 of 7 in Conditional Access from zero
Part 5 of 5 in Automating Entra and Azure safely
On this page
  1. 1. Top sign-in failures
  2. 2. One user's recent sign-ins
  3. 3. Sign-ins blocked by Conditional Access
  4. 4. Successful sign-ins from new countries
  5. 5. Password spray pattern
  6. 6. Legacy authentication still in use
  7. 7. Non-interactive sign-ins by app
  8. 8. Service principal sign-ins from unexpected IPs
  9. Tips for writing your own

The Entra portal shows sign-in logs for 30 days with limited filtering. Sending them to a through Diagnostic settings in Entra keeps them for as long as you configure and lets you query them with . Exporting sign-in logs needs Entra ID P1 or P2.

Log AnalyticsThrough Diagnostic settingsSigninLogs (interactive)AADNonInteractiveUserSignInLogsAADServicePrincipalSignInLogsAADManagedIdentitySignInLogsAuditLogs
Entra sign-in logs land in several tables. Pick the right one.

1. Top sign-in failures

kql
SigninLogs
| where TimeGenerated > ago(7d) and ResultType != "0"
| summarize Count = count() by ResultType, ResultDescription
| top 20 by Count

2. One user's recent sign-ins

kql
SigninLogs
| where TimeGenerated > ago(3d)
| where UserPrincipalName =~ "jane.doe@contoso.com"
| project TimeGenerated, AppDisplayName, ResultType, ResultDescription,
          IPAddress, Location = tostring(LocationDetails.countryOrRegion),
          ConditionalAccessStatus
| order by TimeGenerated desc

3. Sign-ins blocked by Conditional Access

kql
SigninLogs
| where TimeGenerated > ago(1d) and ConditionalAccessStatus == "failure"
| summarize Count = count() by UserPrincipalName, AppDisplayName
| order by Count desc

4. Successful sign-ins from new countries

kql
let known = SigninLogs
| where TimeGenerated between (ago(30d) .. ago(1d)) and ResultType == "0"
| distinct UserPrincipalName, Country = tostring(LocationDetails.countryOrRegion);
SigninLogs
| where TimeGenerated > ago(1d) and ResultType == "0"
| extend Country = tostring(LocationDetails.countryOrRegion)
| join kind=leftanti known on UserPrincipalName, Country
| project TimeGenerated, UserPrincipalName, Country, IPAddress, AppDisplayName

5. Password spray pattern

kql
SigninLogs
| where TimeGenerated > ago(1h) and ResultType == "50126"
| summarize Users = dcount(UserPrincipalName) by IPAddress
| where Users > 10
| order by Users desc

50126 means invalid username or password. One IP failing against many different users is a classic spray.

6. Legacy authentication still in use

kql
SigninLogs
| where TimeGenerated > ago(14d)
| where ClientAppUsed !in ("Browser", "Mobile Apps and Desktop clients")
| summarize Count = count() by ClientAppUsed, UserPrincipalName, AppDisplayName
| order by Count desc
Don't forget non-interactive sign-ins: token refreshes and service sign-ins are in the AADNonInteractiveUserSignInLogs table. Some investigations need both.

7. Non-interactive sign-ins by app

Most sign-in volume is non-interactive: token refreshes by apps the user already signed in to. It's in a separate table and can be large, so only send it if you'll use it.

kql
AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(1d)
| summarize Count = count(), Failures = countif(ResultType != "0") by AppDisplayName
| order by Count desc

8. Service principal sign-ins from unexpected IPs

kql
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(7d) and ResultType == "0"
| summarize IPs = make_set(IPAddress, 20), Count = count() by ServicePrincipalName
| where array_length(IPs) > 3
| order by Count desc

Tips for writing your own

  • Filter on TimeGenerated first. It's the cheapest way to make a query fast.
  • Use =~ for case-insensitive UPN matches.
  • Dynamic columns like LocationDetails and DeviceDetail need tostring() before you group by them.
  • Look up any error code with the AADSTS lookup, or paste a single sign-in into the sign-in log explainer.

More ready-made queries are in the KQL library.

This post was last checked against Microsoft's documentation over six months ago. The approach should still hold, but check the linked sources for anything that has changed before you act on it.

You've reached the end of Conditional Access from zeroBack to the path →You've reached the end of Automating Entra and Azure safelyBack to the path →

Related posts