K0.4 Your First Security Query

The 4-operator query pattern

Every KQL query follows the same pattern. Learn these 4 operators and you can investigate any table in the workspace.

Figure K0.4 — Every query follows this pattern: pick the table, filter the rows, select the columns, limit the output.

Building the query line by line

Open the Sentinel Logs blade. The editor is blank. Type the table name:

1

SigninLogs

Press Run (or Shift+Enter). This returns EVERY row in SigninLogs for the default time range (24 hours). At NE, this is approximately 45,000 rows — too many to be useful. The first rule of KQL: always filter before examining.

Add a time filter and a result filter:

1
2
3

SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType != "0"

The pipe character | chains operators — each line transforms the output of the previous line. where TimeGenerated > ago(1h) keeps only events from the last hour. where ResultType != "0" keeps only failures (ResultType 0 = success). At NE, this reduces 45,000 rows to approximately 30-80 failed sign-ins in the last hour.

Add column selection:

1
2
3
4

SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType != "0"
| project TimeGenerated, UserPrincipalName, IPAddress, ResultType, ResultDescription

project selects which columns appear in the output. Without project, Sentinel shows ALL columns (40+ fields in SigninLogs). With project, you see exactly the 5 fields that matter for this investigation.

Add sorting:

1
2
3
4
5

SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType != "0"
| project TimeGenerated, UserPrincipalName, IPAddress, ResultType, ResultDescription
| sort by TimeGenerated desc

sort by TimeGenerated desc shows the most recent failures first. This is your first complete investigation query: all failed sign-ins in the last hour, showing who, from where, why it failed, sorted newest first.

Run it. The results appear in under 5 seconds. You just queried your organisation’s authentication telemetry directly. No portal page, no workbook, no template. Five lines of KQL.

Reading the results

Each row is one failed authentication attempt. Scan the results for patterns:

Pattern 1 — Single IP, many users: If one IPAddress appears targeting 10+ UserPrincipalNames with ResultType 50126 (wrong password): password spray. This is the DE3-003 detection from the Detection Engineering course — built from this exact query pattern.

Pattern 2 — Single user, many failures: If one UserPrincipalName has 20+ failures from one IP with ResultType 50126: brute force against that specific account.

Pattern 3 — MFA denial cluster: ResultType 500121 (MFA denied) for one user from multiple IPs: the attacker has valid credentials but cannot pass MFA. They may attempt MFA fatigue (DE4-002).

You just performed your first threat investigation using KQL. The entire process — from blank editor to pattern identification — took under 2 minutes.

Troubleshooting

“The query returns no results.” Check the time range. The default is 24 hours. If your workspace has low volume, expand: where TimeGenerated > ago(7d). Also verify: is the table name correct? (SigninLogs, not SignInLogs — capitalisation matters for some tables.)

“The query takes more than 30 seconds.” Your time range is too wide or you are missing the initial where TimeGenerated filter. Always filter by time FIRST — this is the single most important performance pattern in KQL. Every query should start with a time filter.

Beyond the first query: three immediate variations

The 4-operator pattern is a template. Here are three variations that answer different investigation questions using the same structure:

Variation 1 — Who is authenticating from outside the UK?

1
2
3
4
5
6

SigninLogs
| where TimeGenerated > ago(24h)
| where Location !startswith "GB"
| where ResultType == "0"
| project TimeGenerated, UserPrincipalName, IPAddress, Location, AppDisplayName
| sort by TimeGenerated desc

One change: the where filter now targets Location instead of ResultType. Same 4-operator pattern, different investigation question. This query identifies users successfully authenticating from non-UK locations — useful for identifying VPN users, travelling employees, or potentially compromised accounts.

Variation 2 — What processes ran on a specific device?

1
2
3
4
5

DeviceProcessEvents
| where TimeGenerated > ago(1h)
| where DeviceName == "WS-NGE-BRS-MORRISON01"
| project TimeGenerated, FileName, ProcessCommandLine, AccountName, InitiatingProcessFileName
| sort by TimeGenerated desc

Same pattern, different table. DeviceProcessEvents instead of SigninLogs. The where filter targets a specific device instead of a result type. The project shows process-relevant fields instead of authentication-relevant fields. The PATTERN is identical — only the table, filter, and columns change.

Variation 3 — What inbox rules were created today?

1
2
3
4
5

OfficeActivity
| where TimeGenerated > ago(24h)
| where Operation == "New-InboxRule"
| project TimeGenerated, UserId, OfficeObjectId, ClientIP, ResultStatus
| sort by TimeGenerated desc

Third table, third investigation question, same 4-operator pattern. This query finds inbox rule creation — the persistence mechanism used in the CHAIN-HARVEST BEC attack. If any results appear: investigate the rule details in OfficeObjectId and the source IP in ClientIP.

These three variations demonstrate the pattern’s universality: once you know table → where → project → sort, you can investigate authentication, endpoint activity, and email operations. The only knowledge you need is: which TABLE contains the data, which FIELD to filter, and which COLUMNS to display.

The pipe operator: thinking in transformations

The pipe | is the most important character in KQL. It transforms a query from “get everything” to “get exactly what I need.”

Without pipes, you would need to express the entire query as a single statement — specifying the table, filters, columns, and sorting in one declaration (as SQL does with SELECT…FROM…WHERE…ORDER BY). KQL’s pipe model lets you think in STEPS: “start with all sign-in data… now keep only failures… now show only these columns… now sort by time.”

This step-by-step thinking mirrors how analysts naturally investigate: “Show me the sign-ins. OK, too many. Show me only the failures. Better. Now show me just the user, IP, and failure reason. Good — now sort by time so I see the most recent first.”

Each pipe reduces the data: 45,000 rows → 80 rows → 80 rows with 5 columns → 80 rows sorted. The result is precise, readable, and fast.

Common first-query mistakes and how to fix them

Mistake 1 — Forgetting the time filter. The learner types SigninLogs | where ResultType != "0" and the query scans the entire retention period. Fix: always start with where TimeGenerated > ago(1h). Rachel’s rule: develop against 1 hour, investigate against 24 hours, baseline against 30-90 days.

Mistake 2 — Wrong ResultType value. The learner filters for ResultType == "50126" (wrong password) when they meant ResultType == "500121" (MFA denied). There are 50+ distinct failure codes. Fix: run SigninLogs | where ResultType != "0" | summarize count() by ResultType, ResultDescription | sort by count_ desc to see ALL failure types in your environment.

Mistake 3 — Case-sensitive comparison. The learner filters for AppDisplayName == "azure portal" (lowercase). The actual value is “Azure Portal” (title case). KQL == is case-SENSITIVE. Fix: use =~ for case-insensitive comparison or has for substring matching.

Mistake 4 — Projecting non-existent fields. The learner types | project Username, SourceIP but SigninLogs uses UserPrincipalName and IPAddress. Fix: run SigninLogs | getschema to see actual field names.

The query editor: keyboard shortcuts that matter

The Sentinel Logs blade query editor supports shortcuts that accelerate daily work. Shift+Enter runs the query. Ctrl+/ comments or uncomments the current line — useful for temporarily disabling a where filter during debugging. Ctrl+Shift+F formats the query with consistent indentation. Ctrl+D duplicates the current line — useful when building multiple similar where clauses. Ctrl+Space opens IntelliSense for field name autocomplete, eliminating the need to memorise exact column names. Learn these five shortcuts before writing your second query. They save cumulative hours over weeks of investigation work. Rachel estimates she runs 40-60 KQL queries per day during active investigations — 5 seconds saved per query multiplied by 50 queries is 4 minutes per day, or 80 minutes per month.