Search code examples
kqlazure-alerts

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

I want to create an alert on the below scenario:

if (incoming_messages of event hub != outgoing_messages of event hub)
then I should get alerted

For that I created a query in log analytics where the "Comparison" column shows the difference of the incoming and outgoing messages of event hub

But when I am using the below query as "Metric Measurement" for alert creation.....It's giving the below error

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

Can somebody please tell me that how to fix this error or is there any other way to set the alerting for the above scenario ??

Here is the query

let Incoming_Messages = AzureMetrics
| where ResourceProvider =="MICROSOFT.EVENTHUB"
| where _ResourceId contains "ResourceID-Hidden"
| where TimeGenerated > ago(1h)
| where MetricName contains "IncomingMessages"
| count | extend CommonCol="Dummy"
| project CommonCol, TotalIncomingMessages = Count;
let Outgoing_Messages = AzureMetrics
| where ResourceProvider =="MICROSOFT.EVENTHUB"
| where _ResourceId contains "ResourceID-Hidden"
| where TimeGenerated > ago(1h)
| where MetricName contains "OutgoingMessages"
| count | extend CommonCol="Dummy"
| project CommonCol, TotalOutgoingMessages = Count;
Incoming_Messages
| join Outgoing_Messages on CommonCol
| extend Comparison = TotalIncomingMessages - TotalOutgoingMessages
| project TotalOutgoingMessages, TotalIncomingMessages, Comparison

Error Screenshot:

Error Screenshot

Solution

  • It seems that it would make more sense to use 'Number of results' in your case. Since you want to know is any rows meet the criteria.

    Try this query with 'Number of results' (threshold > 0):

    let Incoming_Messages = AzureMetrics
| where ResourceProvider =="MICROSOFT.EVENTHUB"
| where _ResourceId contains "ResourceID-Hidden"
| where TimeGenerated > ago(1h)
| where MetricName contains "IncomingMessages"
| count | extend CommonCol="Dummy"
| project CommonCol, TotalIncomingMessages = Count;
let Outgoing_Messages = AzureMetrics
| where ResourceProvider =="MICROSOFT.EVENTHUB"
| where _ResourceId contains "ResourceID-Hidden"
| where TimeGenerated > ago(1h)
| where MetricName contains "OutgoingMessages"
| count | extend CommonCol="Dummy"
| project CommonCol, TotalOutgoingMessages = Count;
Incoming_Messages
| join Outgoing_Messages on CommonCol
| extend Comparison = TotalIncomingMessages - TotalOutgoingMessages
| project TotalOutgoingMessages, TotalIncomingMessages, Comparison
| where Comparison != 0