1.5.1. KQL's Pipeline Model

💡 First Principle: A KQL query starts with a data source, usually a table, and then passes rows through operators separated by the pipe character (|). Because each operator works only on its input, the cheapest and most important operation is to filter as early as possible, especially by time, so every later stage processes less data.

Here is the same pipeline as a real query:

DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName =~ "powershell.exe"
| where ProcessCommandLine has "-EncodedCommand"
| summarize Executions = count() by DeviceName, AccountName
| order by Executions desc
| take 10

Read it as a sentence: from process-creation events, keep the last seven days, keep PowerShell launches, keep those with an encoded command line, count them per device and account, sort by count, and show the top ten. Each line narrows the problem before the next one runs.

Three structural habits pay off immediately. First, know the time column: Defender XDR advanced hunting tables use Timestamp, while Sentinel and Log Analytics tables use TimeGenerated. Second, know the schema: every table has a fixed set of columns, and picking the right table is half of every hunting question (4.1.1). Third, filter before you aggregate or join: a where placed after a summarize can only filter the summarized output, and a join on unfiltered tables wastes time and can hit result limits.

⚠️ Exam Trap: A query that filters on TimeGenerated against a Defender XDR table such as DeviceProcessEvents, or on Timestamp against a Sentinel table such as SigninLogs, is a classic broken-option distractor. Match the time column to the table family.

Reflection Question: Two queries return identical results, but one filters by time on the first line and the other filters by time after a join. Why is the first usually much faster, and what risk does the second one carry in a large environment?