Skip to content
← Back to blog
8 min en

KQL for SOC analysts: base queries in Defender

Read-only KQL queries to investigate sign-ins, processes and email in Microsoft Defender XDR: which table to use and how to limit the time range.

Written by Eduardo Olivares, SOC analyst in Barcelona.

  • kql
  • microsoft defender
  • advanced hunting
  • soc
  • blue team
  • microsoft sentinel

KQL (Kusto Query Language) is the language used to query telemetry in Microsoft Defender XDR and Microsoft Sentinel. This article gathers four read-only queries on sign-ins, processes and email, using the tables and columns as they appear in Microsoft Learn in November 2026. They are a starting point: thresholds and account names must be adapted to each environment, and it is best to try them first in a test tenant.

Short answer: to hunt in Defender, you only need to know which table answers each question. SigninLogs or EntraIdSignInEvents for identities, DeviceProcessEvents for processes, EmailEvents for email and CloudAppEvents for Microsoft 365 activity. Always limit the time range with Timestamp > ago(...), filter before joining tables and project only the columns you are going to read.

What KQL is and where it runs

In the Defender portal, you write the query under Hunting > Advanced hunting. That tool lets you explore up to 30 days of raw data from Defender for Endpoint, Defender for Office 365, Defender for Cloud Apps and Defender for Identity. If a Microsoft Sentinel workspace is onboarded to the portal, you can also query its tables, with whatever retention that workspace has configured.

Each table depends on the service that populates it being deployed. DeviceProcessEvents comes from Defender for Endpoint, EmailEvents from Defender for Office 365 and CloudAppEvents from Defender for Cloud Apps, which also needs the Microsoft 365 activities connector. If the service is not there, the query does not fail: it simply returns no rows.

Three rules before running anything

  1. Filter by time first. A condition such as Timestamp > ago(1d) at the start reduces the volume before any transformation.
  2. Filter before joining. Reduce each table before the join, put the smaller one on the left and use kind=inner: the default type (innerunique) removes duplicate rows from the left side and can hide results.
  3. Project only what is useful. A project with the columns you are going to read makes the results legible and, before a join, faster too.

One more detail from Microsoft's guidance: has performs better than contains when you are looking for a whole word.

Suspicious sign-ins

Relevant table and columns

SigninLogs is the Microsoft Entra ID sign-in table in Log Analytics and Sentinel. The most commonly used columns when starting out are TimeGenerated, UserPrincipalName, IPAddress, ResultType (0 means success and any other value is a failure), ResultDescription, Location, AppDisplayName and ClientAppUsed.

In the native Defender schema the equivalent table is EntraIdSignInEvents, with Timestamp, AccountUpn, IPAddress, ErrorCode, Country and Application. It requires a Microsoft Entra ID P2 license. The documentation announced that on 19 October 2026 it replaced AADSignInEventsBeta and that old queries would be migrated automatically. If you come across older material using AADSignInEventsBeta, change the table name and check the columns.

Query: repeated failures followed by a success

It looks for an IP that accumulates five or more failures for an account and then gets in within the hour after the last failure. The five and the hour are example values.

let ventana = 1h;
let fallos = SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType in ("50126", "50053")
| summarize Fallos = count(), UltimoFallo = max(TimeGenerated) by UserPrincipalName, IPAddress
| where Fallos >= 5;
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "0"
| project UserPrincipalName, IPAddress, Exito = TimeGenerated
| join kind=inner fallos on UserPrincipalName, IPAddress
| where Exito > UltimoFallo and Exito - UltimoFallo < ventana
| project UserPrincipalName, IPAddress, Fallos, UltimoFallo, Exito

Here a failure means a wrong password (50126) or a locked account (50053). If you count everything that is not 0, pending-MFA prompts (50074, 50076) get in too and the result fills up with noise. Also, SigninLogs only holds interactive sign-ins; non-interactive ones live in AADNonInteractiveUserSignInLogs.

The result does not prove a compromise: someone who forgets their password produces the same pattern. To decide, look at the country (Location), the client (ClientAppUsed) and whether the IP shows up on other accounts. This query groups by user, so it will not see an attack that spreads a few attempts across many accounts.

Processes and command line (DeviceProcessEvents)

DeviceProcessEvents records process creation. The usual columns are FileName, ProcessCommandLine, AccountName, DeviceName, InitiatingProcessFileName and InitiatingProcessCommandLine. Microsoft recommends identifying the program by its file name and not by the full command line, and comparing case-insensitively (in~, =~), because the same command can be written in many ways.

Query: PowerShell with encoded arguments

The -EncodedCommand parameter of powershell.exe accepts a Base64 string with UTF-16LE encoding. It has legitimate uses, so this query is for review, not for blind alerting.

DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| where ProcessCommandLine matches regex @"(?i)\s-(e|ec|en[a-z]*)\s"
| project Timestamp, DeviceName, AccountName, FileName, ProcessCommandLine,
    InitiatingProcessFileName, InitiatingProcessCommandLine
| order by Timestamp desc
| limit 100

The regular expression covers the abbreviations PowerShell accepts for the parameter (-e, -ec, -enc and similar); try it in a test tenant first. To read the content, decode the Base64 as UTF-16LE outside the query. Look at the parent process too: an unexpected InitiatingProcessFileName often tells you more than the command itself.

Email and inbox rules (EmailEvents, CloudAppEvents)

EmailEvents stores the processing of each email. To review phishing that reached the mailbox, the relevant columns are ThreatTypes, DeliveryLocation and LatestDeliveryLocation, which reflects the most recent location of the message, for example if it was removed after delivery. EmailDirection takes the values Inbound, Outbound and Intra-org.

EmailEvents
| where Timestamp > ago(7d)
| where EmailDirection == "Inbound"
| where ThreatTypes has "Phish"
| project Timestamp, NetworkMessageId, SenderFromAddress, RecipientEmailAddress,
    Subject, DeliveryLocation, LatestDeliveryLocation, DetectionMethods
| order by Timestamp desc

For inbox rules and forwarding, CloudAppEvents collects Exchange Online activity. The ActionType names in the query come from Microsoft's alert classification guides on forwarding and inbox manipulation.

CloudAppEvents
| where Timestamp > ago(7d)
| where ActionType in ("New-InboxRule", "UpdateInboxRules", "Set-InboxRule", "Set-Mailbox")
| project Timestamp, AccountDisplayName, AccountObjectId, ActionType,
    IPAddress, CountryCode, Isp, UserAgent, RawEventData
| order by Timestamp desc

The rule configuration is in RawEventData. Compare IPAddress, Isp and CountryCode with the user's usual history.

Common mistakes

  • Row limit: each query returns at most 100,000 records, and results cannot exceed 64 MB.
  • Timeout: a query that runs past 10 minutes ends in an error. Narrow the time range or filter earlier.
  • CPU quota: if the tenant reaches 100%, queries are blocked until the next 15-minute cycle. The portal shows whether a query has Low, Medium or High usage.
  • Time zone: queries are written in UTC. The portal converts the results to the time zone you have configured, and that difference causes confusion when comparing with other sources.
  • Retention: 30 days for native Defender data. For more, you need Sentinel or to export the data.
  • No expected size: if you do not know how many rows a query will return, run count first or add limit.

How it connects with triage and BEC

These queries answer specific questions within a broader workflow. The order in which to look at alerts is in alert triage in a SOC. Inbox rules and sign-ins from unusual locations fit with the business email compromise signals in Microsoft 365.

Frequently asked questions

Where do I run these queries?

The Defender ones (DeviceProcessEvents, EmailEvents, CloudAppEvents) in Hunting > Advanced hunting in the Defender portal. SigninLogs in Log Analytics, or in the Defender portal if you have onboarded the Sentinel workspace.

How many days back can I query?

Up to 30 days of native Defender data. Sentinel tables depend on the retention configured in the workspace.

Should I use SigninLogs or EntraIdSignInEvents?

It depends on where your data is. SigninLogs is the Log Analytics and Sentinel table; EntraIdSignInEvents is in the Defender schema and requires an Entra ID P2 license. The column names differ, so they cannot be swapped without adapting the query.

Why does my query return nothing?

Most often the service that populates the table is not deployed, the CloudAppEvents connector does not have Microsoft 365 activities enabled, or a permission to access that data is missing. After that, check the time range and the exact values of the filters.

Can I turn them into detection rules?

Microsoft states that the same hunting queries can be used to create custom detection rules. Quotas apply separately to manual queries and to rules, so optimize before scheduling them.