Query for extra column

[roopesh_shetty]

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% ?

[#2 · Accepted solution]

@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

[#3]

@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?