Forum Discussion

roopesh_shetty's avatar
roopesh_shetty
Copper Contributor
May 24, 2019

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

  • 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 

    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's avatar
      roopesh_shetty
      Copper Contributor

      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?

Resources