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) in Log Analytics. In the first blog post of this series, we created a pretty simple query handle this question but the query did not include the total amount of memory on a system or the total amount of disk space. To determine these values we need to jump through a few mathematical hoops.

The final query is below:

let CPUAvg = Perf

| where TimeGenerated > now(-60min) and (ObjectName == "Processor"

or ObjectName == "System") and CounterName == "% Processor Time"

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

| sort

by TimeGenerated, Computer desc;

//CPUAvg

let CPUCount = Perf

| where TimeGenerated > now(-60min)

| where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName!="_Total"

| sort by InstanceName asc nulls first

| summarize CPUCount = dcount(InstanceName) by Computer;

//CPUCount

let FreeMemory = Perf

| where TimeGenerated > now(-60min) and (ObjectName == "Memory" and CounterName contains "Available M")

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

| sort by TimeGenerated, Computer desc;

//FreeMemory

let UsedMemory = Perf

| where TimeGenerated > now(-60min) and (ObjectName == "Memory" and CounterName == "Committed Bytes")

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

| sort by TimeGenerated, Computer desc;

//UsedMemory

let TotalMemory = FreeMemory | join UsedMemory on Computer, InstanceName

| project TimeGenerated, Computer, InstanceName, FreeMemory, UsedMemory

| extend TotalMemGB = toint((FreeMemory + (UsedMemory / 1024 / 1024)) / 1024);

//TotalMemory

let FreeMB = Perf

| where TimeGenerated > now(-30min) and CounterName == "Free Megabytes"

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

| sort by TimeGenerated, Computer desc;

//FreeMB

let FreeSpace = Perf

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

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

| sort by TimeGenerated, Computer desc;

//FreeSpace

let DiskTotalFreeMB = FreeMB | join FreeSpace on Computer, InstanceName

| project TimeGenerated, Computer, InstanceName, FreeMB, FreeSpace

| extend TotalSizeGB = toint((FreeMB / FreeSpace * 100) /1024);

//DiskTotalFreeMB

DiskTotalFreeMB | join kind=leftouter CPUAvg on Computer | join kind=leftouter TotalMemory on Computer | join kind=leftouter CPUCount on Computer | sort by TimeGenerated, Computer, InstanceName

| extend FreeDiskGB = FreeMB/1024

| extend FreeMemoryGB = round(FreeMemory/1024)

| project Computer, InstanceName, CPUCount, CPUAvg, FreeMemoryGB, TotalMemGB, round(FreeDiskGB), TotalSizeGB

| distinct Computer, InstanceName, CPUCount, CPUAvg, FreeMemoryGB, TotalMemGB, round(FreeDiskGB), TotalSizeGB

NOTE: The code above was altered on 7/12/2018 to cover performance counters which listed as either “Available MBytes” or “Available Mbytes” per feedback from Billy York (thanks Billy!). We also added CPUCount to the fields so we can also see how many CPU’s exist on the system (the query was drawn from here).

An output example for this query is shown below showing both the values that had for performance counters and the ones that we have calculated (CPUCount, TotalMemGB and TotalSizeGB):

Here’s an example of the actual numbers for a server (the CM one shown above):

The query’s estimate was 151 GB total size which matched the actual value.

The query’s estimate was 8 GB of memory which matched the actual value.

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

• There is also not a performance counter which is available that provides the total amount of disk space in a system. To handle this we used a formula to calculate this based on the counters that we have available:
  • We first gather free megabytes of disk space and the free disk percentage. Using this we can calculate the missing value (total disk space). This is done with an extend:
  • extend TotalSizeGB = toint((FreeMB / FreeSpace * 100) /1024);
  • Note: For large disks, the number of digits which are collected are not sufficient to make a very solid estimate. There are 10 digits available to the right of the decimal point which is very accurate except for when you are looking at drive sizes which are multi-terabyte and take more than 10 digits to represent the number of bytes available on the drive.
• There is also not a performance counter which is available that provides the total amount of memory in a system. To handle this we added a performance counter for the amount of used memory and we used a formula to calculate this based on the counters that we have available:
  • We first gather the amount of available memory (Memory(*)Available Mbytes) and we also gather the (Memory(*)Committed Bytes). We use these to calculate the missing value (total memory). This is done with an extend which adds the values after converting the Committed Bytes to Mbytes:
  • extend TotalMemGB = toint((FreeMemory + (UsedMemory / 1024 / 1024)) / 1024);
• To maximize how close the estimate would be to the actual value, we did not use a toint on any of the values until we finalized the calculation at the end. If we had used toint earlier on values it truncates the numbers after the decimal point and that significantly decreases the accuracy of the estimate.
• To additionally maximize how close the estimate would be to the actual value, we converted to GB and we used a round or the memory (round doesn’t drop the decimals after the decimal point – instead it rounds to the closest integer).

Summary: The above query provides way to visualize the KPI’s for a server including an estimated amount of disk space and memory for the system (which I don’t believe anyone else has put together prior to this point in time). While this query does provide all of the KPI’s related to cpu, disk and memory it only provides an estimated value for the total disk space and total memory. To provide completely accurate values we will discuss another method to provide that in the next blog post of this series!