Forum Discussion
roopesh_shetty
May 24, 2019Copper Contributor
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 == "% Processor Time"
| summarize AggregatedValue = avg(CounterValue) by bin(TimeGenerated, 5m), Computer
| where AggregatedValue > 80
Can it be possible to add one more Column by name “DURATION” on its output using Extend operator which should have values as the duration (in minutes or seconds) from how long the Processor Utilization is above 80% ?
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 windowYou 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) summaryHope it helps,
Meir
- Meir_Mendelovich
Microsoft
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 windowYou 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) summaryHope it helps,
Meir
- roopesh_shettyCopper Contributor
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?