KQL Summarize unable to show Null values

Question

I've stripped some of my query out but I am trying to count the entries from my query from 5 minute buckets.

There may occasionally be no data in these buckets as there were no requests but I want to be able to represent that as a 0 rather than null so that I can then compare that against a previous week.

requests | where success == true | summarize Count = count() by bin(timestamp, 5m) | extend Count = iif(isnull(Count) or Count == 0, 0, Count)

This is returning the following results set

because of my | extend Count = iif(isnull(Count) or Count == 0, 0, Count) I would have expected my results to show all the 5 min buckets between 09:05 and 09:30 but with a count of 0

I can get this to a 0 with a make series but I'm not wanting to make series for graphing as I want to then use this data set to compare to say the week prior to work out any differences

Answer 1

KQL Summarize unable to show Null values

To show NULL values instead of 0. You can use below query, unmatched_data filters out timestamps from the generated sequence to simulate unmatched data. In that timestamps matching the ones in the real_data table are excluded from the sequence. The Count for these unmatched timestamps is initialized to 0. left outer join is used to to ensure all timestamps from the sequence are included, irrespective of the existence of equivalent data in the real_data. final_data checks if the Count is null or zero and replaces it with 0 accordingly.

let start = datetime(2024-03-18T09:05:00);
let end = datetime(2024-03-18T09:30:00);
let step = 5m;

let real_data = datatable(timestamp: datetime, Count: int)
[
    datetime(2024-03-18T09:10:00), 5,
    datetime(2024-03-18T09:20:00), 3
];

let sequence = range x from start to end step step | extend timestamp = x;

let unmatched_data = sequence
| where timestamp != datetime(2024-03-18T09:10:00) and timestamp != datetime(2024-03-18T09:20:00)
| extend Count = 0;

let joined_data = sequence 
| join kind=leftouter real_data on timestamp;

let final_data = joined_data
| extend Count = iif(isnull(Count) or Count == 0, 0, Count);

final_data

Output: