Kusto Query Language (KQL): A SOC Analyst's Practical Tutorial

Kusto Query Language (KQL) is the query language behind Microsoft Sentinel, Microsoft Defender XDR, and Azure Monitor Log Analytics. A KQL query is a sequence of operators connected by pipes, each one narrowing or reshaping the rows the previous operator produced. If you have just been handed a Sentinel workspace and told to find something, KQL is the tool you reach for.

How a KQL query reads, top to bottom

Every KQL query starts with a table name and then flows through a series of operators, each separated by a pipe (|). Read a query the way you'd read a recipe: the first line is the ingredient, and every line after it is a step that changes what you're holding. SecurityEvent | where EventID == 4625 starts with the whole SecurityEvent table and hands back only the rows where the event ID matches. Add another pipe and you're filtering the output of that filter, not the original table.

This matters more than it sounds like it should, because the order of operators changes both the result and the speed of the query. Filtering on a narrow time window and a specific event ID before you summarize is dramatically cheaper than summarizing everything and filtering afterward, because Log Analytics and Defender both use the time range to decide how much data they have to scan in the first place. A query that reads SecurityEvent | summarize count() by Account | where TimeGenerated > ago(1h) is not just slow, it's often wrong, because by the time you filter on time the aggregation has already thrown that column away.

Reading a query top to bottom before you touch it is also the fastest way to understand what somebody else wrote. If you inherit a detection rule or a hunting query from a colleague, walk it one pipe at a time: what table, what's the time window, what's being filtered out, what's being grouped, what's the final shape of the output. That habit alone will get you through most incident response handoffs where the query is the only documentation you have.

The operators that cover most SOC work

Microsoft's own documentation on Kusto Query Language notes that the basics it covers, the most-used functions and operators, should address 75 to 80 percent of the queries analysts write day to day. In practice, seven operators get you through nearly every triage task:

• where filters rows based on a condition, exactly like a SQL WHERE clause: | where EventID == 4625.
• project picks and renames the columns you want to keep, dropping everything else: | project TimeGenerated, Account, IpAddress.
• extend adds a new computed column without dropping the existing ones, useful for deriving a value like a duration or a flag: | extend IsRDP = LogonType == 10.
• summarize aggregates rows into groups, the workhorse for turning a flood of events into counts, summarize FailedAttempts = count() by IpAddress.
• join merges rows from two tables on a matching column, the operator you reach for when the answer needs evidence from two different tables at once.
• take (also written limit) returns a fixed number of rows with no particular ordering, mainly useful for a quick peek at what a table looks like before you build a real query.
• sort (or order by) arranges rows by one or more columns, almost always paired with desc when you want the biggest or most recent values first.

None of these operators are unique to security data. They're the same general-purpose Kusto operators documented for Microsoft Fabric's Real-Time Intelligence and Azure Data Explorer. What makes them feel like "SOC" tools is the tables you point them at.

Worked example: a Windows sign-in failure investigation

Say you're triaging an alert about repeated failed logons against a domain-joined host and you want to know whether it looks like a scripted brute-force attempt or just a user who forgot their password. The SecurityEvent table, populated from Windows Security event logs, is where that story lives. Event ID 4625 is a failed logon; Account, IpAddress, and LogonType tell you who, from where, and by what method.

SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625
| where LogonType in (3, 10)
| summarize FailedAttempts = count(), TargetAccounts = dcount(Account) by IpAddress
| where FailedAttempts > 20
| sort by FailedAttempts desc

Read it top to bottom:

1. SecurityEvent is the table, every Windows security event ingested into the workspace.
2. where TimeGenerated > ago(24h) restricts the scan to the last 24 hours. This line goes first for a reason: it's the cheapest filter to apply and it keeps everything downstream fast.
3. where EventID == 4625 narrows the table to failed logon events specifically. (4624 is the corresponding success event, worth knowing if you extend this query to look for a failure-then-success pattern, which is a classic signal of a brute-force attempt that eventually worked.)
4. where LogonType in (3, 10) keeps only network logons (type 3) and RDP-style remote interactive logons (type 10), which filters out noisy service and batch logon failures that aren't relevant to a credential-attack hypothesis. The full list of logon type values is worth memorizing or bookmarking; our Windows Event IDs cheat sheet has the complete table.
5. summarize FailedAttempts = count(), TargetAccounts = dcount(Account) by IpAddress collapses every remaining row into one row per source IP address, counting total failures and the number of distinct accounts that IP tried.
6. where FailedAttempts > 20 sets a threshold. Twenty is arbitrary; tune it against your own environment's baseline noise.
7. sort by FailedAttempts desc puts the worst offender at the top.

An IP address with a high FailedAttempts count and a high TargetAccounts count is trying many usernames against the same passwords, the signature of password spraying and brute-force techniques (T1110). An IP with a high FailedAttempts count but TargetAccounts of 1 is more likely a single locked-out or misconfigured account. If you extend this query to also pull the matching 4624 success events, you're checking whether an attacker eventually got in, which is the exact question behind valid accounts abuse (T1078): a compromised credential looks identical to a legitimate one once the logon succeeds.

Worked example: process and network telemetry from an endpoint

The second common shape of investigation starts on the endpoint, not the sign-in log. Say Defender flagged an alert involving a living-off-the-land binary, and you want to see whether that process also reached out to the internet. DeviceProcessEvents and DeviceNetworkEvents are two of the core tables in Defender's advanced hunting schema, and this is a real join between them.

DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ ("powershell.exe", "certutil.exe", "mshta.exe")
| where ProcessCommandLine has_any ("-enc", "-EncodedCommand", "urlcache", "-w hidden")
| where isnotempty(ProcessUniqueId)
| project ProcessTime = Timestamp, DeviceId, DeviceName, ProcessUniqueId, FileName, ProcessCommandLine, AccountName, InitiatingProcessFileName
| join kind=inner (
    DeviceNetworkEvents
    | where Timestamp > ago(7d)
    | where RemotePort in (80, 443, 8080)
    | where isnotempty(InitiatingProcessUniqueId)
    | project NetworkTime = Timestamp, DeviceId, InitiatingProcessUniqueId, RemoteIP, RemoteUrl, RemotePort
) on DeviceId, $left.ProcessUniqueId == $right.InitiatingProcessUniqueId
| where NetworkTime between (ProcessTime .. ProcessTime + 5m)
| sort by ProcessTime desc

Walking through it:

1. DeviceProcessEvents is Defender's process-creation table, one row per process launched on a monitored device.
2. where Timestamp > ago(7d) sets the lookback window. Note the column name: Defender's advanced hunting tables use Timestamp, not TimeGenerated, which trips up analysts moving between Sentinel's Log Analytics tables and Defender's schema.
3. where FileName in~ (...) filters to a short list of binaries commonly abused for downloading or executing payloads (the in~ makes the match case-insensitive).
4. where ProcessCommandLine has_any (...) looks for command-line fragments associated with encoded PowerShell, hidden windows, or certutil's URL-cache download trick. has_any matches whole tokens and is both faster and more precise than a substring search with contains.
5. where isnotempty(ProcessUniqueId) discards process rows that cannot be tied to one process instance, and project trims the row while preserving DeviceId and ProcessUniqueId as the correlation keys.
6. join kind=inner (...) merges in DeviceNetworkEvents, filtered to common web ports. The composite condition requires both the same DeviceId and an exact match from the created process's ProcessUniqueId to the network event's InitiatingProcessUniqueId. That identifies one process instance instead of mixing together unrelated launches that happen to share a file name (or a reused PID).
7. where NetworkTime between (ProcessTime .. ProcessTime + 5m) keeps only network connections that happened within five minutes of that exact process instance launching, constraining the result to the immediate response window this hunt is investigating.
8. sort by ProcessTime desc surfaces the most recent activity first.

The instance identifiers prevent cross-process matches; the final time-window filter then limits the result to connections made shortly after that exact launch. Without the instance keys, a time window alone can still mix separate launches of the same binary on a busy endpoint. This is the same discipline that shows up in EDR telemetry generally: process and network events tell two halves of the same story, and the join is how you read them together.

Two more patterns worth recognizing: MFA fatigue and mass repository access

Two more query shapes come up often enough that recognizing them on sight is worth the space, even outside a full walkthrough.

Repeated MFA denials. When an Entra ID sign-in fails specifically at the multi-factor step, whether the user declined the push, entered the wrong code, or simply timed out, it lands in SigninLogs with ResultType "500121." One denial is nothing. A burst of denials against the same account in a short window looks like MFA request generation, or push bombing (T1621): an attacker who already has the password and is trying to wear the user down into approving a push they never requested.

SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType == "500121"
| extend DenialReason = tostring(Status.additionalDetails)
| where DenialReason has "user declined"
| summarize Denials = count() by UserPrincipalName, bin(TimeGenerated, 15m)
| where Denials >= 3
| sort by Denials desc

ResultType == "500121" isolates strong-authentication failures. Status is a dynamic, JSON-like column, so extend DenialReason = tostring(Status.additionalDetails) pulls the human-readable reason out of it before you can filter on it. has "user declined" separates an explicit decline from a timeout or a technical error, and bin(TimeGenerated, 15m) buckets denials into 15-minute windows, so three declines spread across a week don't trip the same threshold as three declines in ten minutes.

Mass access to a shared repository. OfficeActivity carries SharePoint and OneDrive audit events, and a sudden spike in distinct files touched by one account is the signature of data collection from information repositories (T1213): a compromised account, or a departing employee, pulling far more out of a wiki or document library than ordinary browsing would produce.

OfficeActivity
| where TimeGenerated > ago(1h)
| where OfficeWorkload == "SharePoint"
| where Operation in ("FileAccessed", "FileDownloaded")
| summarize FilesTouched = dcount(SourceFileName), Events = count() by UserId, ClientIP, bin(TimeGenerated, 15m)
| where FilesTouched > 50
| sort by FilesTouched desc

Operation in ("FileAccessed", "FileDownloaded") narrows the flood of SharePoint audit events to the two operations that represent someone actually touching file content. dcount(SourceFileName) counts distinct files rather than raw events, since re-opening the same file repeatedly is normal and fifty different files in fifteen minutes usually isn't. Grouping by UserId and ClientIP together also catches the case where the volume is normal for the user but the source IP address is not.

KQL vs SQL for people who already know SQL

If you've written SQL, most of KQL will feel immediately familiar, with a few differences worth internalizing early.

The structural difference that matters most in practice is that KQL is a pipeline, not a single declarative statement. In SQL you describe the shape of the answer and the engine figures out the execution plan; in KQL you write the execution plan yourself, one operator at a time, and the order you choose is the order it runs in. The other detail worth knowing cold: KQL is case-sensitive everywhere, including column names, table names, and string comparisons done with ==. If you need a case-insensitive string match, use =~ instead, or one of the case-insensitive string operators like has versus has_cs.

Common mistakes that make queries slow or wrong

A handful of mistakes account for most of the "why is this query timing out" and "why did this return nothing" questions a new KQL writer runs into.

Filtering time last instead of first. Log Analytics and Defender both use your time filter to limit how much data gets scanned before anything else happens. A query that summarizes across the entire retention period and then filters by time afterward has already paid the cost of scanning everything.

Using contains when has would do. contains does a raw substring match, which is slow and can match inside unrelated words. has matches whole terms and is both faster and usually what you actually meant. If you're searching for the literal string admin and don't want it matching administrator, has is the right tool.

Joining before narrowing. Joining two full tables and then filtering the result wastes the join's own cost. Filter and project down each side of a join before you merge them, exactly like the endpoint worked example above.

Forgetting that KQL is case-sensitive. A query that compares Account == "Admin" will silently return nothing if the real value is stored as admin. This is one of the most common silent failures for analysts coming from case-insensitive tools.

Not aliasing summarize output. summarize count() by IpAddress gives you a column literally named count_. Naming it explicitly (summarize FailedAttempts = count() by IpAddress) makes the query and the output both easier to read six months later, which matters the next time a colleague has to pick up your saved query cold.

Where KQL runs

The same language shows up across a handful of Microsoft products, but the tables underneath differ by product:

• Microsoft Sentinel, Microsoft's cloud SIEM, is built on Azure Monitor's Log Analytics workspaces. Its analytics rules, hunting queries, and workbooks are all written in KQL against tables like SecurityEvent and SigninLogs.
• Microsoft Defender XDR's Advanced hunting page runs KQL against the Device*, Email*, and Identity* tables described in Microsoft's advanced hunting schema, which is a completely separate schema from Sentinel's Log Analytics tables even though the query language is identical.
• Azure Monitor Log Analytics is the general-purpose logging and metrics platform Sentinel is built on top of; if your organization runs Azure infrastructure without Sentinel, you may still be writing KQL against Azure Monitor tables for operational troubleshooting.
• Azure Data Explorer and Microsoft Fabric's Real-Time Intelligence are the broader data-analytics products KQL originated from, used well outside security for anything from IoT telemetry to application logs.

A SIEM like Sentinel exists to centralize this kind of log data in the first place; KQL is the language you use once it's centralized. If your organization writes detections in the vendor-agnostic Sigma rule format, know that Sigma's own backend conversion targets include Sentinel, meaning a Sigma rule is very often translated into exactly the kind of KQL query shown above before it runs.

How to practice

The fastest way to get comfortable is to stop guessing column names and start looking at real rows. Run a bare TableName | take 10 before you write anything else, so you can see the actual values in EventID, IpAddress, or ActionType rather than trusting memory or a cheat sheet. From there, build queries one pipe at a time, re-running after each addition so you can see exactly what each operator changed. This incremental habit is the single biggest difference between analysts who write confident KQL and analysts who paste a query from a blog post and hope.

Beyond the query editor itself, the fastest way to internalize the operators is to work triage scenarios end to end: an alert comes in, you have a hypothesis, and you write the query that either confirms or kills it. That's a very different skill from memorizing syntax, and it's the one that actually gets tested in SOC analyst interviews and on the job. If you want to practice against Sentinel-style and Defender-style panels without touching a production tenant or a client's data, SOCSimulator's SIEM and XDR rooms are built around this exact query-and-triage workflow, and the free tier gives you enough rooms to run these patterns yourself before you need them on a real shift.