Searching for Information with Basic KQL Syntax – SC-200 Study Guide

Purpose of This Lesson

Basic Kusto Query Language (KQL) syntax is the foundation for investigating security data in Microsoft Sentinel, Log Analytics, and Microsoft Defender hunting experiences. The goal is not to retrieve an entire table blindly, but to narrow large volumes of telemetry to the events, systems, accounts, processes, or conditions relevant to an investigation.

This lesson focuses on table discovery, time ranges, broad searches, field-specific filtering, logical operators, and practical Windows security-event investigation.

Key Concepts

Tables and time ranges

KQL queries run against tables containing structured telemetry. In the demonstration environment, available data is grouped into areas such as Azure Monitor, VM insights, Azure resources, container insights, Log Management, Microsoft Sentinel, security and audit data, service maps, custom logs, and restored logs.

A query is constrained by the selected time range. An otherwise valid query can return no results when:

• The selected period contains no matching events.
• The demonstration or production data has changed.
• The relevant connector or data source was not collecting data during that period.
• The query is running against the wrong table.

When troubleshooting an empty result set, verify the table and time range before assuming that the event does not exist.

The pipe operator

The pipe character (|) passes the results of one operation to the next. A typical KQL investigation starts with a table and then adds filtering or projection operations:

SecurityEvent
| where EventID == 4624

This structure makes it possible to build a query incrementally and inspect the results at each stage.

search versus where

search performs a broad text search across columns in a table. It can be useful when exploring unfamiliar data or looking for a value whose field is not yet known, but it can be expensive and produce noisy results in a large environment.

where filters records using an explicit condition. It is generally the better choice for repeatable investigations and detection logic because it targets known fields and reduces the result set more efficiently.

Examples:

SecurityEvent
| search "10"

SecurityEvent
| where Computer == "retail-vm-01"

A broad search for a value such as 10 may match many unrelated columns. A field-specific condition is more precise when the investigative question is known.

Equality and field filtering

The equality operator is written as ==, not a single equals sign. Common fields demonstrated in the lesson include:

• EventID — Windows event identifier.
• Computer — system associated with the event.
• AccountType — for example, machine or user.
• Process — process associated with the event.

String values are placed in quotation marks, while numeric event IDs are not:

SecurityEvent
| where AccountType == "User"

SecurityEvent
| where Process == "PowerShell.exe"

KQL field names and values must be entered accurately. Use IntelliSense when available to discover fields and reduce typing errors, but still verify the generated field name and expected value.

Combining conditions

Use and when every condition must be true:

SecurityEvent
| where EventID == 4624
| where Computer == "retail-vm-01"

The same logic can be expressed in one condition:

SecurityEvent
| where EventID == 4624 and Computer == "retail-vm-01"

Use or when either condition is acceptable. Parentheses are important when combining and and or, because they make the intended logic explicit:

SecurityEvent
| where (EventID == 4624 or EventID == 4625)
    and Computer == "retail-vm-01"

This returns successful logon events (4624) or failed logon events (4625) for the specified computer. Without the grouping, the query may not apply the computer restriction to both event IDs as intended.

Microsoft Security Operations Context

The same KQL principles apply across several Microsoft security workflows:

• Microsoft Sentinel: Query Log Analytics tables for incident investigation, hunting, analytics-rule development, and workbook visualizations.
• Microsoft Defender XDR: Use advanced hunting to investigate signals across Microsoft security products. The schema differs from Sentinel tables, so the correct table and column names must be confirmed.
• Windows security investigation: Use security-event data to examine successful logons, failed logons, account types, systems, and process activity.
• Threat hunting: Start with a hypothesis, select the narrowest relevant table and time range, and progressively add conditions.

For example, a SOC analyst investigating suspicious PowerShell use might first search the relevant process field, then add a host, user, time range, or event type to reduce noise. A successful or failed logon investigation similarly benefits from filtering by event ID and system rather than scanning every event in the table.

The table name and schema are product- and data-source-specific. A query written for the SecurityEvent table cannot automatically be assumed to work against a Defender advanced hunting table with different column names.

KQL Notes

Start broad only for discovery

A broad query can help determine whether a table contains relevant data:

SecurityEvent

However, returning an entire table is inefficient in a large tenant. After confirming the table, add a time range in the interface and field-specific filters.

Search for a value across a table

SecurityEvent
| search "10.2"

This checks all columns for the value. It is useful when the field is unknown, but it does not prove that the value is an IP address or that every match is relevant.

Filter successful logons

SecurityEvent
| where EventID == 4624

Windows event ID 4624 represents a successful logon. The event details still need to be examined to determine the account, logon type, source, target system, and whether the activity is expected.

Filter failed logons

SecurityEvent
| where EventID == 4625

Event ID 4625 represents a failed logon. Repeated failures may be relevant to password spraying or brute-force investigations, but the event must be correlated with identity, source, timing, and baseline information.

Combine event and host filters

SecurityEvent
| where EventID == 4624 and Computer == "retail-vm-01"

Use the actual host value returned by the environment. Demonstration data is periodically changed, so a host name shown in an example may not exist when the query is run.

Search for PowerShell activity

SecurityEvent
| where Process == "PowerShell.exe"

Process filtering identifies candidate events; it does not by itself establish malicious behavior. Inspect command-line data and correlate with the user, host, parent process, execution time, and related alerts.

Exam-Relevant Takeaways

• | chains KQL operators together.
• search performs broad full-text matching across columns; where applies a targeted condition.
• Use == for equality.
• Put string values in quotation marks and use the correct field data type.
• Use and when all conditions must match.
• Use or when either condition can match.
• Use parentheses to control and communicate mixed and/or logic.
• Event ID 4624 indicates a successful Windows logon.
• Event ID 4625 indicates a failed Windows logon.
• A query returning no rows may reflect the selected time range, unavailable data, an incorrect table, or a mismatched value.
• IntelliSense helps discover fields, but KQL remains sensitive to accurate field names and syntax.
• The demonstration environment is not static; query results and host names can change.

Tool / Feature Decision Guide

Investigation need	Recommended approach	Why
Explore an unfamiliar table or locate an unknown value	search	Broadly searches table columns during initial discovery.
Filter known event, host, account, or process fields	where	More precise and generally more efficient than a table-wide search.
Investigate successful Windows logons	where EventID == 4624	Targets the relevant Windows security event.
Investigate failed Windows logons	where EventID == 4625	Targets failed authentication events.
Find either successful or failed logons on one host	Parenthesized or conditions combined with and	Preserves the intended logical scope.
Validate whether a query should return data	Expand the time range and verify the table	Empty results do not always mean the activity is absent.
Discover available columns	Use the table browser and IntelliSense	Reduces schema and spelling errors before building a query.

Common Exam Traps

• Confusing = with ==: KQL equality comparisons use ==.
• Using search when a field is known: This can create unnecessary noise and poor performance.
• Misreading an empty result: Check the time range, table, connector, and exact value first.
• Ignoring parentheses: A or B and C may not express the intended “either A or B, both restricted by C” logic. Group the alternatives explicitly.
• Treating event IDs as conclusions: A 4624 event indicates a successful logon, but not that the logon was legitimate.
• Assuming schemas are interchangeable: Sentinel and Defender advanced hunting use different tables and may use different column names.
• Relying on demo host names: Sample environments can change their data and systems.
• Searching for an IP as an arbitrary string: A broad match may find the value in unrelated fields; target the appropriate column when known.

Real-World SOC Analyst Notes

• Begin with a narrow time range appropriate to the alert or reported activity, then expand only when necessary.
• Preserve the original query, time range, table, and UTC interpretation in the investigation notes so another analyst can reproduce the result.
• Use broad searches for orientation, not as the final detection or hunting query.
• When investigating authentication, correlate successful and failed logons with the account, source host or address, target system, logon type, and normal user behavior.
• PowerShell activity is common in administration and automation. Treat the process match as a pivot point, then inspect command-line content and surrounding telemetry.
• Before operationalizing a query as an analytics rule, test its selectivity and false-positive rate against representative data.
• Confirm that the required data connector is enabled and that retention covers the investigation period. A technically correct query cannot retrieve data that was never ingested or is no longer retained.
• Avoid making containment decisions from a single generic event. Preserve evidence and coordinate with identity, endpoint, or infrastructure teams when the activity may affect a production system.

Quick Reference Summary

Table                  Choose the data source to query
Time range             Defines which records are available to the query
|                      Pipes results into the next operator
search                 Broad full-text search across columns
where                  Targeted filtering by conditions
==                     Equality comparison
and                    All conditions must be true
or                     Either condition may be true
4624                   Successful Windows logon
4625                   Failed Windows logon

A practical workflow is: select the correct table, set an appropriate time range, run a small discovery query, replace broad searches with where filters, group mixed Boolean logic with parentheses, and validate the results in their security context.

Flashcards

Q: When should a SOC analyst use search instead of where in KQL?
A: Use search for initial exploration when the value or column is unknown. Use where for targeted, repeatable filtering once the relevant field is known.

Q: What does the KQL pipe operator do?
A: It passes the output of one table or operator to the next operation, allowing a query to be built as a sequence of steps.

Q: A query for EventID == 4624 returns no rows. What should be checked first?
A: Verify the selected time range, table, data connector, exact field name, and data availability before concluding that no successful logons occurred.

Q: Which KQL operator represents equality?
A: == represents equality. A single = is not the normal equality comparison syntax used in the lesson.

Q: What is the difference between Windows event IDs 4624 and 4625?
A: Event ID 4624 represents a successful logon, while 4625 represents a failed logon.

Q: How would you find successful logons on one specific computer?
A: Filter both the event and host fields, such as where EventID == 4624 and Computer == "host-name". Use the actual field values present in the target table.

Q: Why should a broad search for an IP address be replaced with a field-specific filter when possible?
A: A broad search checks all columns and may return unrelated matches. Filtering the known address field improves precision and usually reduces processing and review effort.

Q: What is the purpose of parentheses in a query that finds event 4624 or 4625 on one host?
A: Parentheses group the alternative event IDs so the host condition applies to both, for example (EventID == 4624 or EventID == 4625) and Computer == "host-name".

Q: What does where Process == "PowerShell.exe" identify?
A: It identifies records whose process field matches PowerShell.exe. It is a starting point for investigation, not proof that the PowerShell activity is malicious.

Q: Why can the same KQL query produce different results in a demonstration environment?
A: The sample data and systems are periodically changed, and the selected time range may contain different records when the query is run.

Q: What is the main performance concern with querying an entire large table?
A: It can process and return excessive data, increasing query cost, latency, and analyst noise. Apply an appropriate time range and targeted filters.

Q: How does IntelliSense help when writing KQL?
A: It exposes available fields and syntax suggestions, helping discover the schema and reduce typing or spelling errors. The analyst must still validate the field and value.

Q: Why is a 4624 event not sufficient to declare a compromise?
A: It only establishes that a logon succeeded. The account, source, target, logon type, timing, and related endpoint or identity evidence determine whether it is suspicious.

Practice Questions

Question 1

An analyst knows the relevant field is Computer and needs successful logons from server-01. Which query is the best choice?

A. SecurityEvent | search "server-01"

B. SecurityEvent | where EventID = 4624 and Computer = "server-01"

C. SecurityEvent | where EventID == 4624 and Computer == "server-01"

D. SecurityEvent | search EventID == 4624

Correct answer: C

Explanation: The query uses the known fields, the correct equality operator ==, and both required conditions. Option A is broader than necessary, while B uses incorrect equality syntax for this lesson.

Question 2

A SOC analyst needs both successful and failed logons, but only from server-01. Which logic is safest?

A. EventID == 4624 or EventID == 4625 and Computer == "server-01"

B. (EventID == 4624 or EventID == 4625) and Computer == "server-01"

C. EventID == 4624 and EventID == 4625 and Computer == "server-01"

D. search "4624 4625 server-01"

Correct answer: B

Explanation: Parentheses group the two acceptable event IDs before applying the host restriction. Option C requires one record to have two different event IDs simultaneously.

Question 3

A query against SecurityEvent returns no results for a known event. What is the most appropriate initial troubleshooting action?

A. Assume the event did not occur.

B. Remove all filters and permanently increase retention.

C. Verify the table, selected time range, connector, and exact field/value syntax.

D. Replace where with a destructive remediation action.

Correct answer: C

Explanation: Empty results can be caused by scope, ingestion, schema, or data-availability issues. These should be validated before drawing an investigative conclusion.

Question 4

An analyst is exploring an unfamiliar table and knows only that a suspicious value contains 10.2. Which approach is most suitable for the first exploratory step?

A. Use search "10.2", then move to a field-specific where filter when the relevant column is identified.

B. Query the entire table repeatedly without a time range.

C. Use where Process == "10.2" regardless of the schema.

D. Treat every returned record as an IP-address match.

Correct answer: A

Explanation: A broad search is appropriate for discovery when the column is unknown. Results must then be validated and narrowed because the value may appear in multiple unrelated columns.

Question 5

A PowerShell process match is found during an investigation. What is the best next step?

A. Immediately classify the host as compromised.

B. Ignore the event because PowerShell is a legitimate Windows tool.

C. Examine command-line details and correlate the user, host, timing, parent process, and related telemetry.

D. Search only for successful logons and close the investigation.

Correct answer: C

Explanation: PowerShell is used for both administration and attacks. The process match is a pivot for contextual investigation, not a final verdict.