KQL Interview Questions: Time Windows, Joins, and Incident Investigation

A service owner asks whether a spike in failed sign-ins belongs to one incident or several unrelated retries. Your query returns a convincing chart, but its time boundaries overlap and its join multiplies events. The difficult part of KQL interviewing is often deciding what a result means before making it faster.

For KQL interview questions, establish the event schema, use explicit time boundaries, choose the intended join flavor, and preserve enough evidence to explain every aggregate. Kusto Query Language is a pipeline for filtering and analyzing records; a correct answer connects each operator to the investigation's unit of analysis.

This guide uses original log fixtures for analysts, data engineers, and incident responders. The tables and fields are illustrative, not guaranteed schemas in Azure Monitor or Microsoft Sentinel.

What should you clarify before writing KQL?

Ask which timestamp represents the incident: occurrence time, collection time, or ingestion time. Then identify the event key, user identity, host identity, and outcome field. An analyst who counts log records when the question asks for unique attempts can produce an accurate query for the wrong quantity.

In the running example, AuthEvents contains Timestamp, EventId, UserId, HostId, and Result. We assume timestamps are UTC and Result uses the string Failure. State those assumptions; real connectors can use different names, types, and outcome codes.

Also clarify the question's scope. "Find failures" is a retrieval task. "Find users with unusual failure rates" needs a denominator and a baseline. "Prove compromise" requires evidence beyond an authentication failure count. Your initial response should identify the observable result the query can support and leave the larger security conclusion open.

How do you make time windows reproducible?

For a fixed investigation, use explicit endpoints rather than repeatedly querying a moving relative interval. A half-open window includes its start and excludes its end. Adjacent windows can then meet without counting the boundary record twice.

let start = datetime(2026-09-24T10:00:00Z);
let stop = datetime(2026-09-24T10:10:00Z);
AuthEvents
| where Timestamp >= start and Timestamp < stop
| where Result == "Failure"
| project Timestamp, EventId, UserId, HostId

In this fixture, an event at 10:00:00 is included and one at 10:10:00 is excluded. The next interval can start at 10:10:00 and include it there. Explain that convention before proposing a scheduled query or comparing counts across adjacent periods.

A relative interval is useful during exploration, but record the resolved investigation period when sharing evidence. Two people running a moving query several minutes apart are not necessarily inspecting the same records. If logs arrive late, identical event-time bounds may still return additional rows later; preserve extraction time as well.

What does a five-minute bin actually represent?

Microsoft documents bin() as rounding values down to a specified bin size. In a time aggregation, it assigns events to fixed buckets. That is different from asking whether any rolling five-minute interval contains enough failures to trigger a rule.

AuthEvents
| where Timestamp >= datetime(2026-09-24T10:00:00Z)
    and Timestamp < datetime(2026-09-24T10:10:00Z)
| where Result == "Failure"
| summarize Failures=count() by bin(Timestamp, 5m)

Suppose the result is eight failures in the 10:00 bucket and twelve in the 10:05 bucket. Those are counts for two aligned periods. They do not establish the maximum over every possible five-minute interval within the investigation.

If a threshold is ten, six events at 10:04:59 and six at 10:05:01 demonstrate the boundary problem. Neither individual aligned bucket reaches ten, yet a short interval crossing the boundary contains twelve. Ask whether the rule requires fixed windows, rolling windows, or overlapping scheduled evaluations before selecting the implementation.

Why can a join turn two failures into six rows?

Imagine two failure records for user U1. An inventory table has three rows for U1 because the user owns three machines. An explicit inner join on UserId creates six matching pairs. That is expected relational behavior, but counting the result as six authentication failures is wrong.

Left event	Matching inventory rows	Rows after inner join
F1 for U1	3	3
F2 for U1	3	3
Total		6

Kusto's default join flavor is innerunique, which deduplicates the left side by the join key. Microsoft documents this behavior; it is not equivalent to preserving all left events. Therefore, specify the flavor and explain the intended cardinality instead of relying on a remembered SQL analogy.

For event enrichment, find the correct entity key or construct one valid inventory row per intended join key first. If the question only asks whether an inventory match exists, consider a membership-oriented join. Always compare event identifiers before and after enrichment to detect accidental expansion or loss.

How do you select the right enrichment record?

"Take the latest inventory row" is tempting, but latest now may not describe the host at incident time. A machine could have changed owner after the failure. Ask whether the source is a current snapshot or a history with validity intervals.

For a current snapshot exercise, reduce inventory to one row per host according to an explicitly stated rule, then left-join failures on HostId. Preserve failures with no match and expose a missing-enrichment indicator. Dropping unmatched records can hide unmanaged machines, precisely the records an investigator might care about.

For historical ownership, require the event timestamp to fall within the ownership interval. Inspect duplicate or overlapping intervals before trusting the join. If the dataset cannot reconstruct historical state, report current ownership as context rather than asserting that the same person owned the host during the event.

The key interview habit is to name the contract: one enrichment row per event, multiple evidence rows per event, or only existence of a match. Different contracts call for different queries and validation checks.

When should you use count versus distinct counts?

count() answers how many records reach the aggregation. A distinct user count answers a different question. Kusto's dcount() is an estimated distinct count; Microsoft describes its accuracy trade-offs and notes that results can vary. Do not silently use an estimate as an exact case inventory.

Suppose 100 failure records belong to ten users and include twenty repeated deliveries of already recorded event identifiers. There are 100 records, ten distinct users, and potentially eighty distinct events if the duplicate assumptions hold. None of these counts can substitute for the others without changing the question.

Before deduplicating, check what EventId means. Is it unique globally, per host, or only within one source stream? A composite key might be necessary. Also define how conflicting duplicates are resolved: two records with the same identifier but different outcomes deserve investigation, not an arbitrary row selection.

For exact evidence collection, retain the underlying identifiers and verify the distinct set using a supported exact method suitable for the dataset size. For broad exploratory dashboards, an approximation may be acceptable when clearly labeled.

How do you investigate a spike without losing context?

Move from aggregate to sample and back. First identify the affected period, service, and outcome. Then inspect representative raw records from the largest contributors and a few quieter groups. A total can be driven by one retrying client, many users, or duplicated ingestion; those have different explanations.

For the eight-and-twelve bucket example, suppose the second bucket's twelve failures all belong to one host. Ask whether that host also had successful attempts, whether its client version changed, and whether its event identifiers repeat. A count alone cannot distinguish a stale credential from a broader attack.

Keep an evidence packet containing fixed bounds, query text, schema assumptions, extraction time, representative event identifiers, and unanswered questions. This is an editorial investigation method, not a product requirement. It makes your result reproducible and gives the next analyst enough context to challenge the conclusion.

How should you handle missing fields and absent results?

An empty result can mean no matching activity, the wrong table, a restrictive filter, missing ingestion, or a schema mismatch. Start with a narrow count and a small sample from the intended source before adding every condition. Confirm that the outcome values actually match the literal being filtered.

Missing identity deserves its own count. If 500 failures contain a user and 200 do not, a per-user chart describes only part of the activity. Decide whether an unknown group should be retained or investigated separately. Do not distribute missing records across users based on speculation.

Likewise, a missing time bucket is not automatically a measured zero. Check source health and expected event cadence before zero-filling a chart. For a system that logs only failures, silence could be normal; for a heartbeat source expected every minute, silence is itself evidence of a collection or service issue.

Explain these alternatives explicitly when the interviewer asks why a dashboard suddenly became quiet.

How do you improve performance without changing meaning?

Microsoft's KQL best-practice guidance emphasizes reducing the data being processed, including selective time and table filters. Apply those reductions early when they preserve the intended result. Keep only the columns needed downstream and avoid scanning unrelated sources merely because a wildcard is convenient.

High-cardinality grouping deserves special attention. Grouping millions of events by a nearly unique identifier creates many groups while offering little aggregation benefit. If the task asks for failures per service, adding event identifiers to the grouping changes both cost and meaning.

Discuss distributed execution hints only after showing the query shape and data sizes. Broadcast or shuffle choices depend on the operands, key distribution, and platform behavior. "Always broadcast the smaller table" is weaker than measuring the actual plan under the intended workload.

Most importantly, compare outputs before and after optimization. Sampling, approximating, dropping unmatched rows, or shortening the time window may improve speed by answering a different question. Label those trade-offs; do not present them as semantics-preserving tuning.

How would you explain a complete incident answer?

Use a short sequence of claims, each with evidence. "There are twenty failure records in the fixed ten-minute interval. Twelve occur in the second aligned bucket. Those twelve belong to one host, but I have not yet established whether they are twelve unique attempts. The inventory join was multiplying rows, so I corrected its cardinality before enrichment."

Then propose the next discriminating query. Check duplicate identifiers, compare successes from the same host, inspect client changes, or verify ingestion health. Avoid an open-ended promise to "investigate further" when a specific uncertainty is already visible.

For practice, have someone change one assumption: identifiers become source-local, timestamps arrive late, or ownership changes mid-window. Rewrite only the parts of your answer affected by that change. This exercise builds the habit that matters most in KQL work: every count, join, and time boundary should have an explanation tied to the question being answered.

Sources and Further Reading

Official Microsoft documentation checked September 24, 2026. Queries use an illustrative schema and were not executed against a live Kusto cluster.

• KQL query best practices: filtering and query-shape guidance.
• Kusto join operator: join flavors and default behavior.
• Kusto bin function: fixed-size binning.
• Kusto dcount function: approximate distinct counts.