SOLVED

Query for finding computers with % processor time above 50 when a security event is occuring

%3CLINGO-SUB%20id%3D%22lingo-sub-226849%22%20slang%3D%22en-US%22%3EQuery%20for%20finding%20computers%20with%20%25%20processor%20time%20above%2050%20when%20a%20security%20event%20is%20occuring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-226849%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20my%20first%20time%20working%20with%20azure%20log%20analytics%20and%20I%20wanted%20some%20feedback%20on%20if%20my%20approach%20to%20my%20data%20request%20is%20correct.%3C%2FP%3E%3CP%3EI%20need%20computers%20which%20have%20%25%20processor%20time%20above%2050%20which%20also%20have%20a%20security%20event%20both%20occurring%20within%20the%20last%20day%20using%20the%20Perf%20and%20SecurityEvent%20tables%3C%2FP%3E%3CP%3EThis%20is%20my%20approach%3C%2FP%3E%3CDIV%3E%3CSPAN%3EPerf%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ejoin%3C%2FSPAN%3E%3CSPAN%3E%20(SecurityEvent)%20%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E%20Computer%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26lt%3B%20ago(%3C%2FSPAN%3E%3CSPAN%3E24%3C%2FSPAN%3E%3CSPAN%3Eh)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20CounterName%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22%25%20Processor%20Time%22%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20InstanceName%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22_Total%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20AggregatedValue%20%3D%20avg(CounterValue)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20Computer%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20AggregatedValue%20%26gt%3B%20%3C%2FSPAN%3E%3CSPAN%3E50%3C%2FSPAN%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20think%20I%20am%20on%20the%20right%20track%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-226849%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-228217%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20for%20finding%20computers%20with%20%25%20processor%20time%20above%2050%20when%20a%20security%20event%20is%20occuring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-228217%22%20slang%3D%22en-US%22%3E%3CP%3EStanislav's%20solution%20still%20only%20shows%20computers%20that%20have%20an%20average%20percent%20processor%20time%20%26gt%3B%2050%25%20over%20the%20whole%2024%20hour%20period.%20If%20you%20are%20interested%20in%20computers%20that%20had%20high%20cpu%20usage%20around%20the%20time%20of%20the%20security%20event%20itself%2C%20you'll%20need%20to%20bin%20the%20two%20time%20streams%20and%20join%20on%20the%20timestamp.%3C%2FP%3E%3CPRE%3Elet%20binSize%20%3D%201h%3B%3CBR%20%2F%3ESecurityEvent%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%20ago(24h)%3CBR%20%2F%3E%7C%20project%20Computer%2C%20bin(TimeGenerated%2C%20binSize)%3CBR%20%2F%3E%7C%20join%20(%3CBR%20%2F%3E%20Perf%3CBR%20%2F%3E%20%7C%20where%20TimeGenerated%20%26gt%3B%20ago(24h)%3CBR%20%2F%3E%20%20%20%20and%20CounterName%20%3D%3D%20%22%25%20Processor%20Time%22%20and%20InstanceName%20%3D%3D%20%22_Total%22%3CBR%20%2F%3E%20%7C%20summarize%20PercentProcessorTime%20%3D%20avg(CounterValue)%20by%20Computer%2C%20bin(TimeGenerated%2C%20binSize)%3CBR%20%2F%3E)%20on%20Computer%2C%20TimeGenerated%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-226926%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20for%20finding%20computers%20with%20%25%20processor%20time%20above%2050%20when%20a%20security%20event%20is%20occuring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-226926%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20Stanislav%20and%20Patrick.%20This%20is%20exactly%20what%20I%20was%20looking%20for.%20This%20really%20helps.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-226913%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20for%20finding%20computers%20with%20%25%20processor%20time%20above%2050%20when%20a%20security%20event%20is%20occuring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-226913%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EI%20think%20what%20you%20are%20trying%20to%20achieve%20is%20the%20query%20below%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Elet%20ComputersWithSecurityEvents%20%3D%20SecurityEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(24h)%20%7C%20distinct%20Computer%3B%0APerf%0A%7C%20where%20Computer%20in%20(ComputersWithSecurityEvents)%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(24h)%0A%7C%20where%20CounterName%20%3D%3D%20%22%25%20Processor%20Time%22%20and%20InstanceName%20%3D%3D%20%22_Total%22%0A%7C%20summarize%20AggregatedValue%20%3D%20avg(CounterValue)%20by%20Computer%0A%7C%20where%20AggregatedValue%20%26gt%3B%2050%3C%2FPRE%3E%0A%3CP%3ELet%20me%20know%20if%20this%20is%20what%20you%20wanted%20to%20achieve.%20I've%20put%20the%20Computers%20with%20security%20events%20into%20separate%20table%20and%20I%20am%20only%20taking%20the%20Computer%20Names%20from%20there%20and%20I%20match%20them%20to%20the%20performance%20query.%20I've%20also%20corrected%20your%20time%20window%20as%20you%20were%20using%20%26lt%3B%20instead%20of%20%26gt%3B.%20With%20this%20query%20it%20will%20get%20the%20data%20for%20the%20last%2024%20hours%20from%20both%20performance%20and%20security.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-226858%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20for%20finding%20computers%20with%20%25%20processor%20time%20above%2050%20when%20a%20security%20event%20is%20occuring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-226858%22%20slang%3D%22en-US%22%3E%3CP%3EI%20see%20what%20you%20saying.%20Yes%2C%20I%20was%20looking%20to%20get%20results%20for%20only%20computers%20that%20had%20average%20%25%20processor%20time%20above%2050%20when%20there%20is%20also%20a%20security%20event%20occuring.%20But%20i'm%20still%20wondering%20how%20I%20would%20go%20about%20doing%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-226857%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20for%20finding%20computers%20with%20%25%20processor%20time%20above%2050%20when%20a%20security%20event%20is%20occuring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-226857%22%20slang%3D%22en-US%22%3E%3CP%3Ethe%20problem%20is%20you're%20averaging%20processor%20time%20over%20the%20whole%20time%20range%20(24h).%26nbsp%3B%20did%20you%20want%20to%20know%20the%20average%20processor%20time%20in%20some%20time%20window%20around%20when%20the%20security%20event%20occurred%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

This is my first time working with azure log analytics and I wanted some feedback on if my approach to my data request is correct.

I need computers which have % processor time above 50 which also have a security event both occurring within the last day using the Perf and SecurityEvent tables

This is my approach

Perf
| join (SecurityEvent) on Computer
| where TimeGenerated < ago(24h)
| where CounterName == "% Processor Time" and InstanceName == "_Total"
| summarize AggregatedValue = avg(CounterValue) by Computer
| where AggregatedValue > 50

 

Do you think I am on the right track?

5 Replies
Highlighted

the problem is you're averaging processor time over the whole time range (24h).  did you want to know the average processor time in some time window around when the security event occurred?

 

 

Highlighted

I see what you saying. Yes, I was looking to get results for only computers that had average % processor time above 50 when there is also a security event occuring. But i'm still wondering how I would go about doing that.

Highlighted
Solution

Hi,

I think what you are trying to achieve is the query below:

 

let ComputersWithSecurityEvents = SecurityEvent
| where TimeGenerated > ago(24h) | distinct Computer;
Perf
| where Computer in (ComputersWithSecurityEvents)
| where TimeGenerated > ago(24h)
| where CounterName == "% Processor Time" and InstanceName == "_Total"
| summarize AggregatedValue = avg(CounterValue) by Computer
| where AggregatedValue > 50

Let me know if this is what you wanted to achieve. I've put the Computers with security events into separate table and I am only taking the Computer Names from there and I match them to the performance query. I've also corrected your time window as you were using < instead of >. With this query it will get the data for the last 24 hours from both performance and security.

Highlighted
Thank you so much Stanislav and Patrick. This is exactly what I was looking for. This really helps.
Highlighted

Stanislav's solution still only shows computers that have an average percent processor time > 50% over the whole 24 hour period. If you are interested in computers that had high cpu usage around the time of the security event itself, you'll need to bin the two time streams and join on the timestamp.

let binSize = 1h;
SecurityEvent
| where TimeGenerated > ago(24h)
| project Computer, bin(TimeGenerated, binSize)
| join (
Perf
| where TimeGenerated > ago(24h)
and CounterName == "% Processor Time" and InstanceName == "_Total"
| summarize PercentProcessorTime = avg(CounterValue) by Computer, bin(TimeGenerated, binSize)
) on Computer, TimeGenerated