Calculating total memory and total disk space in Log Analytics
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 multiterabyte 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!