1.5 Null Handling — The Silent Query Killer

Null means "no value." It is not zero, not an empty string, not false. It is the absence of a value entirely.

- null — the value does not exist - "" — the value exists and is an empty string - 0 — the value exists and is the number zero - false — the value exists and is the boolean false

These are four different things. KQL treats them differently:

Key distinction: isempty() returns true for both null and empty string. isnull() returns true only for null. In security queries, you almost always want isnotempty() — which catches both null and empty string.

How null breaks queries

Comparison with null always returns false:

This means any where clause that compares against a column containing null will exclude that row — regardless of the comparison operator.

Real-world impact:

This query excludes sign-ins with null location data — sign-ins where the location was not recorded. If the adversary used a technique that suppresses location data (certain API-based sign-ins, service principal auth), their sign-ins disappear from your results. You conclude "no sign-ins from outside the UK" when the correct conclusion is "no sign-ins from outside the UK among sign-ins that have location data."

The safe version:

This includes rows where Country is null or empty — making them visible instead of invisible.

The null-safe patterns

Pattern 1: Include nulls explicitly

Pattern 2: Coalesce to a default

Pattern 3: Check for null before using a field

Pattern 4: Separate null investigation

If WithoutLocation is a significant percentage, investigate those sign-ins separately — they may be service principals, managed identities, or suppressed location events.

Null in join operations

Null handling becomes critical in joins. When you join two tables on a key column, rows with null keys behave differently depending on the join type:

This has investigation consequences. If you join SigninLogs to a watchlist on IPAddress, and some sign-in events have null IPAddress, those events are excluded from an inner join. If the adversary's sign-in events have null IP addresses (certain authentication flows do not record IP), they vanish from your investigation results.

Safe join pattern for investigation:

The leftouter join preserves all sign-in events, adding watchlist data where it matches and null where it does not. You can then filter or flag matches without losing the non-matching rows.

Building null-aware detection rules

Every detection rule should be reviewed for null blindness. Use this checklist:

1. Identify the key filtering columns — which columns does the where clause reference? 2. Check null rates — run the null audit query from the Try It exercise for each column 3. Assess security impact — if a null value in this column could represent adversary activity, the detection rule has a blind spot 4. Add null handling — either include nulls in the filter (or isempty(Column)) or create a separate alert for null values in critical columns

Example: null-aware detection for external sign-ins:

Version 2 is the correct pattern for production detection rules. The LocationStatus column makes it immediately visible to the triaging analyst whether the alert fired because of a foreign country or missing location data — two different investigation paths.

Null in aggregation functions

Aggregation functions handle null differently:

This means dcount(IPAddress) does not count rows where IPAddress is null. If 10 sign-ins have no IP address, they are excluded from the distinct count. For security investigations, this can undercount the adversary's footprint.

Safe dcount pattern:

Now you see the complete picture: the distinct IP count, how many events had no IP, and the total.

Null propagation in expressions

Null is contagious in arithmetic and string expressions. Any operation involving null produces null:

This propagation creates subtle bugs in calculated columns:

Defense: Use coalesce to provide default values before calculation:

The isempty vs isnull vs isnotnull distinction

KQL has multiple null-checking functions that behave differently:

For string columns: Use isempty() — it catches both null and empty string, which is what you want. An IPAddress that is null and an IPAddress that is "" are both "missing" for investigation purposes.

For numeric columns: Use isnull() — numeric columns cannot be "empty", only null.

For dynamic columns: Use isempty() after converting to string, or check the specific field: isempty(tostring(LocationDetails.countryOrRegion)).

Counting nulls — the visibility audit query

Before writing any detection rule, audit the null rates in the columns your rule depends on. This query produces a null rate report for SigninLogs:

Run this query on your workspace. The results tell you which columns are unreliable for filtering. If 40% of sign-ins have no RiskLevelDuringSignIn value, a detection rule that filters on where RiskLevelDuringSignIn == "high" is blind to 40% of sign-in events — any of which could be adversary activity.

This audit query is not a one-time exercise. Run it monthly. Null rates change as authentication patterns change (new applications, new device types, conditional access policy modifications). A detection rule that worked last month may have a new blind spot this month because a null rate shifted.

The coalesce function — your null safety net

coalesce returns the first non-null, non-empty value from a list of arguments:

If countryOrRegion is populated, use it. If it is null but state is populated, use state. If both are null, use "Unknown". The fallback chain ensures every row has a value — no nulls propagate into downstream logic.

Detection rule pattern — null-safe IP classification:

This handles null IPs explicitly instead of letting them fall through to the "External" classification (which would be a false positive — a null IP is not necessarily external).