Log Analytics – Disk Queries

So Log Analytics is a really powerful tool, the ability to ingest a wide variety of logs can help you to really build out some robust monitoring to better enable your application. And this ultimately enables the ability to build out robust dashboards.

Now I recently had to do some log analytics queries, specifically around disk statistics to monitor all the disks on a given machine. And if your like me, you don’t write these queries often so when you do it can be a process.

Now a couple of things to note about log analytics queries that matter, especially KQL. The biggest and most important being that order of operations matter. Unlike SQL, when you apply each clause this is a lot closer to using a | in Linux than a “where” clause in SQL. You need to make sure you use the right clause as it can make things a lot harder.

So anyway, here are some queries I think you’ll find helpful:

All Disk Statistics:

Perf | where ObjectName == "LogicalDisk"| summarize Value = min(CounterValue) by Computer, InstanceName, CounterName| sort by CounterName asc nulls last | sort by InstanceName asc nulls last | sort by Computer asc nulls last 

% Free space – Graph

Perf | where ObjectName == "LogicalDisk" and CounterName == "% Free Space" and InstanceName != "\_Total" and Computer = ""| summarize FreeSpace = min(CounterValue) by InstanceName, Computer, TimeGenerated| sort by FreeSpace asc nulls last | render timechart

Avg Disk sec / Read – graph

Perf | where ObjectName == "LogicalDisk" and CounterName == "Avg. Disk sec/Read" and InstanceName != "\_Total" and Computer = ""| summarize AvgDiskReadPerSec = min(CounterValue) by InstanceName, Computer, TimeGenerated| sort by AvgDiskReadPerSec asc nulls last | render timechart

Avg Disk sec / Write

Perf | where ObjectName == "LogicalDisk" and CounterName == "Avg. Disk sec/Write" and InstanceName != "\_Total" and Computer = ""| summarize AvgDiskWritePerSec = min(CounterValue) by InstanceName, Computer, TimeGenerated| sort by AvgDiskWritePerSec asc nulls last | render timechart

Current Disk Queue Length

Perf | where ObjectName == "LogicalDisk" and CounterName == "Current Disk Queue Length" and InstanceName != "\_Total" and Computer = ""| summarize CurrentQueueLength = min(CounterValue) by InstanceName, Computer, TimeGenerated| sort by CurrentQueueLength asc nulls last | render timechart

Disk Reads/sec – graph

Perf | where ObjectName == "LogicalDisk" and CounterName == "Disk Reads/sec" and InstanceName != "\_Total" and Computer = ""| summarize DiskReadsPerSec = min(CounterValue) by InstanceName, Computer, TimeGenerated| sort by DiskReadsPerSec asc nulls last | render timechart

Disk Transfers/sec – Graph

Perf | where ObjectName == "LogicalDisk" and CounterName == "Disk Transfers/sec" and InstanceName != "\_Total" and Computer = ""| summarize DiskTransfersPerSec = min(CounterValue) by InstanceName, Computer, TimeGenerated| sort by DiskTransfersPerSec asc nulls last | render timechart

Disk Writes/sec – Graph

Perf | where ObjectName == "LogicalDisk" and CounterName == "Disk Writes/sec" and InstanceName != "\_Total" and Computer = ""| summarize DiskWritesPerSec = min(CounterValue) by InstanceName, Computer, TimeGenerated| sort by DiskWritesPerSec asc nulls last | render timechart

Alert = % Free Space Warning

Perf | where ObjectName == "LogicalDisk" and CounterName == "% Free Space"| summarize FreeSpace = min(CounterValue) by Computer, InstanceName| where FreeSpace < 20| sort by FreeSpace asc nulls last | render barchart kind=unstacked