In this section
Null Handling — The Silent Query Killer
You know that type conversion functions return null on failure, and that null propagates silently through the pipeline. You have seen coalesce() provide fallback values for missing data. Null handling is a complete discipline in KQL: every source of null values in security data, why null comparisons behave counterintuitively, and the defensive patterns that prevent null from creating blind spots in investigations and detection rules.
Scenario
During an incident investigation, Tom queries for sign-ins from outside the UK: SigninLogs | where TimeGenerated > ago(7d) | where tostring(LocationDetails.countryOrRegion) != "GB". The query returns zero results. Tom documents in the incident report: "No sign-ins from outside the UK in the past 7 days." During the post-incident review, Priya runs a different query and finds 340 sign-in events with null location data, including a cluster of successful authentications from an IP address associated with the attacker's infrastructure. The location was null because the adversary authenticated through an API flow that does not populate geo-IP data. Tom's query silently excluded those events because null fails every comparison, including not-equal.
What null means and why it matters
Null means "no value." It is not zero, not an empty string, not false. It is the absence of a value entirely. KQL distinguishes between these four states, and the distinction matters for every query you write.
Null appears in security data for specific reasons: a field was not populated by the data source (API-based sign-ins often lack LocationDetails), a type conversion failed (Section 1.4), a dynamic sub-field does not exist in the JSON for that particular event, or a leftouter join produced a non-matching row where the right-side columns are null.
Figure 1.5: Null compared to any value, by any operator, returns false. This includes the not-equal operator, which means where Field != "value" excludes null rows alongside matching rows.
How null breaks investigation queries
The counterintuitive behavior is the not-equal case. where Country != "GB" excludes two categories of rows: rows where Country is "GB" (which you intended to exclude) and rows where Country is null (which you did not intend to exclude). The null rows are silently dropped because null != "GB" evaluates to false.
This directly affects investigations. If you search for sign-ins from outside the UK and the adversary authenticated through a flow that does not record location, their sign-in events have null location data. Your "not UK" filter excludes them. You conclude the adversary was not present. The evidence was there. Your query could not see it.
The same pattern applies to every not-equal filter in investigation queries. where AppDisplayName != "Microsoft Office" excludes events with null AppDisplayName. where DeviceDetail.operatingSystem != "Windows" excludes events with no device information. where RiskLevelDuringSignIn != "none" excludes events where the risk engine did not evaluate the sign-in (which is a different condition from "the risk engine evaluated the sign-in and found no risk"). Each exclusion is a potential blind spot in the investigation.
The risk is asymmetric. Including null rows in your results costs you nothing except a few extra rows to review. Excluding them can cost you the evidence that proves the adversary was present. The safe default for investigation queries is always to include nulls and evaluate them separately.
The safe version includes null explicitly:
The or isempty(Country) clause catches rows where Country is null or empty string. The LocationStatus column makes it immediately visible to the triaging analyst whether the alert fired because of a foreign country or missing location data. These are two different investigation paths.
The isempty vs isnull distinction
KQL has separate null-checking functions that behave differently depending on data type:
isempty() returns true for both null and empty string. isnull() returns true only for null. For string columns in security data, use isempty() because both null and empty string mean "the data is missing" for investigation purposes. An IPAddress that is null and an IPAddress that is "" are both "no IP recorded" from an investigation standpoint. For numeric columns, use isnull() because numeric values cannot be empty, only null. For dynamic columns, convert to string first and then use isempty(): isempty(tostring(LocationDetails.countryOrRegion)).
Null in aggregation functions
Aggregation functions handle null in ways that affect your counts and metrics. count() counts all rows, including those with null values in any column. dcount(IPAddress) counts distinct non-null values, excluding rows where IPAddress is null. sum(), avg(), min(), and max() all ignore null values. If all values in the aggregation are null, these functions return null, not zero.
This means dcount(IPAddress) undercounts the total distinct sources if some events have null IP addresses. The avg() function is particularly surprising: it calculates the average of non-null values only. If you have 100 events and 20 have null ByteCount values, avg(ByteCount) computes the average across the 80 non-null values, not across all 100. If the 20 null values represent failed transfers that should be counted as zero, your average is artificially inflated. The safe pattern is avg(coalesce(ByteCount, 0)), which substitutes zero for null before averaging.
The safe pattern for distinct counts includes a separate count of null values:
Now the investigation report includes the complete picture: the number of distinct IP addresses, how many events had no IP, and the total event count. If NullIPCount is 200 out of 5,000 total events, that is 4% of sign-in activity that any IP-based detection rule cannot see.
The null visibility audit
Before deploying any detection rule, audit the null rates in the columns the rule depends on. This query produces a null rate report for the columns most commonly used in SigninLogs filters:
Run this query on your workspace. If 40% of sign-ins have no RiskLevelDuringSignIn value, any detection rule that filters on where RiskLevelDuringSignIn == "high" is blind to 40% of sign-in events. If 15% have no location data, your geo-anomaly detection has a 15% blind spot. Every column with a null rate above a few percent is a column where where filters silently exclude a meaningful portion of your data.
This audit is not a one-time exercise. Null rates change as authentication patterns shift: new applications, new device types, conditional access policy modifications, and changes to service principal configurations all affect which fields are populated. A detection rule that had a 2% blind spot last month may have a 20% blind spot this month because a new application was onboarded that generates sign-in events without location data.
The audit also reveals which tables and columns are reliable enough for detection engineering. If a column has a 50% null rate, it should not be the primary filtering column in a detection rule. Use it as a supplementary signal instead: enrich the detection output with the column when it is available, but do not gate the detection on its presence. The null rate determines the column's reliability as a detection input.
Null-safe patterns for detection rules
Every detection rule that uses a not-equal filter needs the null inclusion pattern. Every rule that uses a field for classification or aggregation needs to handle the null case explicitly. The case function with an isempty check at the top is the cleanest approach:
The isempty(IPAddress) check at the top of the case function handles the null IP case explicitly. Without it, null IPs would fall through to the "External" classification because ipv4_is_in_range() returns false for null input. That false classification would generate a false positive alert: the rule would flag a "sign-in from external IP" when there was simply no IP recorded.
Null in join operations
When you join two tables on a key column, null keys never match in an inner join. Both sides require a non-null value for the row to appear in the result. This means sign-in events with null IPAddress disappear from the output if you inner join SigninLogs to an IP watchlist on the IPAddress column. The adversary's null-IP sign-ins vanish from your investigation results.
The safe pattern uses leftouter join, which preserves all rows from the left table regardless of whether they match the right table. Non-matching rows get null values for the right-side columns. You then check explicitly whether a match occurred:
The leftouter join produces a row for every sign-in event. Events that matched the threat intelligence watchlist have populated right-side columns (ThreatType, Confidence). Events that did not match, including null-IP events, have null in those columns. The TIMatch flag makes the distinction explicit. Module 4 covers join types in depth, but the null safety principle applies from the start: prefer leftouter when you cannot afford to lose rows from the left table.
Null propagation in expressions
Null is contagious in arithmetic. Any operation involving null produces null. 5 + null is null. 100.0 * null / TotalCount is null. datetime_diff("minute", TimeGenerated, null) is null. One null input contaminates the entire expression, and the resulting null value silently fails every downstream filter.
Consider a session duration calculation: extend Duration = datetime_diff("minute", TimeGenerated, CreatedDateTime). If CreatedDateTime is null for some rows (which happens when the sign-in event does not have a creation timestamp separate from the ingestion timestamp), Duration is null for those rows. A subsequent where Duration > 30 silently drops them. If you are investigating long-duration sessions as a possible indicator of token theft, the null-duration events are invisible.
The defense is coalesce() before the calculation. Substitute a safe default value for the potentially null field: coalesce(CreatedDateTime, TimeGenerated) uses TimeGenerated as a fallback, producing a Duration of zero for events where CreatedDateTime was missing. The choice of default is a semantic decision that depends on what null means for that specific field. Zero duration is correct here because it means "duration unknown, not suspicious." For a field like ByteCount, coalesce(ByteCount, 0) is appropriate for transfer volume calculations. For RiskLevelDuringSignIn, substituting zero would artificially lower an average risk score, so a separate handling path for null risk is more appropriate.
String concatenation handles null differently from arithmetic. strcat("hello", tostring(null)) returns "hello" because tostring(null) converts to an empty string, and concatenating with an empty string produces the original string. This means strcat does not propagate null the way arithmetic does. But this also means you might not notice a missing value in concatenated output: strcat(FirstName, " ", LastName) produces "John " if LastName is null, with a trailing space but no error or visible indication that data is missing.
The analyst writes where Country != "GB" to find foreign sign-ins. The query silently excludes all rows where Country is null. The adversary authenticated through an API flow with no location data. Their sign-ins are invisible. The investigation concludes "no foreign access" when the correct conclusion is "no foreign access among events with location data." Adding or isempty(Country) to the filter takes five seconds. Missing the evidence takes three weeks to discover.
KQL Principle
Null is not a value. It is the absence of a value. It fails every comparison, contaminates every calculation, and silently excludes rows from every filter. The defensive habit is: before writing any where clause, ask "what happens if this column is null for some rows?" If the null rows could contain evidence, include them explicitly with isempty(). If they cannot, document why you are excluding them. Every production detection rule should have a documented null-handling decision for every filtering column.
Get weekly detection and investigation techniques
KQL queries, detection rules, and investigation methods — the same depth as this course, delivered every Tuesday.
No spam. Unsubscribe anytime. ~2,000 security practitioners.