[SOLVED] Kqlmagic returns No valid xcolumn

Kqlmagic returns No valid xcolumn

The following example query works in the Azure Data Explorer UI but not with Kqlmagic in Jupyter Notebook.

%%kql
let min_t = toscalar(demo_make_series1 | summarize min(TimeStamp));
let max_t = toscalar(demo_make_series1 | summarize max(TimeStamp));
demo_make_series1
| make-series num=count() default=0 on TimeStamp in range(min_t, max_t, 1h) by OsVer
| render timechart

It just throws No valid xcolumn. Any idea whats the issue?

Note: The database demo_make_series1 is available on the help cluster from ADX.

Solution

This indeed looks like a bug in KqlMagic rendering. We shall check and update. Meanwhile you can use mv-expand before rendering. Regardless, in make-series I suggest you avoid using the deprecated range(...) syntax in favor of 'from ... to ... step ...'. Here is the updated query:

%%kql
let min_t = toscalar(demo_make_series1 | summarize min(TimeStamp));
let max_t = toscalar(demo_make_series1 | summarize max(TimeStamp));
demo_make_series1
| make-series num=count() default=0 on TimeStamp from min_t to max_t step 1h by OsVer
| mv-expand num to typeof(long), TimeStamp to typeof(datetime)
| render timechart

thanks, Adi