Purpose of This Lesson

KQL variables and combination operators make Microsoft Defender and Microsoft Sentinel queries easier to reuse and more useful for investigation. Instead of repeating the same filter, an analyst can define an intermediate result, combine similar event sets, or correlate related records.

This lesson focuses on three practical techniques:

• Declaring a temporary variable with let
• Stacking results with union
• Correlating rows with join

These techniques are especially valuable when investigating Windows security events such as successful logons (4624) and process creation (4688).

Key Concepts

Temporary variables with let

A KQL variable is declared with let. It exists only for the duration of the query and can be referenced later in that query.

let logonEvents =
    SecurityEvent
    | where EventID == 4624;

logonEvents
| summarize Count = count() by Account;

The variable stores the filtered result. The second part of the query then summarizes that result by account.

Important characteristics:

• The variable is temporary; it does not create a permanent table or saved dataset.
• The declaration normally ends with a semicolon.
• The variable must be declared before it is used.
• A variable can reduce duplication in longer queries and make them easier to maintain.
• The selected time range still affects the results. A correct query can return no rows when the selected period contains no matching events.

union: append similar results

union combines rows from multiple tabular expressions into one result set. It is useful when the goal is to display records from different queries together rather than match records to one another.

union
(
    SecurityEvent
    | where EventID == 4688
    | project Computer, EventID, TimeGenerated, Account
),
(
    SecurityEvent
    | where EventID == 4624
    | project Computer, EventID, TimeGenerated, Account
)

This produces one output containing both process-creation and logon events. The queries should generally project compatible columns when a clean, consistent output is required.

union does not prove that a logon caused a process to start, nor does it match one row to another. It simply appends the selected results.

join: correlate matching rows

join merges rows from a left-side expression and a right-side expression when their specified join keys match.

SecurityEvent
| where EventID == 4688
| join kind=inner
(
    SecurityEvent
    | where EventID == 4624
) on Computer

In this example, process-creation events are joined with logon events when both records have the same Computer value.

join kind=inner returns only records for which a match exists on both sides. The output may contain columns from both inputs, and the resulting rows can be expanded to inspect the contributing events.

A key distinction is that joining on Computer establishes only a same-computer relationship. It does not automatically establish that the events involved the same user, session, process, or time sequence. Stronger investigation logic may require additional keys and time-based constraints.

Microsoft Security Operations Context

KQL is used throughout Microsoft security operations for hunting, investigation, and custom detection work. The exact table and field names depend on the data source and product configuration, but the query design principles remain the same.

A SOC analyst might use these techniques to:

• Define a reusable set of suspicious or relevant events.
• Count activity by account, device, or another entity.
• View multiple event types in a unified timeline.
• Correlate related records to identify activity occurring on the same device.
• Build a query that can later support an investigation or analytics rule.

For Defender XDR and Sentinel investigations, query output should be interpreted alongside alert context, incident entities, timestamps, user identity, device identity, and other available telemetry. A broad same-device join is useful for exploration, but it should not by itself be treated as proof of malicious behavior.

Exam-Relevant Takeaways

• Use let to define a temporary named result that can be reused within the same query.
• End a let declaration with a semicolon before starting the next query expression.
• Use union to append rows from multiple tabular expressions into one result.
• Use join to merge rows based on matching values in one or more columns.
• join kind=inner returns only rows with matches on both sides.
• union and join solve different problems: append versus correlate.
• A query can return no results because of the selected time range, even when the syntax and logic are valid.
• A join key such as Computer may be too broad for high-confidence correlation unless additional conditions are applied.

Tool / Feature Decision Guide

Requirement	Use	Why
Reuse a filtered result later in the same query	let	Creates a temporary named expression without repeating the filter
Display records from two event sets together	union	Appends rows into one result set
Match records from two event sets using a common field	join	Correlates rows based on a specified key
Return only records that match on both sides	join kind=inner	Excludes unmatched rows
Investigate why an apparently valid query is empty	Time-range and data-availability checks	The workspace time window and ingested telemetry control what can be returned

KQL Notes

Variable declaration pattern

let Name = TableName
| where Condition;

Name
| summarize count() by SomeField

The semicolon separates the variable declaration from the expression that consumes it.

union versus join

Think of the operators this way:

• union: “Show me all rows from these sources or filters together.”
• join: “Show me rows that can be matched across these sources using this key.”

When using union, projecting the same logical fields from each branch makes the output easier to read. When using join, choose keys that represent the relationship you are actually trying to prove. A device name alone may be appropriate for an initial hunt, but it can produce many unrelated combinations on a busy endpoint.

Common Exam Traps

• Confusing union with join: union appends rows; it does not match them. join correlates rows using one or more keys.
• Forgetting the let semicolon: The variable declaration must be terminated before the variable is referenced.
• Assuming an empty result means invalid KQL: Check the selected time range and whether the relevant table contains data.
• Treating a same-computer join as causal evidence: Matching on Computer does not establish that a specific logon launched a specific process.
• Ignoring output shape: Joined results can include fields from both sides and may be harder to interpret unless relevant columns are projected.
• Using a broad join without considering volume: Common keys can create large or ambiguous result sets, particularly on heavily used systems.

Real-World SOC Analyst Notes

• Start with a narrow time range and expand it deliberately. This reduces noise and makes it easier to understand whether the query is working.
• Use let for repeated filters, especially when several investigative steps depend on the same event population. Clear variable names improve handoff and incident documentation.
• Use union when building a timeline across event types, then sort or project fields as needed for analyst review.
• Use join cautiously. A join on device alone can produce many matches; consider account, session, process, or time relationships when the investigation requires stronger correlation.
• Preserve the original event context. Projecting only a few fields improves readability, but removing timestamps, device names, accounts, or event identifiers can make triage and escalation harder.
• Before converting a hunting query into a detection, test its result volume and false-positive rate. A query that is useful for exploration may be too broad for an alerting rule.
• Document assumptions in the query or investigation notes, particularly when a correlation is based on a broad key such as Computer.
• If a query returns no results, validate the time range, table availability, field names, and data connector or collection configuration before changing the detection logic.

Quick Reference Summary

• let creates a temporary reusable expression.
• union combines rows from multiple expressions.
• join correlates rows using matching key columns.
• join kind=inner keeps only matches found on both sides.
• Computer-only correlation is exploratory and may be ambiguous.
• Always verify time range and telemetry availability when results are empty.

Flashcards

Q: You need to filter SecurityEvent once and summarize the filtered records several times in the same query. Which KQL feature should you use?

A: Use let to define a temporary named result. It prevents repeated filtering and can be referenced later in the same query.

Q: What does the semicolon accomplish after a let declaration?

A: It terminates the variable declaration so KQL can parse the following expression that uses the variable.

Q: When should you choose union instead of join?

A: Choose union when you want to append records from multiple event sets into one output. It does not require matching rows.

Q: When should you choose join instead of union?

A: Choose join when you need to correlate rows from two expressions using a common key such as Computer.

Q: What does join kind=inner return?

A: It returns only rows that have a matching key value on both the left and right sides of the join.

Q: A query for event ID 4624 returns no data. What should you check before rewriting the query?

A: Check the selected time range and confirm that the relevant table contains ingested data. An empty result does not necessarily indicate invalid KQL.

Q: What is the main limitation of joining process-creation and logon events only on Computer?

A: The match shows that both events occurred on the same computer, but not that they involved the same session, account, or causal sequence.

Q: How can project improve the output of a union query?

A: Projecting compatible, relevant columns from each branch produces a more consistent and readable combined result.

Q: What is the conceptual difference between appending and correlating KQL results?

A: Appending uses union to place rows together; correlating uses join to combine rows when specified key values match.

Q: Why might a join produce unexpectedly large or confusing output?

A: A common key such as a device name may match many records on both sides, creating numerous combinations and ambiguous relationships.

Q: What type of event is Windows event ID 4688 in the lesson’s example?

A: It represents process creation.

Q: What type of event is Windows event ID 4624 in the lesson’s example?

A: It represents a successful logon.

Q: Why should a hunting query be tested before being used for alerting?

A: Hunting queries can be intentionally broad, while alerting logic must control volume and false positives to avoid alert fatigue.

Practice Questions

Question 1

A SOC analyst wants to display successful logon events and process-creation events in one result set for timeline review. The analyst does not need to match individual records. Which operator should be used?

A. join kind=inner

B. union

C. let

D. summarize

Correct answer: B. union

union appends rows from the two event queries. A join would attempt to correlate matching rows, which is not required in this scenario.

Question 2

An analyst defines a filtered set of SecurityEvent records and then wants to reference that same set in a later aggregation in the query. Which construction is appropriate?

A. Define it with let and terminate the declaration with a semicolon.

B. Use union without a second expression.

C. Use join kind=inner without specifying a key.

D. Use project to create a permanent table.

Correct answer: A. Define it with let and terminate the declaration with a semicolon.

let creates a temporary named expression that can be reused within the query. It does not create a permanent table.

Question 3

You need to return only process-creation and logon records that share the same Computer value. Which query design best fits the requirement?

A. Use union with both event filters.

B. Use let to rename both tables.

C. Use join kind=inner and specify on Computer.

D. Use summarize count() without grouping.

Correct answer: C. Use join kind=inner and specify on Computer.

An inner join returns records with matching values on both sides, and on Computer defines the correlation key.

Question 4

A same-computer join returns many combinations of logon and process events. The analyst concludes that every process was launched by every matched logon. What is the problem with this conclusion?

A. join can only be used with identical event IDs.

B. union should always be used for security events.

C. A computer name alone may match unrelated sessions and events.

D. let automatically removes duplicate records.

Correct answer: C. A computer name alone may match unrelated sessions and events.

The join establishes only a shared computer value. Additional identity, session, process, or time relationships may be needed to support a stronger conclusion.

Question 5

A syntactically valid query for event ID 4624 returns no rows in the hunting interface. What is the best first troubleshooting action?

A. Replace union with join.

B. Remove the event ID filter immediately.

C. Check the selected time range and whether the table contains data.

D. Convert the query to a permanent variable.

Correct answer: C. Check the selected time range and whether the table contains data.

The query may be correct but operating over a period with no matching telemetry. Time range and data availability should be validated before changing the query logic.