In this section
Check My Knowledge
Scenario 1. You run a summarize query against SigninLogs and group by UserPrincipalName and ResultType. The output contains two columns and 847 rows. A colleague asks you to also include the IPAddress for each row. You add IPAddress to the project list after the summarize. The query fails. Why?
The summarize operator limits output to 500 rows by default, and adding a third column exceeds the memory allocation for the result set. You need to add a take 500 before the project to stay within limits. a
Summarize has no 500-row default limit. The issue is structural, not capacity-related. After summarize, the only columns that exist are the by-clause columns and the aggregation columns. Section 1.1 explains the tabular invariant — every operator produces a table, and summarize reshapes the column set to only what you specified.
After summarize, only the by-clause columns and aggregation columns exist. IPAddress was consumed during aggregation and no longer exists in the output table. To include IP addresses, you need to either add IPAddress to the by clause (which changes the grouping granularity) or use make_set(IPAddress) as an aggregation to collect all IPs per group. b
Correct. Section 1.2 explains that summarize reduces the column set to the by-columns and aggregation results. Every other column from the source table is gone. This is the tabular invariant from Section 1.1 in action — the output of summarize is a new table with a different schema than the input. You cannot reference columns that the previous operator did not produce.
Project cannot follow summarize because both operators modify the column set. You need to use extend to add the IPAddress column back into the result set after summarize completes. c
Project can follow summarize — it selects from whatever columns the previous operator produced. The problem is that IPAddress does not exist in the summarize output, so there is nothing to select. Extend creates new computed columns; it cannot resurrect columns that were consumed by a previous operator. Section 1.2 covers the sequential pipeline — each operator works only on what the previous operator produced.
The query engine reserved IPAddress as a system column that cannot appear alongside aggregation results. Use a different column alias like SourceIP to avoid the naming conflict. d
KQL has no reserved system column names that conflict with user queries. IPAddress is a standard column in SigninLogs with no special restrictions. The error is purely structural — the column does not exist after summarize because it was not included in the by clause or an aggregation function.
Scenario 2. You write a detection rule that identifies users with failed sign-ins from more than three countries in one hour. The query works in the Logs blade. After deploying it as a scheduled analytics rule running every five minutes with a five-minute lookback, it catches only about 60% of the incidents you expected. What architectural issue explains the gap?
Scheduled analytics rules query a read-only snapshot of the table taken at the rule's creation time. The rule needs to be deleted and recreated weekly to refresh the snapshot. a
Analytics rules query live data at each execution interval, not static snapshots. There is no need to recreate rules to refresh data access. The issue is a timing problem between the schedule interval and data availability. Section 1.2 covers how pipeline timing affects query results.
The five-minute lookback window returns too few rows for the summarize to produce meaningful groupings. Increasing the lookback to 24 hours would capture enough data for accurate country counts. b
A 24-hour lookback on a five-minute schedule would re-evaluate most events hundreds of times and generate massive duplicate alerts. The lookback should be slightly larger than the schedule interval — not orders of magnitude larger. The core issue is not lookback size but the gap between schedule and ingestion timing.
The Kusto engine throttles scheduled rule queries to prevent resource exhaustion, limiting each execution to scanning 10,000 rows. Events beyond that limit are silently skipped. c
While query resource limits exist, the scenario describes missed events over time, not truncated results within a single execution. If throttling were the cause, the rule would consistently miss high-volume periods, not randomly miss 40% of incidents. The actual cause is a timing gap between ingestion and evaluation windows.
Ingestion latency causes some sign-in events to arrive after their five-minute evaluation window has already closed. An event generated at 10:01 that takes seven minutes to ingest arrives at 10:08 and falls between the 10:00-10:05 and 10:05-10:10 windows. The lookback should overlap the schedule interval by at least the expected ingestion delay. d
Correct. Section 1.2 explains that pipeline execution depends on data being present at query time. SigninLogs ingestion latency typically ranges from two to eight minutes, but spikes can push individual events beyond ten minutes. A five-minute lookback on a five-minute schedule leaves no margin for late-arriving data. The fix is a lookback window that exceeds the schedule — for example, fifteen minutes on a five-minute schedule. Some events get evaluated twice, but none are missed entirely.
Scenario 3. You are building a query that calculates the percentage of failed sign-ins per user. You write: SigninLogs | where TimeGenerated > ago(7d) | summarize Total = count(), Failed = countif(ResultType != "0") by UserPrincipalName | extend FailRate = Failed / Total. The FailRate column returns 0 for every user, even those with hundreds of failures. What went wrong?
Failed and Total are both long integers. Dividing one long by another long performs integer division, which truncates the decimal. A user with 23 failures out of 100 sign-ins gets 23/100 = 0, not 0.23. The fix is to force floating-point arithmetic: extend FailRate = (Failed * 1.0) / Total or extend FailRate = toreal(Failed) / Total. a
Correct. Section 1.3 covers the implicit type hierarchy and explains that count() returns a long integer. When both operands in a division are long, KQL performs integer division with truncation toward zero. Multiplying by 1.0 promotes the numerator to real, which forces floating-point division. This is one of the most common arithmetic errors in security analytics — every percentage or ratio calculation on aggregated counts needs explicit type promotion.
The countif function does not work with string comparisons. ResultType is a string column, so countif(ResultType != "0") always returns zero. You need to convert ResultType to an integer first with countif(toint(ResultType) != 0). b
Countif works correctly with string comparisons. The expression countif(ResultType != "0") properly counts rows where ResultType is not the string "0". The issue is not the counting — it is the division arithmetic that produces the zero result. Section 1.3 explains the type hierarchy that governs arithmetic operations.
The extend operator cannot perform arithmetic on aggregation columns. You need to use a separate let statement to calculate the ratio after the summarize completes. c
Extend performs arithmetic on any column in the current table, including columns created by summarize. There is no restriction on computing derived values from aggregation results. The issue is integer division behavior, not operator compatibility. Section 1.2 confirms that extend works on whatever the previous operator produced.
The where clause is filtering out all failed sign-ins before the summarize runs. ResultType != "0" in the countif evaluates against already-filtered data that contains only successful sign-ins. d
The where clause filters by TimeGenerated only — it does not filter by ResultType. All sign-ins within the seven-day window reach the summarize, including failures. The countif correctly counts failures within the unfiltered result set. The zero output comes from integer division truncation, not from upstream filtering.
Scenario 4. You extract the city field from LocationDetails in SigninLogs using extend City = LocationDetails.city and filter with where City == "London". The query returns zero results, but you can see London sign-ins in the portal. What should you evaluate first?
The LocationDetails column is encrypted at rest and requires a decryption key before dot-notation extraction works. You need to enable the Log Analytics decryption feature for the workspace. a
LocationDetails is not encrypted. It is a standard dynamic column accessible through dot-notation in any workspace with SigninLogs ingestion enabled. There is no decryption feature required. The issue is a type behavior that Section 1.3 covers in detail.
The city field uses a localized name based on the tenant's language setting. If the tenant is configured for British English, the value might be stored as "London" with a Unicode character that looks identical but fails the equality check. b
LocationDetails city values use standard ASCII text without localization variations. Unicode homoglyph issues exist in some contexts, but this is not the cause here. The issue is more fundamental — it involves how KQL handles the return type of dynamic field extraction. Section 1.3 identifies this specific pattern.
Dot-notation on a dynamic column returns a dynamic-typed value, not a string. Comparing dynamic to a string literal with == fails silently. The fix is extend City = tostring(LocationDetails.city), which converts the extracted value to a string before the comparison. c
Correct. Section 1.3 explains that dynamic is the container type for nested JSON structures in KQL. When you extract a nested field with dot-notation, the result retains the dynamic type. A direct equality comparison between dynamic and string does not match, even when the underlying value is the text "London". The tostring() cast converts the dynamic value to a proper string, enabling the comparison. This is one of the most frequent mistakes in sign-in investigation queries.
The == operator is case-sensitive in KQL. If the stored value is "london" (lowercase), the comparison to "London" fails. You should use =~ for case-insensitive matching or normalize with tolower(). d
While == is indeed case-sensitive in KQL, the LocationDetails city field stores proper-cased city names. The actual issue occurs before case comparison — dynamic-typed values extracted via dot-notation fail string equality checks entirely until you apply tostring(). Section 1.3 covers the dynamic type behavior that causes this.
Scenario 5. You parse a JSON field from a custom log using parse_json(RawData) and extract a nested timestamp with extend EventTime = parsed.metadata.timestamp. The EventTime column shows correct-looking timestamps like "2025-05-15T08:23:41Z", but when you filter with where EventTime > ago(1d), the clause filters out records you expect to keep. What is happening?
The ago() function returns a datetime relative to the query execution time, which uses the workspace's configured timezone. If the workspace is set to UTC but the timestamps are in local time, the comparison window is offset by the timezone difference. a
KQL always operates in UTC regardless of workspace configuration. There is no timezone setting that shifts ago() behavior. The issue is not a timezone mismatch — it is a type mismatch between the extracted value and the datetime comparison. Section 1.4 identifies the conversion pattern needed here.
The extracted timestamp is a dynamic-typed value that looks like a datetime string but is not actually a datetime. Comparing a dynamic value to a datetime produced by ago() does not perform the chronological comparison you expect. The fix is extend EventTime = todatetime(parsed.metadata.timestamp), which converts the extracted value to a true datetime before comparison. b
Correct. Section 1.4 covers todatetime() as the conversion function for string-encoded timestamps. Values extracted from dynamic objects via dot-notation retain the dynamic type. A dynamic value holding the text "2025-05-15T08:23:41Z" is not a datetime — it is a dynamic containing a string that resembles a datetime. The > comparison against ago(1d) either fails silently or performs a string comparison instead of a chronological one. Explicit todatetime() conversion is required before any time-based filtering or arithmetic.
The parse_json function only parses the first level of JSON nesting. Accessing parsed.metadata.timestamp fails silently because metadata is a second-level object, and the function returns null for anything beyond the first level. c
parse_json parses the full JSON structure regardless of nesting depth. Dot-notation traversal works at any nesting level as long as the key names match the actual JSON structure. The issue is not parsing depth — it is the type of the value that dot-notation returns. Section 1.4 explains the conversion requirement.
The where clause should use between(ago(1d) .. now()) instead of > ago(1d) because the greater-than operator does not work with datetime values returned from parsed JSON. d
The > operator works with datetime values. Both > ago(1d) and between(ago(1d) .. now()) are valid datetime filters when applied to actual datetime-typed columns. The issue is that EventTime is not a datetime — it is a dynamic value that needs explicit conversion before any temporal comparison works correctly.
Scenario 6. You convert the ResultType column from string to integer using extend ErrorCode = toint(ResultType) to analyze error code ranges. The conversion works for most rows, but you notice that some rows where ResultType contains a valid-looking number now have a null ErrorCode. The original string value is "53003". What caused the null?
The toint function rejects values with leading whitespace. If the ResultType field was ingested with a trailing space ("53003 "), the conversion fails. You need to apply trim_start() before converting. a
While whitespace can sometimes cause conversion issues, toint() handles leading and trailing whitespace for numeric strings. The specific value 53003 fails for a different reason — a range limitation of the int type. Section 1.4 covers why tolong() is the safer choice for error codes and process identifiers.
ResultType 53003 is a deprecated error code that Microsoft removed from the valid error code registry. The toint function validates against a known-good list and rejects unrecognized codes. b
toint() performs numeric conversion only — it does not validate against any external registry of error codes. A deprecated code would still convert to an integer if it is a valid number. The null result comes from a numeric range limitation, not a validation check. Section 1.4 explains the specific range boundaries.
The toint function cannot convert strings that represent numbers larger than 9999. Five-digit error codes exceed the function's parsing capacity and return null. You need to use tolong() for any value above four digits. c
The toint limit is not at four digits. The int type supports values up to 2,147,483,647 (approximately 2.1 billion), which is well above 53003. However, some ResultType values in Entra ID logs can exceed the 32-bit signed integer range. The principle of using tolong() for safety is correct, but the reasoning about a four-digit limit is wrong. Section 1.4 covers the actual int and long ranges.
The value 53003 is within the 32-bit int range, but other ResultType values in the same table can exceed it. If the specific null rows contain values above 2,147,483,647 or non-numeric strings, toint() returns null. The broader fix is to use tolong() for all ID and code conversions, because int overflow returns null silently and tolong() handles the full range of numeric codes that appear in security logs. d
Correct. Section 1.4 establishes the conversion safety principle: use tolong() instead of toint() for any numeric string conversion in security tables. Process IDs, error codes, and session identifiers can exceed the 32-bit signed integer range. The toint() function returns null for out-of-range values without any warning. The conversion tracking pattern — countif(isnotnull(tolong(Field))) — reveals how many rows would survive conversion before you commit to filtering on the converted value.
Scenario 7. You write a detection rule to find sign-ins where the country does not match the user's normal location. The where clause reads: where Country != "United States". During tuning, you discover the rule never fires for users who sign in through service principals or certain API flows. Investigation shows these sign-ins have an empty LocationDetails field. Why does your rule miss them?
When LocationDetails is empty, the Country extraction returns null. The comparison null != "United States" evaluates to null, not true. Null fails the where filter, so the row is excluded. The rule silently skips every sign-in without location data — exactly the authentication flows an attacker would use. The fix is where Country != "United States" or isempty(Country). a
Correct. Section 1.5 identifies this as the most dangerous null behavior in security queries. Null fails every comparison, including not-equal. A detection rule that filters on Country != "expected value" creates a blind spot for every authentication flow that does not populate location data — service principals, some OAuth token grants, and API-based access. These are the exact flows attackers exploit because they bypass geographic controls. The defensive pattern adds an explicit null check: where Country != "United States" or isempty(Country).
Service principal sign-ins are recorded in a separate table (AADServicePrincipalSignInLogs), not in SigninLogs. Your rule cannot detect them because it queries the wrong table. The fix is to create a second rule querying the service principal table. b
While AADServicePrincipalSignInLogs does exist as a separate table, the scenario describes sign-ins that are in the query results but filtered out by the where clause. The rows exist in the result set — they just have null Country values that the != comparison excludes. The issue is null handling behavior, not table selection. Section 1.5 covers this null comparison pattern.
The Kusto engine optimizes != comparisons by skipping rows where the column value is not indexed. LocationDetails for service principal flows is stored in a non-indexed partition, so the engine skips those rows for performance. c
The Kusto engine does not skip non-indexed rows during query evaluation. Indexing affects query performance but not query correctness — every row that matches the where condition is returned regardless of index status. The missed rows are excluded because null != "United States" does not evaluate to true. Section 1.5 explains the null comparison semantics.
The != operator in KQL only works with exact string matching. If Country contains "United States" with different capitalization or extra whitespace from the API response, the comparison returns true instead of false, causing those rows to appear in results rather than being excluded. The actual missing rows have a different issue. d
The != operator does perform exact, case-sensitive string matching, but this scenario is about rows with null Country values, not capitalization variations. Rows with null are excluded because null != anything evaluates to null, which the where clause treats as false. Section 1.5 explains that null fails every comparison — including comparisons that logically should return true.
Scenario 8. You are reviewing a colleague's investigation query. The query filters SigninLogs for a compromised user, then uses extend to create three new columns: one extracts the browser from DeviceDetail, one converts ResultType to a long integer, and one parses the ConditionalAccessPolicies array. The colleague runs the query and reports that the extend line "does not work" — but the query returns results without errors. What should you check?
The extend operator has a limit of two computed columns per statement. The third column silently fails and returns null for every row. The fix is to split the extend into two separate extend operators with two columns each. a
Extend has no limit on the number of computed columns per statement. You can create dozens of columns in a single extend. The query returning results without errors but producing unexpected values points to a different issue. Section 1.4 explains the behavior that causes silent failures in type conversion.
DeviceDetail, ResultType, and ConditionalAccessPolicies use three different data types (dynamic, string, dynamic), and extend cannot mix data types in a single statement. Each extraction needs its own extend line. b
Extend handles mixed data types in a single statement without restriction. You can create a string column, a long column, and a dynamic column in the same extend. There is no type-homogeneity requirement. The issue is that one or more of the conversion functions is returning null without error. Section 1.4 covers the silent null-on-failure pattern.
KQL conversion functions return null on failure instead of raising errors. The query runs successfully because no conversion error stops execution — failed conversions just produce null columns. You should check each extended column individually with isnotnull() counts to identify which conversion is failing and on which rows. The colleague sees "it does not work" because a column that should contain values contains null. c
Correct. Section 1.4 establishes that every to* conversion function returns null on failure. The query runs without error because null is a valid value — KQL does not distinguish between "no data" and "conversion failed." The diagnostic step is the conversion tracking pattern: summarize countif(isnotnull(NewColumn)) for each extended column. The column with low non-null counts is the one with conversion failures. Common causes include dot-notation on dynamic returning dynamic instead of string, or toint() overflow on large numeric codes.
The extend operator preserves the original column when the new column has the same name. If the colleague named the converted ResultType column "ResultType" (same as the source), the original string column is retained and the new long column is discarded. The fix is to use a different name like ResultCode. d
Extend with the same column name does overwrite the original column in KQL — it does not preserve the original. If you write extend ResultType = tolong(ResultType), the column is replaced with the converted version. While reusing column names can cause confusion during debugging, it does not explain the "does not work" symptom. The more likely cause is silent null returns from conversion failures, as covered in Section 1.4.
How was this module?
Your feedback helps us improve the course. One click is enough — comments are optional.
Thank you — your feedback has been received.
© 2026 Ridgeline Cyber Defence™ Ltd. Content may not be reproduced or redistributed. Worked artifacts may be adapted for use within your organization.