With KQL, is there a way to generate extra rows in the results table, for every possibility in a case statement, even if there were no matches from the dataset? This is to save the front end from having logic to fill the gaps where there were no results, this probably isn't best practice, but I've been asked to and I'm all for making people's lives easier.
My case statement is acting on a previous calculation and bucketing the results into buckets of <30, 30-90 and the default 90+. Furthermore, I am binning the results by day, so, for each day there should be three rows returned with the Total Count per bucket per day, even if they are 0 results for a bucket in a day.
I've tried using make-series and summarise in different ways to try and add a default of 0 to the cases to ensure a result but obviously that doesn't make sense and isn't working.
// delay is a calculation of datetimes
| summarize Total = count() by Duration = case(delay < 30, "< 30 days", delay <= 90, "30-90 days", "90+ days"), bin(timestamp, 1d)
| sort by timestamp
After the case is closed, we lose context of the buckets so I can't grab them and check their 0 state. Is there a way to artificially force these 0 results into the results table?
Thanks for your time, I hope this will be fun one!
Edit: I'd also be open to and I'm currently working on multiple aggregations for this data and potentially extra columns for these buckets per row day, instead of extra rows.
So I resolved this one by changing the approach to the answer. I found it too hard trying to artificially force 0 results into rows, so I turned the results on its head.
I binned my data first by day, swapped the case for count_ifs and returned a single row per day back out with the totals I needed in their own columns. So by default when the query didn't count a result it was expectedly 0.
My solution dropped from 8 lines to 4 and I no longer needed to do a preliminary search before the query, dropping the need to store a table of results to act on.
| summarize under_30=countif(delay<30), 30_90=countif(delay>=30 and delay <=90), over_90=countif(delay>90) by bin(timestamp, 1d)
Sorry this doesn't resolve how to display default rows when a case doesn't match, but sometimes the best solution is found after a refactor and thinking about it differently.