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
Top comments (0)