In this section
Your First Security Query
You know which tables contain security telemetry and which investigation questions each table answers. You may have opened the Sentinel Logs blade and seen the empty query editor. This section eliminates the blank-editor hesitation. You will write, run, and read the results of a real investigation query, then adapt the same pattern to two other tables. Every query in this course builds on the four-operator pattern you learn here.
Scenario
Monday morning at Northgate Engineering. The helpdesk logs three tickets before 9 AM — all account lockouts. Tom Ashworth opens the Sentinel Logs blade. The query editor is blank. The cursor blinks. Tom needs to answer one question before he escalates: are these lockouts caused by users mistyping passwords after a long weekend, or is an attacker spraying credentials across the tenant? The answer is in SigninLogs. The query takes five lines.
The data-flow model
KQL processes data as a pipeline. You start with a table. A source of rows and columns, and pass it through a sequence of operators, each separated by a pipe character (|). Every operator accepts a tabular dataset from the pipe, transforms it in some way, and emits a new tabular dataset to the next operator. The table you start with might contain 45,000 rows. The first where reduces that to 800. The second where reduces it to 30. project keeps five columns out of 40. sort by reorders the 30 rows. The result is a compact, readable answer to your investigation question.
This is the fundamental difference between KQL and a search box. A search box takes a keyword and returns everything that matches. KQL takes a question, formulated as a sequence of transformations, and returns exactly the rows and columns that answer it. Each line narrows the scope. Each pipe passes only the surviving rows forward. Nothing you write in the query editor modifies the underlying data. KQL is read-only by design. You are querying an immutable copy of your organization's security telemetry.
The four-operator pipeline: table reference → where (filter rows) → project (select columns) → sort by (order results). Every query in this course follows this pattern.
Building the query line by line
Open the Sentinel Logs blade. The editor area sits in the center of the page with a table list on the left and the time range selector in the top-right corner. The default time range is "Last 24 hours". The selector controls the outer boundary of your data, but you will override it inside the query itself for precision.
Type the table name and run it:
This returns every row in SigninLogs for the time range, potentially tens of thousands of rows. At Northgate Engineering, that is approximately 45,000 interactive sign-ins per day. The results pane shows rows and columns in a grid, but at this volume it is useless. You cannot visually scan 45,000 rows. The first rule of KQL: always filter before you examine.
Add a time constraint and a result filter. 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. The ago() function calculates a point in time relative to now. ago(1h) means "one hour before the current moment." where ResultType != "0" keeps only failures. ResultType is a string column that stores the Entra ID error code for each sign-in attempt. A value of "0" means success. Every other value represents a specific failure: 50126 is an invalid password, 50053 is a locked account, 500121 is a denied MFA prompt, 50055 is an expired password. At Northgate Engineering, the two where filters reduce 45,000 rows to approximately 30–80 failed sign-ins per hour.
Note that you used two separate where lines rather than combining them with and. Both approaches produce identical results. Separate lines are easier to read, easier to comment out during debugging, and easier to reorder when you want to change the filter priority. Use separate where lines as your default.
Add column selection and sorting to produce the complete investigation query:
project selects which columns appear in the results. Without it, Sentinel returns all 40+ columns in SigninLogs, most irrelevant to this investigation. With project, you see exactly seven fields: when the attempt occurred, which account was targeted, where the request originated, what error code was returned, the human-readable description of that error, which application the attacker targeted, and which geographic location the IP resolved to. sort by TimeGenerated desc places the most recent failures at the top.
Run it. The results appear in under five seconds. The status bar at the bottom of the results pane shows the total row count and execution time. If you see "Completed. Showing results from the last 24 hours" but your query uses ago(1h), your in-query time filter overrides the time selector. The query returns only the last hour regardless of what the selector says. This is intentional. The time selector sets the maximum boundary. Your where TimeGenerated clause sets the actual boundary. Always control time in the query, not the selector.
Reading the output like an analyst
The results grid shows one row per failed sign-in attempt. Each row is a discrete authentication event (one user, one IP, one application, one outcome. Click the expand arrow (>) on any row to see the full record) all 40+ columns, including the ones you excluded with project. This is useful when you spot a suspicious row in the compact view and want the complete context without rewriting the query. The projected view is your triage tool. The expanded view is your investigation detail.
Here is what the output looks like for Northgate Engineering's Monday morning lockout investigation:
Two patterns are visible immediately. The first six rows share a single IP address (185.220.101.34), the same ResultType (50126), and are spaced three seconds apart. Six different users, all failing against the same application, from the same IP, in under a minute. The Location column shows RU, not a country where Northgate has employees. This is a password spray. The attacker is cycling through usernames with one or two common passwords, trying to find an account that does not have a strong password or has not yet been enrolled in MFA.
The bottom two rows are different. Morrison's 10.1.4.22 failure is from an internal IP with a GB location, likely a mistyped password from the Bristol office. Bell's failure is 50055 (expired password), a routine administrative event. These are normal operational failures, not attacks.
Tom can now answer his question with confidence: three of the lockout tickets may be related to the spray (if the attacker tried enough attempts to trigger the lockout threshold), and the helpdesk should reset those accounts after confirming no successful authentications from the attacker's IP range.
Three patterns that separate attacks from noise
Every failed-sign-in investigation produces results that fall into recognizable patterns. Learning to identify them is more important than memorizing the query that generated them.
Pattern 1: Single IP, many users. One IPAddress appears across 10+ different UserPrincipalName values, all with ResultType 50126. This is a password spray. The attacker tries one or two common passwords against many accounts, staying below the per-account lockout threshold. If you see this pattern, investigate immediately, even a 1% success rate against 810 Northgate accounts means 8 compromised credentials.
Pattern 2: Single user, many failures from one IP. One UserPrincipalName shows 20+ failures from one IPAddress, all ResultType 50126. This is a brute force attack. The attacker is cycling through a password list against a known username. The per-account lockout policy should stop this, but check whether the account locked out and whether any attempt succeeded before the lockout triggered.
Pattern 3 (MFA denial cluster. One UserPrincipalName shows failures with ResultType 500121 (MFA denied) from one or more IPAddress values. The attacker has the correct password) they passed the first authentication factor, but MFA blocked the sign-in. If the denials arrive in rapid succession (five or more within ten minutes), the attacker may be attempting MFA fatigue: sending repeated push notifications hoping the user approves one by mistake. This is a high-severity finding. The password is compromised and must be reset regardless of whether MFA held.
Everything else is probably noise. Scattered 50126 failures from internal IPs with GB locations are users mistyping passwords. 50055 (expired password) and 50057 (disabled account) are administrative events. 50058 (user interrupted sign-in) means someone closed the browser tab. These patterns produce volume but not risk.
The performance habit: has versus contains
One performance distinction matters from your very first query. When you need to match a string value, use has instead of contains.
has searches the term index. An internal index that KQL maintains for every string column, tokenized on non-alphanumeric boundaries. It finds whole words in milliseconds because it reads the index, not the raw data. contains performs a substring scan across every character position in every row. Against a table with millions of rows, has completes in milliseconds while contains takes seconds or times out entirely.
The practical rule: if you are looking for a complete word or phrase, use has. Searching for "Teams" in AppDisplayName? has "Teams" matches "Microsoft Teams" instantly. Searching for "powershell.exe" in a FileName column? has "powershell.exe" finds it through the term index. Reserve contains for genuine substring matches: finding "pass" inside "password" or "bypass." For multiple terms, has_any("Teams", "Outlook", "Exchange") matches rows containing any of those words, and has_all("Teams", "External") matches rows containing both.
This is not an optimization you apply later. It is a habit you build now. Every query you write in this course uses has by default and contains only when substring matching is explicitly required.
Adapting the pattern to other tables
The four-operator pattern works identically against every table. Only the table name, the filter conditions, and the projected columns change. Here is the same structure against DeviceProcessEvents. The table you would query if the password spray succeeded and the attacker gained access to a workstation:
Same four operators, different investigation question. The where filter targets Morrison's workstation by device name. The project selects process execution fields, which executable ran, what command-line arguments it used, which user account launched it, and which parent process initiated the execution. If the spray compromised Morrison's credentials and the attacker authenticated successfully, this query shows what happened next on the endpoint.
And the same pattern against OfficeActivity to investigate BEC persistence. The mailbox manipulation that attackers perform after gaining access to a compromised account:
Third table, third investigation question, same four operators. This query finds inbox rule creation. The persistence mechanism attackers deploy in business email compromise attacks. They create rules that redirect incoming mail to a hidden folder or forward it to an external address, ensuring the compromised user never sees the attacker's conversations. If any results appear, the OfficeObjectId field contains the rule parameters and ClientIP shows where the rule was created from.
Three queries. Three tables. Three different investigation questions. One pattern. The table name tells KQL where to look. The where clauses tell it what you care about. The project tells it which columns to show you. The sort tells it how to order the results. Master this pattern and you can interrogate any table in the workspace.
Common first-query mistakes
Four mistakes appear in almost every analyst's first week with KQL. Knowing them in advance saves hours of debugging.
Case sensitivity in equality. The analyst writes where AppDisplayName == "azure portal" and gets zero results. The actual value stored in the column is "Azure Portal". KQL's == operator is case-sensitive. It compares the exact bytes. Use =~ for case-insensitive equality, or has for term matching (which is case-insensitive by default). The general rule: use == only when you know the exact casing, which is rare for display names and common for error codes.
Missing time filter. The analyst writes SigninLogs | where ResultType != "0" without constraining TimeGenerated. The query scans the entire retention period, potentially 90 days of data, millions of rows. It either runs for minutes and returns an unmanageable result set, or it times out at 10 minutes and returns nothing. Always filter by time first. Develop your queries against one hour. Investigate against 24 hours. Baseline against 30 to 90 days when you need trend data.
Wrong column names. The analyst projects Username and SourceIP, but SigninLogs uses UserPrincipalName and IPAddress. DeviceProcessEvents uses AccountName and DeviceName. Every table defines its own column names and there is no universal standard across tables. Use Ctrl+Space in the query editor for IntelliSense autocomplete, or run TableName | getschema to see every column name and its data type before writing the query.
Unquoted string comparisons. ResultType is a string column, not an integer. where ResultType != 0 (unquoted) performs a numeric comparison against a string field. It either fails silently or returns unexpected results. where ResultType != "0" (quoted) is correct. When in doubt, run getschema to check the column type. String columns require quoted values. Integer and long columns use unquoted numbers. Datetime columns use datetime() or ago() functions.
The analyst runs SigninLogs | project UserPrincipalName, IPAddress (no where clause. The query returns 45,000 rows, takes 20 seconds, and the results are too large to scan visually. The analyst adds filters after the fact, running the same expensive scan repeatedly. The fix is structural: write the where clause first, then add project and sort. Filter first, display second. This is not a performance optimization) it is an investigation discipline. You decide what you are looking for before you look.
KQL Principle
KQL is read-only. You cannot modify, delete, or insert data through queries. The worst outcome of a bad query is a slow result set — cancel it with the Stop button and add tighter filters. The query editor is the safest tool in your security stack. Run queries freely, adjust filters, explore tables. The underlying data is immutable. The only cost of experimentation is time, and you control that with ago().
Get weekly detection and investigation techniques
KQL queries, detection rules, and investigation methods — the same depth as this course, delivered every Tuesday.
No spam. Unsubscribe anytime. ~2,000 security practitioners.