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

8
votes

I'm trying to create a custom metric alert based on some metrics in my Application Insights logs. Below is the query I'm using;

let start = customEvents
| where customDimensions.configName == "configName" 
| where name == "name"
| extend timestamp, correlationId = tostring(customDimensions.correlationId), configName = tostring(customDimensions.configName);

let ending = customEvents
| where customDimensions.configName == configName" 
| where name == "anotherName" 
| where customDimensions.taskName == "taskName" 
| extend timestamp, correlationId = tostring(customDimensions.correlationId), configName = tostring(customDimensions.configName), name= name, nameTimeStamp= timestamp ;


let timeDiffs = start
| join (ending) on correlationId
| extend timeDiff = nameTimeStamp- timestamp
| project timeDiff, timestamp, nameTimeStamp, name, anotherName, correlationId;

timeDiffs
| summarize AggregatedValue=avg(timeDiff) by bin(timestamp, 1m)

When I run this query in Analytics page, I get results, however when I try to create a custom metric alert, I got the error Search Query should contain 'AggregatedValue' and 'bin(timestamp, [roundTo])' for Metric alert type

The only response I found was adding AggregatedValue which I already have, I'm not sure why custom metric alert page is giving me this error.

1
azureazure-devopsazure-application-insights

1 Answers

15
votes

I found what was wrong with my query. Essentially, aggregated value needs to be numeric, however AggregatedValue=avg(timeDiff) produces time value, but it was in seconds, so it was a bit hard to notice. Converting it to int solves the problem,

I have just updated last bit as follows

timeDiffs
| summarize AggregatedValue=toint(avg(timeDiff)/time(1ms)) by bin(timestamp, 5m)

This brings another challenge on Aggregate On while creating the alert as AggregatedValue is not part of the grouping that is coming after by statement.