1.5.2. Core Operators for Security Questions
💡 First Principle: Most security questions reduce to a handful of verbs: find the matching events, shape them into readable columns, count or group them to spot outliers, and connect them to other data. KQL has an operator for each verb, and recognizing which verb a question needs tells you which operator completes the query.
| Verb | Operator | Security example |
|---|---|---|
| Find | where | where ActionType == "LogonFailed" |
| Find by time | ago(), between | where Timestamp > ago(1d) |
| Shape (keep columns) | project | project Timestamp, DeviceName, AccountName |
| Shape (add columns) | extend | extend Hour = bin(Timestamp, 1h) |
| Count and group | summarize ... by | summarize Failures = count() by AccountName |
| Collect values | make_set(), dcount() | summarize Devices = dcount(DeviceName) by AccountName |
| Rank | order by / sort by, top, take | top 10 by Failures desc |
| Connect | join | Correlate sign-ins with process events by account |
| Combine | union | Search several tables at once |
String matching deserves special care because it's where exam distractors hide:
| Operator | Matches | Case | Notes |
|---|---|---|---|
== | Exact value | Sensitive | "PowerShell.exe" == "powershell.exe" is false |
=~ | Exact value | Insensitive | Safer for file and account names |
has | Whole term within a string | Insensitive | Uses the term index, so it's fast |
contains | Any substring | Insensitive | Slower; matches inside words |
startswith / endswith | Prefix or suffix | Insensitive | Common for paths and domains |
in / in~ | Any value in a list | Sensitive / insensitive | where FileName in~ ("psexec.exe", "wmic.exe") |
A brute-force detection pattern shows several verbs working together:
SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType != "0"
| summarize FailedAttempts = count(), TargetedAccounts = dcount(UserPrincipalName) by IPAddress
| where TargetedAccounts > 10
| order by FailedAttempts desc
Failed sign-ins (ResultType other than "0") from the last hour are grouped per source IP, and any IP that failed against more than ten distinct accounts surfaces as a likely password spray. Note the second where after summarize: it filters on a column that only exists because summarize created it. That's the pipeline model working as intended.
join deserves one more warning. Its default kind in KQL is innerunique, which removes duplicate keys from the left table before matching. When a question needs every matching pair, specify kind=inner explicitly. You'll build joins across email, identity, and endpoint tables in Phase 4.
⚠️ Exam Trap: has and contains are not interchangeable. has "admin" matches the term admin in "admin login" but not inside "administrator"; contains "admin" matches both. When a hotspot asks for the most efficient operator to match a whole word, choose has; when it must match part of a word, choose contains.
Reflection Question: You need to find every device where a user ran any of five known credential-dumping tools, regardless of how the file name is capitalized. Which operator matches a list of values case-insensitively, and which aggregation would show how many distinct devices each account touched?
