Five useful KQL queries on Entra ID sign-in and audit logs
Entra ID logs hold most of the signals needed to spot an identity compromise, provided you keep them long enough and know how to query them. Here are the queries we use most often during audits, to be adapted to your own environment.
Prerequisite: sending logs to the right place
Log retention in the Entra portal is short and depends on licensing; check the current durations in Microsoft's documentation. To query a meaningful history, configure diagnostic settings that export logs to a Log Analytics workspace, optionally connected to Microsoft Sentinel.
The most useful tables are SigninLogs (interactive sign-ins), AADNonInteractiveUserSignInLogs (non-interactive sign-ins, including refresh token use), AADServicePrincipalSignInLogs (application sign-ins) and AuditLogs (directory changes). Non-interactive sign-ins are high volume, but ignoring them means ignoring much of the activity generated by stolen tokens.
1. Legacy authentication
Legacy protocols cannot do MFA. Before blocking them, and to confirm the block is effective, list who still uses them:
SigninLogs | where TimeGenerated > ago(30d) | where ClientAppUsed !in ("Browser", "Mobile Apps and Desktop clients") | summarize Attempts = count() by UserPrincipalName, ClientAppUsed, ResultType
Any result after the block is in place deserves a look: a rejected attempt is expected, a successful sign-in is not.
2. Repeated MFA failures
An attacker who knows the password runs into MFA. Repeated failures, especially denied notifications, can point to an MFA fatigue attempt:
SigninLogs | where TimeGenerated > ago(7d) | where ResultType == "500121" | summarize Failures = count() by UserPrincipalName, bin(TimeGenerated, 1h) | where Failures > 5
Tune the threshold to your user base. Any user who shows up should be contacted: their password is probably known to someone else.
3. Password spraying
The classic signature: a single IP address failing against many different accounts with error 50126 (invalid credentials).
SigninLogs | where TimeGenerated > ago(1d) | where ResultType == "50126" | summarize Accounts = dcount(UserPrincipalName), Failures = count() by IPAddress | where Accounts > 10 | order by Accounts desc
Column types. In SigninLogs, ResultType is a string: compare it with "50126", not 50126. A query that returns nothing is not always good news.
4. New application consents
Illicit consent is a quiet way to gain lasting access to data. Every consent should be known:
AuditLogs | where TimeGenerated > ago(30d) | where OperationName == "Consent to application" | extend Initiator = tostring(InitiatedBy.user.userPrincipalName), Application = tostring(TargetResources[0].displayName) | project TimeGenerated, Initiator, Application, Result
Add the Add app role assignment to service principal operation, which records application permissions being granted.
5. Privileged role changes
Any role assignment outside the normal process is a strong signal:
AuditLogs | where TimeGenerated > ago(30d) | where Category == "RoleManagement" | where OperationName has "member to role" | extend Target = tostring(TargetResources[0].userPrincipalName), Initiator = tostring(InitiatedBy.user.userPrincipalName) | project TimeGenerated, OperationName, Initiator, Target
PIM operations have their own names; filtering on the category rather than an exact operation name avoids missing them. Cross-check the results against your change requests.
What an audit checks
- Export of sign-in logs, including non-interactive and application sign-ins, and of audit logs.
- Effective retention in the workspace.
- Alert rules covering consents, roles and common attack patterns.
- Alert handling: who receives them and how quickly.
- Protection of the workspace itself against data deletion or tampering.