Kusto syntax mystery discovered while creating a Metrics type alert against a custom log query

Copper Contributor

Please help me understand a Kusto syntax validation mystery I just discovered while creating a Metrics type alert against a custom log query.


The syntax error message was:

"Search Query should contain 'AggregatedValue' and 'bin(TimeGenerated, [roundTo])' for Metric alert type"

 

After reading docs, such as:

https://docs.microsoft.com/en-us/azure/azure-monitor/platform/alerts-unified-log

 

... I modified my query code to satisfy the rules. I succeeded in getting the code validated, but was left with a mystery.

 

This syntax did not work:

// no workie
let prevavg = metrics_kafka_CL
| where TimeGenerated between(ago(120m)..ago(60m))
| where isnotempty(kafka_ReplicaManager_PartitionCount_Value_value_d)
| summarize avg(kafka_ReplicaManager_PartitionCount_Value_value_d);
let prevavg_sc = toscalar(prevavg);
let latestavg = metrics_kafka_CL
| where TimeGenerated between(ago(60m)..ago(0m))
| where isnotempty(kafka_ReplicaManager_PartitionCount_Value_value_d)
| summarize avg(kafka_ReplicaManager_PartitionCount_Value_value_d);
let latestavg_sc = toscalar(latestavg);
let delta = abs(latestavg_sc - prevavg_sc);
let delta_percentage = iff(prevavg_sc != 0, 100 * (delta / prevavg_sc), toreal(0));
//let delta_percentage_round = round(delta_percentage);
let TimeGenerated = ago(0m);
let final_resultset = print delta_percentage, TimeGenerated;
final_resultset | summarize AggregatedValue = any(delta_percentage) by bin(TimeGenerated, 1m)

 

On a hunch, I compressed the code to remove as many variable assignments as I could, and voila, it worked. But WHY?! :

 

let prevavg_sc = toscalar(metrics_kafka_CL
| where TimeGenerated between(ago(120m)..ago(60m))
| where isnotempty(kafka_ReplicaManager_PartitionCount_Value_value_d)
| summarize avg(kafka_ReplicaManager_PartitionCount_Value_value_d));
let latestavg_sc = toscalar(metrics_kafka_CL
| where TimeGenerated between(ago(60m)..ago(0m))
| where isnotempty(kafka_ReplicaManager_PartitionCount_Value_value_d)
| summarize avg(kafka_ReplicaManager_PartitionCount_Value_value_d));
let final_resultset = print delta_percentage = iff(prevavg_sc != 0, 100 * (abs(latestavg_sc - prevavg_sc) / prevavg_sc), toreal(0)), TimeGenerated = ago(0m);
final_resultset | summarize AggregatedValue = any(delta_percentage) by bin(TimeGenerated, 1m)

2 Replies

@ekiersky The main difference is that you have is toscalar in the modified query that made it work for you.

I don't think so. The toscalar() function is used in both query runs, in effectively the same way. Line #5 in the first run, right? Thanks for taking a look.