You may have come across a previous post of mine about monitoring servers CPU usage. If not, you can find it at https://pixelrobots.co.uk/2019/06/monitor-your-servers-cpu-usage-using-azure-log-analytics/. The post you are reading now goes hand in hand with the previous post to help you monitor your servers. In this post, I will go through the query I use and will show you how to pin it to a dashboard, but first, we have some prerequisites to get out of the way first.
The prerequisites
You will need to have the Azure monitor agent installed on the VM’s you want to monitor. You can even monitor none Azure servers too. You can find more about installing the agent at https://docs.microsoft.com/en-us/azure/azure-monitor/platform/log-analytics-agent#install-and-configure-agent
You will also need to enable extra data sources. For this guide, it will be performance counters. You can read more about this at https://docs.microsoft.com/en-us/azure/azure-monitor/platform/data-sources-performance-counters. Basically, if you have not enabled performance counters in you Log Analytics Workspace you will need to. To do this go to your Log Analytics Workspace click Advanced Settings, then click Data, now click Windows Performance Counters, and finally click Add the selected performance counters.
Now that you have that out of the way, lets get to it.
The Query
| // Chart memory if its under nnMB over the past nn days/hours | |
| let setMBValue = 1024; | |
| // enter a MB value to check | |
| let startDate = ago(5h); | |
| // enter how many days/hours to look back on | |
| Perf | |
| | where TimeGenerated > startDate | |
| | where ObjectName == "Memory" and CounterName == "Available Mbytes" and Computer in ((Heartbeat | |
| | where OSType == "Windows" | |
| | distinct Computer)) | |
| | extend FreeMemory = CounterValue | |
| | summarize FreeMemoryMB = min(FreeMemory) by Computer | |
| | where FreeMemoryMB < setMBValue | |
| | summarize max(FreeMemoryMB) by Computer | |
| | join | |
| ( | |
| Perf | |
| | where TimeGenerated > startDate | |
| | where ObjectName == "Memory" and CounterName == "Available Mbytes" and Computer in ((Heartbeat | |
| | where OSType == "Windows" | |
| | distinct Computer)) | |
| | extend FreeMemory = CounterValue | |
| ) | |
| on Computer | |
| | make-series Free_Memory_MB = min(FreeMemory) on TimeGenerated from ago(1d) to now() step 2h by Computer | |
| | render timechart |
Explaining the query
So this bit:
| // Chart memory if its under nnMB over the past nn days/hours | |
| let setMBValue = 1024; | |
| // enter a MB value to check | |
| let startDate = ago(5h); |
Is the only bits you will need to change. Let setMBValue is the MB value you want to set as a threshold. So, in this case, I am using 1024MB aka 1GB. Let startDate is how many hours or days you want the chart over. I have used 5 Hours. You could choose the whole working day of 7h+30m if you like. Totally up to you.
| Perf | |
| | where TimeGenerated > startDate | |
| | where ObjectName == "Memory" and CounterName == "Available Mbytes" and Computer in ((Heartbeat | |
| | where OSType == "Windows" | |
| | distinct Computer)) | |
| | extend FreeMemory = CounterValue | |
| | summarize FreeMemoryMB = min(FreeMemory) by Computer | |
| | where FreeMemoryMB < setMBValue | |
| | summarize max(FreeMemoryMB) by Computer |
Here we do our initial query that looks at the performance counter Available Mbytes for the Server. It is set to only query servers with the OS type windows and only when the Available memory is under the value you set before.
| | join | |
| ( | |
| Perf | |
| | where TimeGenerated > startDate | |
| | where ObjectName == "Memory" and CounterName == "Available Mbytes" and Computer in ((Heartbeat | |
| | where OSType == "Windows" | |
| | distinct Computer)) | |
| | extend FreeMemory = CounterValue | |
| ) | |
| on Computer | |
| | make-series Free_Memory_MB = min(FreeMemory) on TimeGenerated from ago(1d) to now() step 2h by Computer | |
| | render timechart |
The query then does a join on the initial query with the second query, which is basically the same, but without the summarize as it’s not needed now. You then make a series of the data on the time generated from the time you set above to now (as in when the query runs) and step it by 10 minutes. You could change the step if you like, I found 10 minutes to be a good fit for me. The last line then renders a nice chart for you.
Running the query
If you run the query in your log analytics log window and have servers with less than 1024mb of available memory you should see something like this.
You will notice that even though the server does not currently have less than the set available memory it is still showing in the graph. This is by design to help you look for issues or patterns over the time period set.
Dashboards?
It’s is very easy to pin this chart to a dashboard, in fact, all you have to do is click the Pin button at the top right of the query window. This will then ask you what dashboard to save the Chart to. One thing to note is you will have needed to create a dashboard and share it first. Unfortunately, you are unable to create a dashboard from the pin drop down.
There you have it you can now monitor both Azure and non-Azure servers available memory usage using Azure Log Analytics and Azure Dashboards.
I hope you found this article helpful. If you have any questions or comments please reach out in the usual ways.
3 Comments
Tony · June 28, 2019 at 1:56 pm
Hi Richard, great site.
I get a syntax error when trying to run this query – does it work ok your side?
Pixel Robots. · June 28, 2019 at 7:45 pm
Thanks for finding that. The code plugin decided to change a < to some random text. Should be fixed now.
Irfan AHmed · April 11, 2020 at 6:47 am
Hi Robot, you have done some fantastic work. Just want to let you know that the memory counter is case sensitive and your query in current format will not return the records. Available Mbytes should be Available MBytes. Please check. I am new to log analytics but this is what i observed.