Unable to create valid KQL query for Azure Custom log search as Metric alert type

Question

This is my KQL query

Perf
| where TimeGenerated > ago(60m)
| where (ObjectName == "Processor")
| summarize AggregatedValue = avg(CounterValue) by Computer , _ResourceId
| where AggregatedValue < 100
| project Computer, AggregatedValue

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

Note : Above query is working successfully (prints result) in Azure Monitor - Logs as below image1. But same query is throwing Error while running in as below image2.

-------------------------------------------------------------------------------------------------

The error message seems very descriptive. Have you tried adding `bin(TimeGenerated, [roundTo])` (where *roundTo* is a timespan, E.g., 30s, 5m, 1h etc.) to the *by* clause? — David דודו Markovitz, Dec 07 '22 at 14:08
Tried with this below query : same Error ''' Perf | where bin(TimeGenerated, 1h) > ago(60m) | where (ObjectName == "Processor") | summarize AggregatedValue = avg(CounterValue) by Computer, _ResourceId | where AggregatedValue < 100 | project Computer, AggregatedValue ''' — praveen, Dec 07 '22 at 16:39
@DavidדודוMarkovitz , I can't understand your comment, I'm new to KQL.Can you please help me constructing this query. — praveen, Dec 07 '22 at 16:50
Try to put the bin expression as part of the summarize operator, after the word *by* — David דודו Markovitz, Dec 07 '22 at 17:05

score 1 · Accepted Answer · answered Dec 13 '22 at 02:06

The error message seems very descriptive. Tried adding bin(TimeGenerated, [roundTo])

It worked

    Perf
    | where TimeGenerated > ago(1h)
    | where CounterName == "% Processor Time" and InstanceName == "_Total" 
    | project TimeGenerated, Computer, CounterValue, _ResourceId
    | summarize AggregatedValue = avg(CounterValue)  by bin(TimeGenerated, 1h), Computer, _ResourceId

Thanks @David דודו Markovitz

Unable to create valid KQL query for Azure Custom log search as Metric alert type

1 Answers1