Find High CPU USage Process Names (ISSUE)

%3CLINGO-SUB%20id%3D%22lingo-sub-390901%22%20slang%3D%22en-US%22%3EFind%20High%20CPU%20USage%20Process%20Names%20(ISSUE)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390901%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CDIV%3ECan%20Someone%20please%20tell%20me%20what%20I've%20done%20wrong%3F%20found%20this%20code%20but%20can't%20figure%20out%20why%20it's%20not%20working.%3C%2FDIV%3E%3CDIV%3Ethis%20is%20the%20error%3A%20%3CSTRONG%3E'join'%20operator%3A%20Failed%20to%20resolve%20table%20or%20column%20expression%20named%20'FindCPU'%20Support%20id%3A%20cfe38685-4eac-40d0-9fb6-46d0220a5493%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20Find%20Top%20Processes%20utilizing%20CPU%20%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20defining%20our%20cpu%20threshold%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20CPUThreshold%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E90%3C%2FSPAN%3E%3CSPAN%3E%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20define%20time%20sample%20rate%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20Time%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%3Em%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20define%20Count%20of%20Processes%20to%20return%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20ProcessCount%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E5%3C%2FSPAN%3E%3CSPAN%3E%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20Find%20instances%20of%20total%20cpu%20being%20used%20above%2090%25%20over%20the%20last%2010%20mins%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20TopCPU%20%3D%20Perf%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%20now(-%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%3Em)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20ObjectName%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Processor%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20CounterName%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22%25%20Processor%20Time%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%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%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20CounterValue%20%26gt%3B%20%3C%2FSPAN%3E%3CSPAN%3E90%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20Computer%2C%20ObjectName%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2C%20CounterName%2C%20CounterValue%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2C%20TimeGenerated%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20end%20query%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20find%20Process%20count%20for%20device(s)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20TopProcess%20%3D%20Perf%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%20now(-%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%3Em)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20ObjectName%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Processor%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20CounterName%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22%25%20Processor%20Time%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%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%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20CounterValue%20%26gt%3B%20%3C%2FSPAN%3E%3CSPAN%3E90%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20Computer%2C%20ObjectName%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2C%20CounterName%2C%20CounterValue%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2C%20TimeGenerated%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20end%20query%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20find%20cpu%20count%20for%20devices%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20FindCPU%20%3D%20Perf%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%3D%20ago(%3C%2FSPAN%3E%3CSPAN%3E1%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%20ObjectName%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Processor%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20CounterName%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22%25%20Processor%20Time%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20InstanceName%20!%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%3Esort%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20InstanceName%20asc%20nulls%20first%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20CPUCount%20%3D%20dcount(InstanceName)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20Computer%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20end%20query%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20Join%20all%20datasets%20together%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EFindCPU%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Ejoin%3C%2FSPAN%3E%3CSPAN%3E(TopCPU)%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%3Ejoin%3C%2FSPAN%3E%3CSPAN%3E(TopProcess)%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%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20PercentProcessorUsed%20%3D%20CounterValue1%20%2F%20CPUCount%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20avg(PercentProcessorUsed)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20Computer%2C%20ObjectName%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2C%20CounterName%2C%20CPUCount%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2C%20TotalCPU%3DCounterValue%20%3C%2FSPAN%3E%3CSPAN%3E%2F%2F%20rename%20countervalue%20to%20totalcpu%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2C%20Process%3DObjectName1%20%3C%2FSPAN%3E%3CSPAN%3E%2F%2F%20rename%20objectname1%20to%20process%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2C%20ProcessTime%3DCounterName1%20%3C%2FSPAN%3E%3CSPAN%3E%2F%2Frename%20countername1%20to%20processtime%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2C%20ProcessName%3DInstanceName%20%3C%2FSPAN%3E%3CSPAN%3E%2F%2F%20rename%20Instancename%20to%20ProcessName%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2C%20TimeGenerated%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Process%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Process%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20avg_PercentProcessorUsed%20%26gt%3B%20%3C%2FSPAN%3E%3CSPAN%3E25%3C%2FSPAN%3E%20%3CSPAN%3E%2F%2F%20only%20return%20processes%20that%20are%20using%20more%20than%2025%25%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Etop%3C%2FSPAN%3E%3CSPAN%3E%20ProcessCount%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20avg_PercentProcessorUsed%20desc%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20Computer%2C%20CPUCount%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2C%20ProcessName%2C%20avg_PercentProcessorUsed%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2C%20TotalCPU%2C%20Process%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2C%20ProcessTime%2C%20TimeGenerated%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-390901%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391439%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20High%20CPU%20USage%20Process%20Names%20(ISSUE)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391439%22%20slang%3D%22en-US%22%3EFirst%20of%20all%2C%20your%20mistake%20is%20that%20you%20have%20empty%20lines%20between%20the%20different%20parts%20of%20the%20query%20so%20the%20bottom%20part%20doesn't%20recognize%20the%20upper%20parts.%20Just%20remove%20the%20blank%20lines%20and%20you%20are%20better.%3CBR%20%2F%3EThen%2C%20none%20of%20the%20sub-queries%20return%20in%20its%20result%20InstanceName%20and%20you%20refer%20to%20it%20in%20the%20last%20summarize.%20%3CBR%20%2F%3ELast%20comment%3A%20when%20you%20can%2C%20you%20want%20to%20have%20your%20where%20conditions%20before%20summarize%20and%20not%20after.%20I%20moved%20one%20of%20them.%3CBR%20%2F%3E%3CBR%20%2F%3EHere%20is%20the%20result%2C%20please%20review%20it%20before%20using%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%2F%2F%20Find%20Top%20Processes%20utilizing%20CPU%20%3CBR%20%2F%3E%2F%2F%20defining%20our%20cpu%20threshold%3CBR%20%2F%3Elet%20CPUThreshold%20%3D%2090%3B%3CBR%20%2F%3E%2F%2F%20define%20time%20sample%20rate%3CBR%20%2F%3Elet%20Time%20%3D%2010m%3B%3CBR%20%2F%3E%2F%2F%20define%20Count%20of%20Processes%20to%20return%3CBR%20%2F%3Elet%20ProcessCount%20%3D%205%3B%3CBR%20%2F%3E%2F%2F%20Find%20instances%20of%20total%20cpu%20being%20used%20above%2090%25%20over%20the%20last%2010%20mins%3CBR%20%2F%3Elet%20TopCPU%20%3D%20Perf%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%20now(-10m)%3CBR%20%2F%3Eand%20ObjectName%20%3D%3D%20%22Processor%22%3CBR%20%2F%3Eand%20CounterName%20%3D%3D%20%22%25%20Processor%20Time%22%3CBR%20%2F%3Eand%20InstanceName%20%3D%3D%20%22_Total%22%3CBR%20%2F%3Eand%20CounterValue%20%26gt%3B%2090%3CBR%20%2F%3E%7C%20project%20Computer%2C%20ObjectName%3CBR%20%2F%3E%2C%20CounterName%2C%20CounterValue%3CBR%20%2F%3E%2C%20TimeGenerated%3B%3CBR%20%2F%3E%2F%2F%20end%20query%3CBR%20%2F%3E%2F%2F%20find%20Process%20count%20for%20device(s)%3CBR%20%2F%3Elet%20TopProcess%20%3D%20Perf%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%20now(-10m)%3CBR%20%2F%3Eand%20ObjectName%20%3D%3D%20%22Processor%22%3CBR%20%2F%3Eand%20CounterName%20%3D%3D%20%22%25%20Processor%20Time%22%3CBR%20%2F%3Eand%20InstanceName%20%3D%3D%20%22_Total%22%3CBR%20%2F%3Eand%20CounterValue%20%26gt%3B%2090%3CBR%20%2F%3E%7C%20project%20Computer%2C%20ObjectName%3CBR%20%2F%3E%2C%20CounterName%2C%20CounterValue%3CBR%20%2F%3E%2C%20TimeGenerated%3B%3CBR%20%2F%3E%2F%2F%20end%20query%3CBR%20%2F%3E%2F%2F%20find%20cpu%20count%20for%20devices%3CBR%20%2F%3Elet%20FindCPU%20%3D%20Perf%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(1h)%3CBR%20%2F%3E%7C%20where%20ObjectName%20%3D%3D%20%22Processor%22%3CBR%20%2F%3Eand%20CounterName%20%3D%3D%20%22%25%20Processor%20Time%22%3CBR%20%2F%3Eand%20InstanceName%20!%3D%20%22_Total%22%3CBR%20%2F%3E%7C%20sort%20by%20InstanceName%20asc%20nulls%20first%3CBR%20%2F%3E%7C%20summarize%20CPUCount%20%3D%20dcount(InstanceName)%20by%20Computer%3B%3CBR%20%2F%3E%2F%2F%20end%20query%3CBR%20%2F%3E%2F%2F%20Join%20all%20datasets%20together%3CBR%20%2F%3EFindCPU%20%7C%20join(TopCPU)%20on%20Computer%3CBR%20%2F%3E%7C%20join(TopProcess)on%20Computer%3CBR%20%2F%3E%7C%20extend%20PercentProcessorUsed%20%3D%20CounterValue1%20%2F%20CPUCount%3CBR%20%2F%3E%7C%20where%20ObjectName1%3D%3D%22Process%22%3CBR%20%2F%3E%7C%20summarize%20avg(PercentProcessorUsed)%20by%20Computer%2C%20ObjectName%3CBR%20%2F%3E%2C%20CounterName%2C%20CPUCount%3CBR%20%2F%3E%2C%20TotalCPU%3DCounterValue%20%2F%2F%20rename%20countervalue%20to%20totalcpu%3CBR%20%2F%3E%2C%20Process%3DObjectName1%20%2F%2F%20rename%20objectname1%20to%20process%3CBR%20%2F%3E%2C%20ProcessTime%3DCounterName1%20%2F%2Frename%20countername1%20to%20processtime%3CBR%20%2F%3E%2C%20TimeGenerated%3CBR%20%2F%3E%7C%20where%20avg_PercentProcessorUsed%20%26gt%3B%2025%20%2F%2F%20only%20return%20processes%20that%20are%20using%20more%20than%2025%25%3CBR%20%2F%3E%7C%20top%20ProcessCount%20by%20avg_PercentProcessorUsed%20desc%3C%2FLINGO-BODY%3E
Highlighted
Contributor
Can Someone please tell me what I've done wrong? found this code but can't figure out why it's not working.
this is the error: 'join' operator: Failed to resolve table or column expression named 'FindCPU' Support id: cfe38685-4eac-40d0-9fb6-46d0220a5493
 
// Find Top Processes utilizing CPU


// defining our cpu threshold
let CPUThreshold = 90;


// define time sample rate
let Time = 10m;

// define Count of Processes to return
let ProcessCount = 5;

// Find instances of total cpu being used above 90% over the last 10 mins
let TopCPU = Perf
| where TimeGenerated > now(-10m)
and ObjectName == "Processor"
and CounterName == "% Processor Time"
and InstanceName == "_Total"
and CounterValue > 90
| project Computer, ObjectName
, CounterName, CounterValue
, TimeGenerated;

// end query


// find Process count for device(s)
let TopProcess = Perf
| where TimeGenerated > now(-10m)
and ObjectName == "Processor"
and CounterName == "% Processor Time"
and InstanceName == "_Total"
and CounterValue > 90
| project Computer, ObjectName
, CounterName, CounterValue
, TimeGenerated;

// end query



// find cpu count for devices
let FindCPU = Perf
| where TimeGenerated >= ago(1h)
| where ObjectName == "Processor"
and CounterName == "% Processor Time"
and InstanceName != "_Total"
| sort by InstanceName asc nulls first
| summarize CPUCount = dcount(InstanceName) by Computer;
// end query


// Join all datasets together
FindCPU | join(TopCPU) on Computer
| join(TopProcess)on Computer
| extend PercentProcessorUsed = CounterValue1 / CPUCount
| summarize avg(PercentProcessorUsed) by Computer, ObjectName
, CounterName, CPUCount
, TotalCPU=CounterValue // rename countervalue to totalcpu
, Process=ObjectName1 // rename objectname1 to process
, ProcessTime=CounterName1 //rename countername1 to processtime
, ProcessName=InstanceName // rename Instancename to ProcessName
, TimeGenerated
| where Process == "Process"
and avg_PercentProcessorUsed > 25 // only return processes that are using more than 25%
| top ProcessCount by avg_PercentProcessorUsed desc
| project Computer, CPUCount
, ProcessName, avg_PercentProcessorUsed
, TotalCPU, Process
, ProcessTime, TimeGenerated
1 Reply
Highlighted
First of all, your mistake is that you have empty lines between the different parts of the query so the bottom part doesn't recognize the upper parts. Just remove the blank lines and you are better.
Then, none of the sub-queries return in its result InstanceName and you refer to it in the last summarize.
Last comment: when you can, you want to have your where conditions before summarize and not after. I moved one of them.

Here is the result, please review it before using:

// Find Top Processes utilizing CPU
// defining our cpu threshold
let CPUThreshold = 90;
// define time sample rate
let Time = 10m;
// define Count of Processes to return
let ProcessCount = 5;
// Find instances of total cpu being used above 90% over the last 10 mins
let TopCPU = Perf
| where TimeGenerated > now(-10m)
and ObjectName == "Processor"
and CounterName == "% Processor Time"
and InstanceName == "_Total"
and CounterValue > 90
| project Computer, ObjectName
, CounterName, CounterValue
, TimeGenerated;
// end query
// find Process count for device(s)
let TopProcess = Perf
| where TimeGenerated > now(-10m)
and ObjectName == "Processor"
and CounterName == "% Processor Time"
and InstanceName == "_Total"
and CounterValue > 90
| project Computer, ObjectName
, CounterName, CounterValue
, TimeGenerated;
// end query
// find cpu count for devices
let FindCPU = Perf
| where TimeGenerated >= ago(1h)
| where ObjectName == "Processor"
and CounterName == "% Processor Time"
and InstanceName != "_Total"
| sort by InstanceName asc nulls first
| summarize CPUCount = dcount(InstanceName) by Computer;
// end query
// Join all datasets together
FindCPU | join(TopCPU) on Computer
| join(TopProcess)on Computer
| extend PercentProcessorUsed = CounterValue1 / CPUCount
| where ObjectName1=="Process"
| summarize avg(PercentProcessorUsed) by Computer, ObjectName
, CounterName, CPUCount
, TotalCPU=CounterValue // rename countervalue to totalcpu
, Process=ObjectName1 // rename objectname1 to process
, ProcessTime=CounterName1 //rename countername1 to processtime
, TimeGenerated
| where avg_PercentProcessorUsed > 25 // only return processes that are using more than 25%
| top ProcessCount by avg_PercentProcessorUsed desc