A Common KQL Mistake in Threat Hunting and Detection Engineering

When crafting queries for threat hunting or detection engineering using the Kusto Query Language (KQL), a frequent oversight involves handling numeric fields with null values. In KQL, numeric types such as int, long, and real can be null when data is missing, due to internal bugs in EDRs, or when queries or functions return no data. This nuance can significantly impact the effectiveness of queries, causing false negatives.

Consider the following example data:

let MyTable = datatable(
str_val:string  , num_val:int )[
'foo'           ,   5           ,
'bar'           ,   int(null)   ,
'baz'           ,   8           ,
];

Sample Data

Imagine we want to filter records where num_val is less than 8. A straightforward approach might look like this:

MyTable
| where num_val < 8

As you see, the query also excluded the row where the num_val is null, potentially leading to false negatives when we try to identify/investigate malicious activities. There are certain situations where this most likely happens:

• The FileProfile() function doesn’t always return information for a file, especially if Defender for Endpoint doesn’t record the file hash. In such cases, the fields coming from the function become null or empty.

If we filter the logs using a condition likeGlobalPrevalence < 200 , those files with null prevalence information also gets excluded. What if they were malicious?

• When joining tables using leftouter/rightouter, if there is no corresponding row in the other table that contains a numeric value, we get null values.

Filtering on the value field using <, >, <=, >= operator causes the same issue as with the FileProfile() function.

How to Fix

The fix for the null value problem is quite easy. We just need to add an additional condition using the OR operator:

| where num_val < 8 or isnull(num_val)

Now it’s time to check queries to see if there are such oversight :)

Happy hunting!