Querying Log Analytics for server key performance indicators

A colleague of mine recently reached out to me to ask for a query which would display key performance counters for each known server (items like CPU, free disk space, free memory, total memory). The answer to this question is actually more complex than I would have expected as it ended up that I needed to use join to bring together the various KPI results. The final query is below:

let CPUAvg = Perf

| where TimeGenerated > now(-10min) and (ObjectName == "Processor"
or ObjectName == "System") and CounterName == "% Processor Time"

| summarize CPUAvg = toint(avg(CounterValue)) by bin(TimeGenerated, 1h), Computer

| sort
by TimeGenerated, Computer desc;

//CPUAvg

let MemoryAvg = Perf

| where TimeGenerated > now(-10min) and (ObjectName == "Memory"
and (CounterName == "Available MBytes"
or CounterName == "Available MBytes Memory"))

| summarize AvgFreeMemory = toint(avg(CounterValue)) by bin(TimeGenerated, 1h), Computer

| sort
by Computer asc;

//MemoryAvg

let FreeDiskSpace = Perf

| where TimeGenerated > now(-10min) and CounterName == "% Free Space"
and InstanceName !contains
"DPM"

| summarize FreeDiskSpace = (avg(CounterValue)) by bin(TimeGenerated, 1h), Computer, InstanceName

| sort
by TimeGenerated, Computer desc;

//FreeDiskSpace

CPUAvg | join kind=leftouter (MemoryAvg) on Computer | join kind=leftouter(FreeDiskSpace) on Computer

| project Computer, TimeGenerated, CPUAvg, AvgFreeMemory, DiskName=InstanceName, toint(FreeDiskSpace) | sort
by Computer asc

An output example for this query is shown below:

There are a couple of cool tricks in the query above which are worth mentioning.

  1. This query is assembled from a series of sub-queries. Each sub-query each ends with a // and the name of the variable which was created with the let statement. This was a tip that introduced in a previous blog post to make the query easier to debug.
  2. On the disk space item, I’ve excluded “DPM” from the drive list due to how DPM added a bunch of drive letters which cluttered up the results.
  3. This is using a leftouter join to bring together each of the variables which were created.
  4. Formatting on the last line uses a couple of tricks:
    1. Renaming InstanceName to DiskName to increase the readability of the results from the query.
    2. Converting the FreeDiskSpace to an int as extra digits beyond the original percentage aren’t very relevant. Having these shown as integers again increases the readability of the query.

Note: You may also note that one record (AzureRH1) does not have values for DiskName or DiskFreeSpace. This is because this particular type of Unix system doesn’t have performance counters available which match the naming convention in this blog post.

Summary: The above query provides a quick way to visualize the major KPI’s which you may already be collecting for your servers in Log Analytics. This query however does not provide the total disk space or total memory for a server – we will discuss one method to provide that in the next blog post of this series!

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.