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

%3CLINGO-SUB%20id%3D%22lingo-sub-659372%22%20slang%3D%22en-US%22%3EKusto%20syntax%20mystery%20discovered%20while%20creating%20a%20Metrics%20type%20alert%20against%20a%20custom%20log%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-659372%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20help%20me%20understand%20a%20Kusto%20syntax%20validation%20mystery%20I%20just%20discovered%20while%20creating%20a%20Metrics%20type%20alert%20against%20a%20custom%20log%20query.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThe%20syntax%20error%20message%20was%3A%3C%2FP%3E%3CP%3E%22Search%20Query%20should%20contain%20'AggregatedValue'%20and%20'bin(TimeGenerated%2C%20%5BroundTo%5D)'%20for%20Metric%20alert%20type%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20reading%20docs%2C%20such%20as%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Fplatform%2Falerts-unified-log%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Fplatform%2Falerts-unified-log%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E...%20I%20modified%20my%20query%20code%20to%20satisfy%20the%20rules.%20I%20succeeded%20in%20getting%20the%20code%20validated%2C%20but%20was%20left%20with%20a%20mystery.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20syntax%20did%20not%20work%3A%3C%2FP%3E%3CP%3E%2F%2F%20no%20workie%3CBR%20%2F%3Elet%20prevavg%20%3D%20metrics_kafka_CL%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20between(ago(120m)..ago(60m))%3CBR%20%2F%3E%7C%20where%20isnotempty(kafka_ReplicaManager_PartitionCount_Value_value_d)%3CBR%20%2F%3E%7C%20summarize%20avg(kafka_ReplicaManager_PartitionCount_Value_value_d)%3B%3CBR%20%2F%3Elet%20prevavg_sc%20%3D%20toscalar(prevavg)%3B%3CBR%20%2F%3Elet%20latestavg%20%3D%20metrics_kafka_CL%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20between(ago(60m)..ago(0m))%3CBR%20%2F%3E%7C%20where%20isnotempty(kafka_ReplicaManager_PartitionCount_Value_value_d)%3CBR%20%2F%3E%7C%20summarize%20avg(kafka_ReplicaManager_PartitionCount_Value_value_d)%3B%3CBR%20%2F%3Elet%20latestavg_sc%20%3D%20toscalar(latestavg)%3B%3CBR%20%2F%3Elet%20delta%20%3D%20abs(latestavg_sc%20-%20prevavg_sc)%3B%3CBR%20%2F%3Elet%20delta_percentage%20%3D%20iff(prevavg_sc%20!%3D%200%2C%20100%20*%20(delta%20%2F%20prevavg_sc)%2C%20toreal(0))%3B%3CBR%20%2F%3E%2F%2Flet%20delta_percentage_round%20%3D%20round(delta_percentage)%3B%3CBR%20%2F%3Elet%20TimeGenerated%20%3D%20ago(0m)%3B%3CBR%20%2F%3Elet%20final_resultset%20%3D%20print%20delta_percentage%2C%20TimeGenerated%3B%3CBR%20%2F%3Efinal_resultset%20%7C%20summarize%20AggregatedValue%20%3D%20any(delta_percentage)%20by%20bin(TimeGenerated%2C%201m)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20a%20hunch%2C%20I%20compressed%20the%20code%20to%20remove%20as%20many%20variable%20assignments%20as%20I%20could%2C%20and%20voila%2C%20it%20worked.%20But%20WHY%3F!%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elet%20prevavg_sc%20%3D%20toscalar(metrics_kafka_CL%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20between(ago(120m)..ago(60m))%3CBR%20%2F%3E%7C%20where%20isnotempty(kafka_ReplicaManager_PartitionCount_Value_value_d)%3CBR%20%2F%3E%7C%20summarize%20avg(kafka_ReplicaManager_PartitionCount_Value_value_d))%3B%3CBR%20%2F%3Elet%20latestavg_sc%20%3D%20toscalar(metrics_kafka_CL%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20between(ago(60m)..ago(0m))%3CBR%20%2F%3E%7C%20where%20isnotempty(kafka_ReplicaManager_PartitionCount_Value_value_d)%3CBR%20%2F%3E%7C%20summarize%20avg(kafka_ReplicaManager_PartitionCount_Value_value_d))%3B%3CBR%20%2F%3Elet%20final_resultset%20%3D%20print%20delta_percentage%20%3D%20iff(prevavg_sc%20!%3D%200%2C%20100%20*%20(abs(latestavg_sc%20-%20prevavg_sc)%20%2F%20prevavg_sc)%2C%20toreal(0))%2C%20TimeGenerated%20%3D%20ago(0m)%3B%3CBR%20%2F%3Efinal_resultset%20%7C%20summarize%20AggregatedValue%20%3D%20any(delta_percentage)%20by%20bin(TimeGenerated%2C%201m)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-722765%22%20slang%3D%22en-US%22%3ERe%3A%20Kusto%20syntax%20mystery%20discovered%20while%20creating%20a%20Metrics%20type%20alert%20against%20a%20custom%20log%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-722765%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F352238%22%20target%3D%22_blank%22%3E%40ekiersky%3C%2FA%3E%26nbsp%3BThe%20main%20difference%20is%20that%20you%20have%20is%20toscalar%20in%20the%20modified%20query%20that%20made%20it%20work%20for%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-722862%22%20slang%3D%22en-US%22%3ERe%3A%20Kusto%20syntax%20mystery%20discovered%20while%20creating%20a%20Metrics%20type%20alert%20against%20a%20custom%20log%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-722862%22%20slang%3D%22en-US%22%3EI%20don't%20think%20so.%20The%20toscalar()%20function%20is%20used%20in%20both%20query%20runs%2C%20in%20effectively%20the%20same%20way.%20Line%20%235%20in%20the%20first%20run%2C%20right%3F%20Thanks%20for%20taking%20a%20look.%3C%2FLINGO-BODY%3E
New 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.