Forum Discussion
Help with Disk query in Log Analytics
Hi Rajinder Rahul,
Your question is very popular, indeed many times users want to get the latest report of a computer performance counter (such as free space). Note that the overall size of the disk is not reported AFAIK but the free percent of it and free MB are.
First, I highly recommend to start with the table name (Perf), to avoid unneeded search of the entire DB.
To get the latest report I suggest using "arg_max", which would be more accurate than "summarize min". "arg_max" is intended exactly to return the the record that has a maximum value, in this case the record with the maximum TimeGenerated (meaning it is the latest record found). For example:
Perf | where TimeGenerated > ago(1d) | where ObjectName == "LogicalDisk" and CounterName == "% Free Space" | summarize (TimeGenerated, Free_Space_Percent)=arg_max(TimeGenerated, CounterValue) by Computer, InstanceName | where strlen(InstanceName) ==2 and InstanceName contains ":"
The above example will returns the maximum free space percent for each computer and instance:
The same can be done for free MB.
To combine the results of both calculations I recommend using "Join", which lets you match results by computer and instance names. See the join example here.
The results look like this:
HTH,
Noa
MicrosoftHi Rajinder Rahul,
Your question is very popular, indeed many times users want to get the latest report of a computer performance counter (such as free space). Note that the overall size of the disk is not reported AFAIK but the free percent of it and free MB are.
First, I highly recommend to start with the table name (Perf), to avoid unneeded search of the entire DB.
To get the latest report I suggest using "arg_max", which would be more accurate than "summarize min". "arg_max" is intended exactly to return the the record that has a maximum value, in this case the record with the maximum TimeGenerated (meaning it is the latest record found). For example:
Perf | where TimeGenerated > ago(1d) | where ObjectName == "LogicalDisk" and CounterName == "% Free Space" | summarize (TimeGenerated, Free_Space_Percent)=arg_max(TimeGenerated, CounterValue) by Computer, InstanceName | where strlen(InstanceName) ==2 and InstanceName contains ":"
The above example will returns the maximum free space percent for each computer and instance:
The same can be done for free MB.
To combine the results of both calculations I recommend using "Join", which lets you match results by computer and instance names. See the join example here.
The results look like this:
HTH,
Noa
Thanks Noa
That's a great help, would you know if I can get "% Used Space", would be good to add any other metrics I can regarding logicaldisk information
Thanks
- Noa Kuperberg
Hey,
You can do the same calculation with many disk counters.
I checked the reports from the last day to evaluate that (I believe it covers all or most of the possible disk counters)
- Disk Transfers/sec
- Current Disk Queue Length
- Avg. Disk sec/Write
- Avg. Disk sec/Read
- Disk Reads/sec
- % Free Space
- Free Megabytes
- Disk Writes/sec
Hello Noa Kuperberg,,
I am searching for a KQL query Disk read bytes and Disk writes bytes which not available in counter name. How can I get these counter name and results
If you are capturing the counters - please check here:
Then you can query them (after the data is available in the Workspace).
// Disk usage Perf | where TimeGenerated > ago(1h) | where CounterName == "Disk Read Bytes/sec" or CounterName == "Disk Write Bytes/sec" | project TimeGenerated, CounterName, CounterValue | summarize avg(CounterValue) by CounterName, bin(TimeGenerated, 1m) | render timecharthttps://ms.portal.azure.com#@72f988bf-86f1-41af-91ab-2d7cd011db47/blade/Microsoft_Azure_Monitoring_Logs/DemoLogsBlade/resourceId/%2FDemo/source/LogsBlade.AnalyticsShareLinkToQuery/q/H4sIAAAAAAAAA22PwQqCQBRF94L%252F8HClEIgfYIsK2kVE1PqpN51qnHgzUxh9fOWiNFo%252B7rnn8tKUFsqeyFuuEQZryCEMHnRrIKCt0liihbBDRVPi2sRZk3yBufGtg6xYg%252FKcol61AVc06xxsalFGREb%252Bg3tRDkPyLb6IOaJ04%252B3JUPA5dnz26EvWa82i7iC%252B1vEwTqjoxuVCtfGPPNP9T4K2gpB7hWXD4oIn%252BZ8NOR0BAAA%253D
Hi Noa
Sorry for the direct approach.. A quick question again, if I may.
I'm trying to find the Avg. Disk sec/Write and Avg. Disk sec/Read on disks in azure using log analytics, but I keep getting errors.
I tried modifying your query to the one below:
Perf
| where TimeGenerated > ago(7d)
| where ObjectName == "LogicalDisk" and CounterName == "Avg. Disk sec/Write"
| summarize (TimeGenerated, Avg_Disk_Write)=arg_max(TimeGenerated, CounterValue) by Computer, InstanceName
| where strlen(InstanceName) ==2 and InstanceName contains ":";
let Avg_Disk_Write=
Perf
| where TimeGenerated > ago(7d)
| where ObjectName == "LogicalDisk" and CounterName == "Avg. Disk sec/Read"
| summarize (TimeGenerated, Avg_Disk_Read)=arg_max(TimeGenerated, CounterValue) by Computer, InstanceName
| where strlen(InstanceName) ==2 and InstanceName contains ":";
Avg_Disk_Read
| join (
disk_free_MB
) on Computer, InstanceName
| project Computer, InstanceName, Avg_Disk_Write, Avg_Disk_ReadI then tried
Perf | where ObjectName == "Capacity and Performance" and (CounterName == "VHD Reads/s" or CounterName == "VHD Writes/s") | summarize AggregatedValue = avg(CounterValue) by bin(TimeGenerated, 7d), CounterName, InstanceName
None of which have helped, I was hoping you could point me in the right direction...
I'm trying to understand the IOPS for disks.
Hope you don't mind me contacting you directly, if you'd like me to raise it as a seperate question, please let me know..
Thanks
Gin
- Noa Kuperberg
