SOLVED

Query for extra column

%3CLINGO-SUB%20id%3D%22lingo-sub-643122%22%20slang%3D%22en-US%22%3EQuery%20for%20extra%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643122%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20have%20this%20below%20Query%20which%20is%20providing%20us%20the%20output%20as%20list%20of%20servers%20whose%20Processor%20Utilization%20value%20is%20above%2080%25%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EPerf%20%7C%20where%20ObjectName%20%3D%3D%20%22Processor%22%20and%20CounterName%20%3D%3D%20%22%25%20Processor%20Time%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%7C%20summarize%20AggregatedValue%20%3D%20avg(CounterValue)%20by%20bin(TimeGenerated%2C%205m)%2C%20Computer%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%7C%20where%20AggregatedValue%20%26gt%3B%2080%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20it%20be%20possible%20to%20add%20one%20more%20Column%20by%20name%20%E2%80%9C%3CSTRONG%3EDURATION%3C%2FSTRONG%3E%E2%80%9D%20on%20its%20output%20using%20Extend%20operator%20which%20should%20have%20values%20as%20the%20duration%20(in%20minutes%20or%20seconds)%20from%20how%20long%20the%20Processor%20Utilization%20is%20above%2080%25%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-643122%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-645990%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20for%20extra%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-645990%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F338025%22%20target%3D%22_blank%22%3E%40roopesh_shetty%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20you%20go%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20style%3D%22color%3A%20%23000000%3B%20background-color%3A%20%23fffffe%3B%20font-family%3A%20Consolas%2C%20'Courier%20New'%2C%20monospace%3B%20font-weight%3A%20normal%3B%20font-size%3A%2014px%3B%20line-height%3A%2016px%3B%20white-space%3A%20pre%3B%22%3E%0A%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3EPerf%20%7C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%230000ff%3B%22%3Ewhere%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%20ObjectName%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23a31515%3B%22%3E%22Processor%22%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22color%3A%20%230000ff%3B%22%3Eand%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%20CounterName%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23a31515%3B%22%3E%22%25%20Processor%20Time%22%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%7C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%230000ff%3B%22%3Esummarize%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%20AggregatedValue%20%3D%20avg(CounterValue)%2C%20min(TimeGenerated)%2C%20max(TimeGenerated)%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%230000ff%3B%22%3Eby%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%20bin(TimeGenerated%2C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%2309885a%3B%22%3E5%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3Em)%2C%20Computer%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%7C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%230000ff%3B%22%3Ewhere%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%20AggregatedValue%20%26gt%3B%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%2309885a%3B%22%3E80%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FFONT%3E%3CDIV%20style%3D%22color%3A%20%23000000%3B%20background-color%3A%20%23fffffe%3B%20font-family%3A%20Consolas%2C%20'Courier%20New'%2C%20monospace%3B%20font-weight%3A%20normal%3B%20font-size%3A%2014px%3B%20line-height%3A%2016px%3B%20white-space%3A%20pre%3B%22%3E%0A%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%7C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%230000ff%3B%22%3Eextend%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%20Duration%20%3D%20max_TimeGenerated%20-%20min_TimeGenerated%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23008000%3B%22%3E%2F%2FAssuming%20a%20single%20time%20span%20in%20this%205m%20window%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20also%20do%20daily%20aggregations%20per%20computer.%20Just%20add%20this%20at%20the%20end%3A%3C%2FP%3E%0A%3CDIV%20style%3D%22color%3A%20%23000000%3B%20background-color%3A%20%23fffffe%3B%20font-family%3A%20Consolas%2C%20'Courier%20New'%2C%20monospace%3B%20font-weight%3A%20normal%3B%20font-size%3A%2014px%3B%20line-height%3A%2016px%3B%20white-space%3A%20pre%3B%22%3E%0A%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%7C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%230000ff%3B%22%3Esummarize%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%20sum(Duration)%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%230000ff%3B%22%3Eby%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%20bin_at(TimeGenerated%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%2309885a%3B%22%3E1%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3Ed%2Cstartofday(now()))%20%2C%20Computer%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23008000%3B%22%3E%2F%2FDaily%20(calendar%20days)%20summary%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHope%20it%20helps%2C%3C%2FP%3E%0A%3CP%3EMeir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-650049%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20for%20extra%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-650049%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F84435%22%20target%3D%22_blank%22%3E%40Meir%20Mendelovich%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20Meir%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20just%20added%20one%20more%20line%20to%20this%20query%20as%20I%20need%20to%20list%20out%20servers%20which%20has%20usage%20more%20than%2080%25%20for%20the%20duration%20less%20than%205%20minutes%20as%20below%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EPerf%20%7C%20where%20ObjectName%20%3D%3D%20%22Processor%22%20and%20CounterName%20%3D%3D%20%22%25%20Processor%20Time%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%7C%20summarize%20AggregatedValue%20%3D%20avg(CounterValue)%2C%20min(TimeGenerated)%2C%20max(TimeGenerated)%26nbsp%3B%20by%20bin(TimeGenerated%2C%205m)%2C%20Computer%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%7C%20where%26nbsp%3B%20round(AggregatedValue)%20%26gt%3B%2080%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%7C%20extend%20Duration%20%3D%20max_TimeGenerated%20-%20min_TimeGenerated%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%7C%20where%20Duration%20%26lt%3B%205m%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20query%20is%20giving%20the%20desired%20result%20as%20expected.%20But%20when%20I%20tried%20to%20use%20this%20same%20query%20while%20creating%20alert%20based%20on%20Metric%20Measurement%20alert%20logic%20I%20am%20getting%20the%20error%20as%20%3CSTRONG%3E%E2%80%9CSearch%20Query%20should%20contain%20%E2%80%9CAggregatedValue%E2%80%9D%20and%20bin(TimeGenerated.%20%5BroundTo%5D)%20for%20Metric%20Alert%20type%3C%2FSTRONG%3E%E2%80%9D.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20anything%20which%20need%20to%20fine%20tune%20further%20this%20query%20to%20use%20it%20in%20creating%20alerts%20based%20on%20Metric%20Measurement%20alert%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

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 Replies
Highlighted
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

Highlighted

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