Purpose of This Lesson

KQL is most useful when an analyst can reduce a large result set to the exact columns, records, and time ranges needed for an investigation. This lesson focuses on shaping query output and calculating values from existing event data in Microsoft Defender advanced hunting or Microsoft Sentinel Log Analytics.

The key skills are:

• Calculating an event’s age relative to the current time
• Selecting only relevant columns
• Filtering records by event ID, IP address patterns, or text
• Sorting results in ascending or descending order
• Limiting the result set after sorting
• Creating a derived hour column from an event timestamp
• Using arithmetic operators with numeric values

The exact rows returned by a query depend on the tenant’s data, selected time range, schema, and retention. For exam purposes, understanding the operator sequence is more important than memorizing sample output.

Key Concepts

extend: Create a calculated or derived column

extend adds a new column to each matching record without removing the existing columns. It is useful for calculations and for making data easier to group or inspect.

SecurityEvent
| extend EventAge = now() - TimeGenerated

This calculates the elapsed time between the current time and each event’s TimeGenerated value. The result is a timespan, so it represents the event’s age rather than a simple numeric count.

A derived column can also extract part of a timestamp:

SecurityEvent
| extend EventHour = format_datetime(TimeGenerated, "HH")

EventHour contains the hour portion of the event timestamp. This can help an analyst identify activity concentrated during unusual hours or prepare data for further aggregation.

project: Select the columns returned

project controls which columns appear in the output. It does not primarily filter records; it filters the displayed fields.

SecurityEvent
| project TimeGenerated

A practical investigation query often projects only the fields needed for review:

SecurityEvent
| project TimeGenerated, Computer, Account, EventID

This makes results easier to read and reduces unnecessary output. Do not confuse project with where: where chooses rows, while project chooses columns.

where: Filter records

where keeps only records that satisfy a condition. For example, Windows Security event ID 4624 represents a successful logon event:

SecurityEvent
| where EventID == 4624

Text and pattern filters are also useful:

SecurityEvent
| where IPAddress startswith "10.1"
| project IPAddress

SecurityEvent
| where IPAddress endswith "1"

SecurityEvent
| where CommandLine contains "PowerShell"

contains looks for the specified text anywhere in the value. It is therefore broader than a prefix or suffix test. In operational use, consider whether case sensitivity and exact matching matter for the investigation; KQL provides additional string operators when a more precise match is required.

order by: Sort records

order by sorts the result set using a specified column. The direction is either ascending (asc) or descending (desc).

To identify the earliest successful logons in the selected data:

SecurityEvent
| where EventID == 4624
| order by TimeGenerated asc
| take 10

To see the newest events first, use desc instead. Sorting is especially important when combined with take; otherwise, the returned records are not necessarily the earliest or latest ones you intended to retrieve.

take: Limit the number of rows

take returns a specified number of records:

| take 10

The order of operations matters. order by TimeGenerated asc | take 10 returns the ten earliest records according to that sort. Using take 10 before sorting can produce an arbitrary subset and then sort only that subset.

Arithmetic operators

KQL supports arithmetic such as addition, subtraction, multiplication, and division when the data types are compatible. A common time calculation is subtraction of a timestamp from now():

| extend EventAge = now() - TimeGenerated

When adding or subtracting time intervals, use KQL’s supported time-span expressions and verify the resulting data type. A timestamp, a timespan, and a numeric value are not interchangeable.

Microsoft Security Operations Context

A SOC analyst usually begins with broad telemetry and then narrows it through a sequence of questions:

1. Which records are relevant to the investigation?
2. Which fields are needed to make a decision?
3. Should the records be ordered chronologically or by recency?
4. Is a derived value, such as event age or hour of day, useful?
5. How many results can be reviewed safely and efficiently?

In Microsoft Defender XDR advanced hunting, the same query-shaping principles apply to tables such as DeviceProcessEvents, EmailEvents, and identity-related telemetry, although the available columns differ. In Microsoft Sentinel, the query runs against tables in the Log Analytics workspace, such as SecurityEvent, subject to connector configuration, ingestion, and retention.

A successful query is not automatically a complete investigation. A filtered SecurityEvent result may need to be correlated with the user, device, source IP, process, alert, incident, and surrounding activity. Projecting too few columns can remove evidence needed for triage, so use minimal output for focused review but retain relevant investigation fields when documenting or escalating an incident.

Exam-Relevant Takeaways

• extend creates a calculated or derived column while retaining the original columns.
• project selects the columns displayed in the result.
• where filters rows based on a condition.
• order by ... asc sorts from earliest/smallest to latest/largest; desc reverses the order.
• take limits the number of returned rows.
• To retrieve the earliest ten matching events, sort ascending first and then apply take 10.
• now() - TimeGenerated calculates event age as a time span.
• format_datetime() can derive a formatted hour or other time component from a timestamp.
• startswith, endswith, and contains answer different string-matching questions.
• Query results vary with the selected time range and the data actually ingested into the tenant.

Tool / Feature Decision Guide

Investigation need	KQL approach	Why it fits
Keep only records matching an event ID	where EventID == ...	Filters rows without changing the displayed fields
Display only selected fields	project Field1, Field2	Makes results focused and readable
Add an event-age calculation	extend EventAge = now() - TimeGenerated	Creates a time-span value for each record
Extract the event hour	extend EventHour = format_datetime(TimeGenerated, "HH")	Adds a derived time field
Find the oldest matching records	order by TimeGenerated asc followed by take	Sorting must occur before limiting
Find the newest matching records	order by TimeGenerated desc followed by take	Places recent records first
Match a text prefix	startswith	Tests the beginning of a value
Match a text suffix	endswith	Tests the end of a value
Find text anywhere in a field	contains	Searches within the value rather than only at an edge

KQL Notes

KQL queries are evaluated as a pipeline from left to right. Each pipe passes its output to the next operator. A compact example combines filtering, sorting, limiting, and column selection:

SecurityEvent
| where EventID == 4624
| order by TimeGenerated asc
| take 10
| project TimeGenerated, Computer, Account, EventID

This query finds ten of the earliest successful logon events in the selected dataset and displays only four columns.

The query’s time context matters. now() reflects the time at query execution, while TimeGenerated reflects the timestamp recorded for the event. In addition, timestamps are commonly handled in UTC in Microsoft security data, so interpret an extracted hour with the tenant’s time-zone requirements in mind.

Common Exam Traps

• Confusing columns and rows: Use project to choose columns and where to choose records.
• Limiting before sorting: take 10 before order by does not reliably produce the ten earliest or latest events.
• Assuming contains means exact equality: It matches text found within a value, not necessarily the whole value.
• Treating an empty result as a syntax failure: The table may contain no matching data in the selected time range, or the relevant connector may not be ingesting the expected field.
• Ignoring schema differences: A field available in SecurityEvent may have a different name or may not exist in a Defender XDR hunting table.
• Over-projecting during evidence collection: Removing useful fields can make later correlation and escalation harder.
• Interpreting event age as a number without checking the type: Subtracting datetimes produces a time span, not an ordinary integer.
• Assuming sample output is stable: Microsoft training and demo datasets change; the operators and pipeline logic are the durable knowledge.

Real-World SOC Analyst Notes

• Start with a bounded time range to reduce noise, execution time, and investigation cost.
• Use project for an analyst-friendly view, but preserve the original event or record when evidence may be needed for escalation.
• Validate the data source before concluding that no activity occurred. An empty result can indicate missing ingestion, an incorrect table, an incorrect field, or an overly narrow time range.
• When investigating suspicious PowerShell, a simple contains "PowerShell" search is a starting point, not a verdict. Correlate the command line with the account, device, parent process, alert context, and timing.
• Document the query, time range, tenant or workspace, and relevant results in the case record so another analyst can reproduce the decision.
• Be cautious with local-time interpretations. An unusual hour based on UTC may be normal after conversion to the organization’s operating time zone.
• Use precise filters when the result set is large. Broad text matching can create false positives and contribute to alert fatigue.

Quick Reference Summary

// Calculate the age of every event
SecurityEvent
| extend EventAge = now() - TimeGenerated

// Display selected columns
SecurityEvent
| project TimeGenerated, Computer, Account, EventID

// Find the ten earliest successful logons
SecurityEvent
| where EventID == 4624
| order by TimeGenerated asc
| take 10

// Add the event hour
SecurityEvent
| extend EventHour = format_datetime(TimeGenerated, "HH")

// Filter by text or value pattern
SecurityEvent
| where CommandLine contains "PowerShell"
| where IPAddress startswith "10.1"

Remember the operator roles: where filters records, project selects fields, extend calculates fields, order by sorts records, and take limits output.

Flashcards

Q: Which KQL operator should you use when you need to display only TimeGenerated, Computer, and Account while retaining all matching records?

A: Use project TimeGenerated, Computer, Account. project controls the returned columns; it does not filter which rows match.

Q: An analyst needs the ten earliest successful logon events. Which operator sequence is required?

A: Filter with where EventID == 4624, sort with order by TimeGenerated asc, and then use take 10. Sorting must precede limiting.

Q: What does extend EventAge = now() - TimeGenerated produce?

A: It adds an EventAge column containing the elapsed time since each event was generated. The result is a time span.

Q: When would you use extend instead of project?

A: Use extend when you need to calculate or derive a new column. Use project when you only need to control which existing or already-defined columns are displayed.

Q: What is the difference between where and project in a KQL pipeline?

A: where filters records based on a predicate, while project selects the fields shown for the records that remain.

Q: Which operator finds PowerShell text appearing anywhere in a command-line value?

A: Use contains, such as CommandLine contains "PowerShell". It searches within the field rather than requiring the value to start or end with that text.

Q: When is startswith a better choice than contains for an IP address filter?

A: Use startswith when the required value must appear at the beginning, such as an address beginning with 10.1. It expresses a narrower condition than searching for the text anywhere.

Q: What does endswith test?

A: It tests whether a field value concludes with the specified text, such as an IP address ending in a particular character sequence.

Q: Which KQL function can derive the hour portion of TimeGenerated?

A: Use format_datetime(TimeGenerated, "HH") in an extend expression. Interpret the resulting hour with the relevant time-zone context.

Q: Why might a syntactically valid IP-address query return no rows?

A: The selected data may contain no matching addresses, the time range may be too narrow, or the field may not be populated by that data source. An empty result is not automatically evidence that the query is wrong.

Q: What is the exam trap when using take 10 with order by?

A: take 10 must follow the sort if the requirement is the ten earliest or latest records. Taking rows first can select an arbitrary subset.

Q: How does order by TimeGenerated desc differ from asc in an investigation?

A: desc places the newest events first, which is useful for recent activity; asc places the oldest events first, which is useful for finding the beginning of a sequence.

Q: Why should an analyst avoid assuming that an empty SecurityEvent result proves no activity occurred?

A: Ingestion configuration, connector health, field population, retention, and the selected time range can all affect visibility. Validate the data source and query scope before drawing a conclusion.

Practice Questions

Question 1

A SOC analyst must return the five most recent successful Windows logon events and show only the timestamp, account, and computer. Which query structure is best?

A. SecurityEvent | take 5 | where EventID == 4624 | project TimeGenerated, Account, Computer

B. SecurityEvent | where EventID == 4624 | order by TimeGenerated desc | take 5 | project TimeGenerated, Account, Computer

C. SecurityEvent | project TimeGenerated, Account, Computer | where EventID == 4624 | take 5

D. SecurityEvent | where EventID == 4624 | project TimeGenerated, Account, Computer | order by TimeGenerated asc | take 5

Correct answer: B

where first identifies successful logons, order by ... desc puts the newest records first, and take 5 then limits the result to the five most recent records. The final project selects the requested fields.

Question 2

An analyst wants to add a field showing how long ago each event occurred. Which expression is appropriate?

A. project EventAge = now() - TimeGenerated

B. extend EventAge = now() - TimeGenerated

C. where EventAge = now() - TimeGenerated

D. order by EventAge = now() - TimeGenerated

Correct answer: B

extend creates a calculated column while keeping the source record available. The subtraction of two datetime values produces an event-age time span.

Question 3

A hunting query must find records where the command-line field contains the word PowerShell anywhere in its value. Which filter should be used?

A. CommandLine startswith "PowerShell"

B. CommandLine endswith "PowerShell"

C. CommandLine contains "PowerShell"

D. CommandLine == "PowerShell"

Correct answer: C

contains searches for the text within the field. startswith and endswith restrict the match to an edge, while equality requires the entire value to match.

Question 4

A query for IP addresses beginning with 10.1 returns no records. What should the analyst do first?

A. Conclude that no internal addresses were used.

B. Replace where with project.

C. Validate the time range, table, field population, and data ingestion.

D. Add take 10 before the filter.

Correct answer: C

A valid query can return an empty set when the data source has no matching records, the field is not populated, or the scope is incorrect. The result should be validated before making a security conclusion.

Question 5

An analyst wants to inspect activity by the hour of the event. Which approach creates an hour value from TimeGenerated?

A. extend EventHour = format_datetime(TimeGenerated, "HH")

B. where EventHour == TimeGenerated

C. project EventHour = now() - TimeGenerated

D. take EventHour

Correct answer: A

format_datetime() derives a formatted component from the timestamp, and extend adds it as a new column. The analyst should also account for UTC versus local time when interpreting the hour.