SOLVED

Help with Disk query in Log Analytics

Copper Contributor

Hi

 

I was wondering if I could get some help with Log analytics. New to this so bear with me.

 

I'm trying to create a query that will provide informtaion on disk utilisation in Azure. I've gottwo commands (below), however I'm not able to merge them as I would like one query which gives me % free space, overall size of disk, name of vm and name of disk. Anything else I can get in terms of disk usage would be great, not overly concerned with IOPs at the moment.

 

The commands are:

Thsi proivides info on free space: 

search ObjectName == "LogicalDisk" and CounterName == "% Free Space"

 

This one provides information on free Mb remaining.

search ObjectName == "LogicalDisk" and CounterName == "Free Megabytes"

 

I have tried this which helps, but again information is quite limited

search ObjectName == "LogicalDisk" and CounterName == "Free Megabytes" and TimeGenerated > ago(1d)
| summarize FreeSpace = min(CounterValue) by Computer, InstanceName
| where strlen(InstanceName) ==2 and InstanceName contains ":"

 

Thanks in advance :)

 

14 Replies
best response confirmed by Rajinder Rahul (Copper Contributor)
Solution

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:

arg_max.png

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:

join.pngHTH,

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 

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

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_Read

 

I 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

 

 

 

Hey Gin,

Try this query.

 

HTH,

Noa

Hi Noa

 

Can you please help me with query for the vm names starting with " ZEXXXXX0000- 

  1. Memory utilization average for a cloud environment/subscriptions over a period of 30 days.
  2. C:\ and all data drives utilization over a period of 30 days.

Thanks

Satish

Hi Satish,

Please try to post new questions as new posts, it will help us understand the real subject, and other users can perhaps find it useful too.

To your questions,

1. Here's an example for calculating the average free memory over the last 30 days. Note that we don't query an entire subscription, we query a workspace:

Perf
| where TimeGenerated > ago(30d) 
| where Computer startswith "Contoso"
| where ObjectName == "Memory" and 
  (CounterName == "Available MBytes Memory" or // the name used in Linux records
  CounterName == "Available MBytes")          // the name used in Windows records
// calculate the average free memory for each computer
| summarize avg_free_memory=avg(CounterValue) by Computer

2. Free disk space:

Perf
| where TimeGenerated > ago(30d)
| where ObjectName == "LogicalDisk" or  // the object name used in Windows records
  ObjectName == "Logical Disk"          // the object name used in Linux records
| where CounterName == "Free Megabytes"
| summarize avg_free_disk_MB=avg(CounterValue) by Computer, InstanceName

HTH,

Noa

How to check the disk failure in log analytics


@Rajinder Rahul wrote:

Hi

 

I was wondering if I could get some help with Log analytics. New to this so bear with me.

 

I'm trying to create a query that will provide informtaion on disk utilisation in Azure. I've gottwo commands (below), however I'm not able to merge them as I would like one query which gives me % free space, overall size of disk, name of vm and name of disk. Anything else I can get in terms of disk usage would be great, not overly concerned with IOPs at the moment.

 

The commands are:

Thsi proivides info on free space: 

search ObjectName == "LogicalDisk" and CounterName == "% Free Space"

 

This one provides information on free Mb remaining.

search ObjectName == "LogicalDisk" and CounterName == "Free Megabytes"

 

I have tried this which helps, but again information is quite limited

search ObjectName == "LogicalDisk" and CounterName == "Free Megabytes" and TimeGenerated > ago(1d)
| summarize FreeSpace = min(CounterValue) by Computer, InstanceName
| where strlen(InstanceName) ==2 and InstanceName contains ":"

 

Thanks in advance :)

 


 

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

@Prince0103

 

If you are capturing the counters - please check here:

 

clipboard_image_0.png

 

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 timechart

Go to Log Analytics and Run Query

clipboard_image_1.png

Hello @CliveWatson,

 

Thank you for answer it works for me. 

 

Could you please suggest me the KQL queries for Network In Total and Network Out Total, because I am getting two objects name here(Network Interface and Network Adapter) which Objects I should use and what couter name I should use for Network In/Out.

 

Kindly suggest me on this.

@Prince0103 

 

A Network Adapter is typically a piece of hardware (or virtual hardware).  The network interface is built in software. Usually, there is one network interface per adapter.

 

How are you configured and what do you want to show?  Just insert the counternames that make sense to you to the previous example.

 

 

@Noa Kuperberg 

Hi Noa, the "% Free Space" query somehow does not show any data for me.
I have a log analytics workspace with 2 VMs connected. 

Any idea why they dont show? What setting am I missing here ?

@Noa Kuperberg 

Hi Noa,

I've followed your approach to find disk size of each drive for Windows VM's and it is working well. I've validated it against the disk size in portal and they are matching!!

But when I tried to use the same approach for Linux VM's it's not working.

I want to find the size of each data disk from two metrics : % Used Space and Free Megabytes.

Linux Vm's have % Used Space metric instead of % Free Space. So, I've calculated the % Free Space by subtracting % Used space from 100.

I've calculated disksize = (((Free Megabytes/(100 - % Used Space))*100)/1024).

But the resultant disk size did not match with the disk size in portal.

Can you please help me regarding this

 

Thank you  

1 best response

Accepted Solutions
best response confirmed by Rajinder Rahul (Copper Contributor)
Solution

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:

arg_max.png

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:

join.pngHTH,

Noa

 

View solution in original post