Summarizing KQL Results and Filtering Time Ranges – SC-200 Study Guide

Study guide

Technical reference and lesson notes

Purpose of This Lesson

Kusto Query Language (KQL) is used in Microsoft Defender portals and Microsoft Sentinel to filter, summarize, and investigate security data. This lesson focuses on two practical analyst skills:

Restricting results to a useful time range.
Turning large event sets into counts, rankings, and other summaries.

These techniques are important during alert triage, threat hunting, incident scoping, and dashboard or workbook development.

Key Concepts

Filtering with `where`

The where operator keeps only records that meet a condition. For example, Windows Security Event ID 4624 represents successful logon activity in the SecurityEvent table:

SecurityEvent
| where EventID == 4624

The double equals operator (==) is used for comparison. It is different from the single equals sign used when assigning an output name in an expression such as summarize TotalLogons = count().

Summarizing with `summarize`

summarize aggregates rows rather than returning every matching event. A total count can be assigned a readable name:

SecurityEvent
| where EventID == 4624
| summarize TotalLogons = count()

To group the count by a field, use by:

SecurityEvent
| summarize EventCount = count() by EventSourceName

This produces one result row per event source. To return only the five largest groups, sort by the generated count and limit the results:

SecurityEvent
| summarize EventCount = count() by EventSourceName
| top 5 by EventCount

Counting distinct values with `dcount`

dcount() estimates the number of unique values in a column. It is useful when the question is about distinct accounts, computers, IP addresses, or other entities rather than the total number of records.

SecurityEvent
| summarize UniqueAccounts = dcount(Account) by Computer

This returns the number of distinct accounts observed for each computer. It does not list the accounts themselves.

Adding a constant classification with `extend`

extend creates a calculated column. A constant value can be used to label a sample of records:

SecurityEvent
| take 10
| extend LogType = "Security"

Every returned row receives the value Security in the new LogType column. The value is analyst-defined and does not automatically identify the underlying log source.

take is especially useful while testing a query. Applying it before an expensive operation can reduce the amount of data processed and help avoid unnecessary resource consumption.

Microsoft Security Operations Context

In a SOC, raw event output is often too large to review manually. KQL aggregation helps answer scoping questions quickly:

How many successful logons occurred during the investigation window?
Which event sources generated the most activity?
How many distinct accounts were observed on a computer?
Is activity concentrated on one host or distributed across many hosts?

The same reasoning applies across Microsoft security products. Defender XDR advanced hunting uses KQL over security telemetry, while Sentinel uses KQL over Log Analytics tables. The exact table and column names vary, so analysts must verify the schema before adapting a query.

Time selection is also operationally significant. A portal time picker limits the query’s search window, while a time predicate in the query explicitly defines the investigation period. When a query contains its own time filter, the interface may indicate that the time range is set in the query.

KQL Notes

Relative time with `ago()`

ago() creates a time value relative to the current time. This is useful for repeatable hunting queries:

SecurityEvent
| where TimeGenerated >= ago(100h)

For the last 24 hours, use ago(24h). Relative filters move automatically as time passes, which makes them suitable for scheduled or routinely repeated investigations.

Absolute time with `between`

Use between when the investigation requires fixed start and end points:

SecurityEvent
| where TimeGenerated between (datetime(2021-01-01) .. datetime(2029-01-01))

The two periods (..) separate the start and end values. Fixed ranges are useful for post-incident review, audit work, and comparing a known historical period.

Time range decision guide

Requirement	Suitable approach	Why
Quickly adjust a portal search window	Portal time picker	Convenient for interactive exploration
Make a hunting query automatically follow the current time	`TimeGenerated >= ago(...)`	The range moves with the current time
Reproduce an investigation for a defined period	`between (datetime(...) .. datetime(...))`	The boundaries are explicit and repeatable
Avoid processing an unnecessarily large dataset while testing	Narrow time filter plus `take`	Reduces query scope and resource use

Query order matters

KQL operators are evaluated as a pipeline. Filter early when possible, then summarize or enrich the smaller result set:

SecurityEvent
| where TimeGenerated >= ago(24h)
| where EventID == 4624
| summarize TotalLogons = count()

A query that extends or summarizes an entire large table can consume excessive resources. A broad time range may also produce misleading results if the analyst is trying to answer a short-term incident question.

Exam-Relevant Takeaways

where filters rows; it does not aggregate them.
== tests equality in a filter condition.
summarize produces aggregate results such as counts.
count() counts records; dcount(Column) counts distinct values.
by Column groups an aggregate by the selected field.
top N by Column limits the output to the highest-ranked values.
ago() creates a relative time boundary.
between uses explicit start and end datetimes separated by ...
extend adds a calculated column but does not modify the source table.
take limits returned rows and is useful for query testing.
A query’s time predicate can override or define the effective investigation range independently of the portal picker.

Common Exam Traps

Confusing count() and dcount(): count() counts event rows, while dcount(Account) counts unique account values.
Using assignment syntax in where: A filter uses ==; an aggregate alias uses a single =.
Assuming extend changes stored data: It only adds a column to the query result.
Treating a label as authoritative: extend LogType = "Security" creates a custom label; it does not validate the log source.
Forgetting the time scope: A correct query over the wrong time period can produce an incorrect incident conclusion.
Reading summarize ... by as a complete event list: Aggregation returns grouped results, not the original records.
Running an unrestricted query during testing: Large tables can increase wait time and resource consumption.

Real-World SOC Analyst Notes

Start with the narrowest defensible time window, then expand it if the evidence indicates earlier or later activity.
Preserve the original time range, query text, and UTC assumptions in investigation notes so another analyst can reproduce the result.
Use take while validating syntax and column names, but do not mistake a sample for a complete investigation.
A count is a lead, not proof of malicious activity. High-volume legitimate systems may dominate a top result.
When counting distinct accounts, validate whether the column contains service accounts, machine accounts, or normalized identities that could affect interpretation.
If a query consumes excessive resources, narrow the time range, filter earlier, select only needed columns, or test against a small sample before broadening it.
Avoid presenting an analyst-created extend label as evidence of provenance. Document how the label was generated.

Quick Reference Summary

KQL element	Purpose
`where`	Filter records
`==`	Compare values for equality
`summarize`	Aggregate records
`count()`	Count rows
`dcount(Column)`	Count distinct values
`by Column`	Group an aggregate
`top N by Column`	Return the highest-ranked N results
`ago(24h)`	Relative time boundary
`between (start .. end)`	Fixed time interval
`extend`	Add a calculated or constant column
`take N`	Return a limited sample of rows

Flashcards

Q: An analyst needs the number of successful Windows logons in the current investigation window. Which KQL pattern should be used?
A: Filter EventID == 4624, then use summarize TotalLogons = count(). The filter identifies the event type and count() counts matching rows.

Q: When should an analyst use dcount(Account) instead of count()?
A: Use dcount(Account) when the question concerns unique accounts rather than the total number of event records. Multiple events from one account should contribute one distinct value.

Q: What does summarize EventCount = count() by EventSourceName return?
A: It returns one row per event source with the number of records associated with that source. It is an aggregate view, not a list of individual events.

Q: How would you identify the five event sources with the highest activity?
A: Summarize the count by EventSourceName, then use top 5 by EventCount. The alias must match the count column created by summarize.

Q: When is ago() preferable to a fixed datetime range?
A: Use ago() for repeatable searches such as the last 24 hours, because the boundary moves with the current time. Use fixed datetimes when the investigation must be reproducible for a historical period.

Q: What is the purpose of between (datetime(...) .. datetime(...))?
A: It restricts a timestamp column to an explicit start and end range. The two periods separate the boundaries.

Q: What is the equality syntax trap when writing a KQL filter?
A: A where comparison uses ==, such as where EventID == 4624. A single = is used to assign an alias or calculated expression, such as Total = count().

Q: An unrestricted query is consuming excessive resources while being developed. What should the analyst do first?
A: Narrow the time range and test with a small take value, while applying selective filters as early as practical. This reduces the data processed during development.

Q: What does extend LogType = "Security" do?
A: It adds a result column named LogType containing the constant value Security for each returned row. It does not change the underlying table or prove the record’s provenance.

Q: Why can the portal time picker and a KQL time predicate lead to different apparent time-range states?
A: The portal picker controls the interactive query window, while a timestamp predicate in the query defines a range explicitly. The interface can indicate that the effective range is set in the query.

Q: What is the operational difference between take 10 and summarize count()?
A: take 10 returns a sample of up to ten records, whereas summarize count() returns an aggregate total. A sample is useful for inspection but cannot establish the complete event volume.

Q: A query reports six distinct accounts on one computer. What does that result not tell you?
A: It does not identify which six accounts were observed, nor does it establish that the activity was malicious. Additional projection, filtering, and investigation are required.

Practice Questions

Question 1

A SOC analyst wants a query that always examines successful logons from the previous 24 hours, regardless of when it is run. Which approach is best?

A. Use EventID = 4624 without a time filter

B. Use TimeGenerated between (datetime(2021-01-01) .. datetime(2029-01-01))

C. Use where TimeGenerated >= ago(24h) and filter EventID == 4624

D. Use extend TimeGenerated = "24 hours"

Correct answer: C

ago(24h) creates a rolling time boundary, and == correctly compares the event ID. The other choices either omit the time scope, use a fixed historical range, or create a meaningless calculated column.

Question 2

An analyst must determine how many different user accounts authenticated to each computer. Which query pattern is appropriate?

A. summarize EventCount = count() by Computer

B. summarize UniqueAccounts = dcount(Account) by Computer

C. top 5 by Account

D. extend UniqueAccounts = "Account"

Correct answer: B

dcount(Account) counts distinct account values and by Computer produces a result for each computer. A normal count() would count events, not unique accounts.

Question 3

A query returns too many rows and the analyst wants the five event sources with the greatest number of records. Which sequence should be used?

A. summarize EventCount = count() by EventSourceName | top 5 by EventCount

B. extend EventCount = count() | take 5

C. where EventSourceName == top 5

D. summarize EventSourceName = dcount(EventCount)

Correct answer: A

The query first groups and counts events by source, then ranks the generated count column and limits the output. The other options do not perform the required aggregation and ranking.

Question 4

During query development, an analyst runs extend LogType = "Security" against a large table and receives a resource warning. What is the best immediate improvement?

A. Replace the string with a second equals sign

B. Add take 10 before the extend operation while testing

C. Remove all time filters

D. Use dcount() to label every row

Correct answer: B

Limiting the input with take reduces the rows processed while the analyst validates the query. In production analysis, the analyst should also apply an appropriate time range and filters.

Question 5

An incident report must be reproducible for activity between two known calendar dates. Which time-filter technique is most appropriate?

A. A rolling ago() expression only

B. The portal’s default last-24-hours setting

C. TimeGenerated between (datetime(start) .. datetime(end))

D. extend InvestigationWindow = "historical"