CODEWITHSUNDEEP
performanceazure-data-explorerkql
Chumpy
Chumpy

Reputation: 43

How to get details of top CPU using servers in KQL

All our servers have the Log Analytics agent installed. The agent is configured to capture performance counters info. I can easily get an overview of CPU usage on all servers by firing this query:

Perf
| where ObjectName == "Processor" and CounterName == "% Processor Time"
| summarize HourlyUsage = avg(CounterValue) by bin(TimeGenerated, 1h),  Computer 
| render timechart

I would like to create a graph showing the CPU usage of the top 10 heaviest loaded servers. I created a KQL query to get the top 10 of CPU using servers:

let TopCPUMaxServers = Perf
| where ObjectName == "Processor" and CounterName == "% Processor Time" 
| summarize Max_CPU = max(CounterValue) by Computer, CounterName
| top 10 by Max_CPU  asc nulls last;

This query gives me the list of interesting servers. Now I would like to get the performance counters of this list. When I try this:

let TopCPUMaxServers = Perf
| where ObjectName == "Processor" and CounterName == "% Processor Time" 
| summarize Max_CPU = max(CounterValue) by Computer, CounterName
| top 10 by Max_CPU  asc nulls last;
Perf | join (TopCPUMaxServers) on Computer 
| where ObjectName == "Processor" and CounterName == "% Processor Time"
| summarize avg(CounterValue) by bin(TimeGenerated, 1h), Computer
| render timechart

I only get the measurements from the first query displayed on a time chart. (basically 10 values) How can I get the performance counters for the servers obtained in the first query?

Regards, Sven

Upvotes: 0

Views: 2075

Answers (2)

Avnera
Avnera

Reputation: 7618

The default join kind is "innerunique" which returns the first match. You can use a the "leftouter" kind but for this scenario the "in" operator will be simpler and will likely perform better:

let TopCPUMaxServers = Perf
| where ObjectName == "Processor" and CounterName == "% Processor Time" 
| summarize Max_CPU = max(CounterValue) by Computer, CounterName
| top 10 by Max_CPU  asc nulls last
| project Computer;
Perf 
| where Computer in (TopCPUMaxServers)
| where ObjectName == "Processor" and CounterName == "% Processor Time"
| summarize avg(CounterValue) by bin(TimeGenerated, 1h), Computer
| render timechart

Upvotes: 2

Alexander Sloutsky
Alexander Sloutsky

Reputation: 3017

You can use next variant using in() operator:

let TopCPUMaxServers = Perf
| where ObjectName == "Processor" and CounterName == "% Processor Time" 
| summarize Max_CPU = max(CounterValue) by Computer, CounterName
| top 10 by Max_CPU  asc nulls last
| project Computer;
Perf 
| where Computer in (TopCPUMaxServers)
| where ObjectName == "Processor" and CounterName == "% Processor Time"
| summarize avg(CounterValue) by bin(TimeGenerated, 1h), Computer
| render timechart

Upvotes: 2

Related Questions