Tips for running better KQL Queries in Azure

If you’re managing Microsoft Entra ID, raw log tables can quickly become overwhelming. Millions of sign-in attempts, application interactions, and administrative updates flow through Log Analytics daily.

Using Kusto Query Language (KQL) allows you to filter out noise and focus on critical identity insights. Here are some essential KQL queries for auditing activity, hunting threats, and keeping your tenant secure.

1. Spotting Repeated Failed Sign-Ins (Brute-Force & Spraying)

To identify users experiencing repeated login failures—potentially indicating brute-force attacks, misconfigured apps, or expired credentials—aggregate failure counts in SigninLogs over the past 24 hours.

Code snippet

SigninLogs
| where TimeGenerated >= ago(24h)
| where ResultType != "0" // 0 indicates success
| summarize FailedCount = count() by UserPrincipalName, ResultDescription
| order by FailedCount desc
| take 10

2. Hunting for High-Risk Sign-Ins

If Entra ID Protection is enabled, Microsoft automatically flags suspicious logons (e.g., impossible travel, anonymous IP addresses, or leaked credentials).Filter for medium- to high-risk sign-ins to prioritize triage.

Code snippet

SigninLogs
| where TimeGenerated >= ago(7d)
| where RiskLevelDuringSignIn in~ ("medium", "high") 
   or RiskState in~ ("atRisk", "confirmedCompromised")
| project TimeGenerated, UserPrincipalName, IPAddress, Location, AppDisplayName, RiskLevelDuringSignIn, RiskDetail
| order by TimeGenerated desc

3. Tracking Administrative Role Changes

When an attacker gains an initial foothold, privilege escalation is often the next step. Monitor AuditLogs for sensitive updates, such as users being added to privileged roles (e.g., Global Administrator).

Code snippet

AuditLogs
| where TimeGenerated >= ago(30d)
| where Category has "RoleManagement" or OperationName has_any ("Add member to role", "Activate eligible role")
| extend Actor = tostring(InitiatedBy.user.userPrincipalName)
| extend TargetUser = tostring(TargetResources[0].userPrincipalName)
| extend RoleName = tostring(TargetResources[0].modifiedProperties[0].newValue)
| project TimeGenerated, OperationName, Actor, TargetUser, RoleName
| order by TimeGenerated desc

4. Detecting Legacy Authentication Attempts

Legacy protocols (such as basic POP3, IMAP, or older SMTP clients) bypass modern defenses like Multi-Factor Authentication (MFA). Isolating these sign-in attempts helps enforce strict Conditional Access policies.

Code snippet

SigninLogs
| where TimeGenerated >= ago(7d)
| where ClientAppUsed in ("Authenticated SMTP", "AutoDiscover", "Exchange ActiveSync", "IMAP4", "POP3")
| summarize AttemptCount = count(), UniqueUsers = dcount(UserPrincipalName) by ClientAppUsed, AppDisplayName
| order by AttemptCount desc

5. Auditing OAuth Application Consents

Consent phishing remains a common attack vector where users unknowingly grant broad permissions to malicious third-party applications. Reviewing OAuth permission grants helps detect rogue application additions.

Code snippet

AuditLogs
| where TimeGenerated >= ago(30d)
| where Category == "ApplicationManagement"
| where OperationName has_any ("Consent to application", "Add delegated permission grant")
| extend Actor = tostring(InitiatedBy.user.userPrincipalName)
| extend AppName = tostring(TargetResources[0].displayName)
| extend Permissions = tostring(TargetResources[0].modifiedProperties[0].newValue)
| project TimeGenerated, Actor, AppName, Permissions, OperationName
| order by TimeGenerated desc

Tips for Better KQL Queries

  • Filter by time first: Always start queries with a where TimeGenerated >= ago(...) clause to reduce the volume of scanned data and speed up execution.
  • Limit returned columns:Use project to keep only relevant fields, making output easier to scan and analyze.
  • Leverage aggregations: Use summarize with functions like count(), dcount(), and make_set() to convert raw log streams into structured summaries.