Forum Discussion

Copper Contributor

May 24, 2019

Solved

Query for extra column

Hi Guys, We have this below Query which is providing us the output as list of servers whose Processor Utilization value is above 80% Perf | where ObjectName == "Processor" and CounterName == ...

Azure Log Analytics

azure monitor

Meir_Mendelovich
May 25, 2019
roopesh_shetty

Here you go:

Perf | where ObjectName == "Processor" and CounterName == "% Processor Time"

| summarize AggregatedValue = avg(CounterValue), min(TimeGenerated), max(TimeGenerated) by bin(TimeGenerated, 5m), Computer

| where AggregatedValue > 80

| extend Duration = max_TimeGenerated - min_TimeGenerated //Assuming a single time span in this 5m window

You can also do daily aggregations per computer. Just add this at the end:

| summarize sum(Duration) by bin_at(TimeGenerated,1d,startofday(now())) , Computer //Daily (calendar days) summary

Hope it helps,

Meir

Meir_Mendelovich

Microsoft

May 25, 2019

roopesh_shetty

Here you go:

Perf | where ObjectName == "Processor" and CounterName == "% Processor Time"
| summarize AggregatedValue = avg(CounterValue), min(TimeGenerated), max(TimeGenerated)  by bin(TimeGenerated, 5m), Computer
| where AggregatedValue > 80| extend Duration = max_TimeGenerated - min_TimeGenerated //Assuming a single time span in this 5m window

You can also do daily aggregations per computer. Just add this at the end:

| summarize sum(Duration) by bin_at(TimeGenerated,1d,startofday(now())) , Computer //Daily (calendar days) summary

Hope it helps,

Meir

roopesh_shetty
Copper Contributor
May 27, 2019
Meir_Mendelovich

Thanks Meir,

I have just added one more line to this query as I need to list out servers which has usage more than 80% for the duration less than 5 minutes as below;

Perf | where ObjectName == "Processor" and CounterName == "% Processor Time"
| summarize AggregatedValue = avg(CounterValue), min(TimeGenerated), max(TimeGenerated) by bin(TimeGenerated, 5m), Computer
| where round(AggregatedValue) > 80
| extend Duration = max_TimeGenerated - min_TimeGenerated
| where Duration < 5m

This query is giving the desired result as expected. But when I tried to use this same query while creating alert based on Metric Measurement alert logic I am getting the error as “Search Query should contain “AggregatedValue” and bin(TimeGenerated. [roundTo]) for Metric Alert type”.

Is there anything which need to fine tune further this query to use it in creating alerts based on Metric Measurement alert?