Apr 30 2020
10:08 AM
- last edited on
Apr 08 2022
10:25 AM
by
TechCommunityAP
Apr 30 2020
10:08 AM
- last edited on
Apr 08 2022
10:25 AM
by
TechCommunityAP
I would like a help with a Kusto query which I have almost completed but I am struggling in the last part.
Kusto query
Perf
| where ObjectName == "Memory" and CounterName == "% Committed Bytes In Use" and Computer == "VM-WVD-REL86-5.networkhg.org.uk"
| extend Used_Percent_Memory = 100- CounterValue
| summarize Used_Percent_Memory = max(CounterValue) by Computer, ObjectName
| where Used_Percent_Memory < 90 and Used_Percent_Memory > 70
This query shows me percent memory being used over 70% for specific server
But I would like to add a extra line in this query, where it will show me the memory percent for 5 days for this relevant server
I found this but I am not sure where to exactly added within this query.
let start_time=startofday(datetime("2018-03-01"));
let end_time=now();
Apr 30 2020 12:16 PM
Perf
| where TimeGenerated > startofday(ago(5d)) // from midnight, just use ago(5d) for 5days before now
| where ObjectName == "Memory" and CounterName == "% Committed Bytes In Use"
//and Computer == "VM-WVD-REL86-5.networkhg.org.uk"
| extend Used_Percent_Memory = 100- CounterValue
| summarize Used_Percent_Memory = max(CounterValue) by Computer, ObjectName
| where Used_Percent_Memory < 90 and Used_Percent_Memory > 70
Computer | ObjectName | Used_Percent_Memory |
---|---|---|
Computer1 | Memory | 77.8518753051758 |
Computer2 | Memory | 83.6081695556641 |
You could also use startofweek()
Perf
| where TimeGenerated > startofweek(now())
| where ObjectName == "Memory" and CounterName == "% Committed Bytes In Use"
//and Computer == "VM-WVD-REL86-5.networkhg.org.uk"
| extend Used_Percent_Memory = 100- CounterValue
| summarize Used_Percent_Memory = max(CounterValue), min(TimeGenerated) by Computer, ObjectName
| where Used_Percent_Memory < 90 and Used_Percent_Memory > 70
Computer | ObjectName | Used_Percent_Memory | min_TimeGenerated |
---|---|---|---|
Computer1 | Memory | 77.8518753051758 | 2020-04-27T09:00:52.997Z |
Computer2 | Memory | 83.6081695556641 | 2020-04-26T00:00:03.257Z |
You can also compare weeks, example
Perf
| where TimeGenerated > startofday(ago(14d))
| where Computer startswith "retail"
| where ObjectName == "Memory" and CounterName == "% Committed Bytes In Use"
| extend Used_Percent_Memory = 100- CounterValue
| summarize This_week_Used_Percent_Memory = max(CounterValue) by bin(TimeGenerated, 1d), Computer, ObjectName
| order by TimeGenerated asc
| extend PrevWeek=prev(This_week_Used_Percent_Memory, 7)
| where TimeGenerated > ago(7d)
| project TimeGenerated, This_week_Used_Percent_Memory , PrevWeek
| render timechart
Apr 30 2020 03:08 PM - edited Apr 30 2020 03:14 PM
@CliveWatson Thanks , I tired your query but it didn't answer my question, I have pasted the query.
I have attached the picture to this post, table is showing value for 1 day but I want it to show value for the 5 days. for instance , I want the query to show the memory usage for Monday, Tuesday, Wednesday, Thursday and Friday for the server ""VM-WVD-REL86-5.networkhg.org.uk". I hope , you will work this out
May 01 2020 12:52 AM
I see now, so change to this:
Perf
| where TimeGenerated > startofday(ago(5d))
| where ObjectName == "Memory" and CounterName == "% Committed Bytes In Use"
| where Computer startswith "retail"
| extend Used_Percent_Memory = 100- CounterValue
| summarize Used_Percent_Memory = max(CounterValue)by bin(TimeGenerated,1d) , Computer, ObjectName
| where Used_Percent_Memory < 90 and Used_Percent_Memory > 70
We take 5 days of data, then use a BIN in the summarise line to group the results per day
TimeGenerated | Computer | ObjectName | Used_Percent_Memory |
---|---|---|---|
2020-05-01T00:00:00Z | retail | Memory | 83.4487609863281 |
2020-04-28T00:00:00Z | retail | Memory | 83.6081695556641 |
2020-04-30T00:00:00Z | retail | Memory | 83.2831649780273 |
2020-04-26T00:00:00Z | retail | Memory | 83.5125961303711 |
2020-04-29T00:00:00Z | retail | Memory | 82.2031326293945 |
2020-04-27T00:00:00Z | retail | Memory | 81.8324279785156 |
e.g.
May 01 2020 07:12 AM
@CliveWatsonThanks, you are a star, if I have any more Kusto querys, I will let you know, thanks again
May 04 2020 12:09 PM
Hello Clive thanks for the query , but what I observe is the difference in the actual memory in the Virtual Machine when compared with query output.
I used this query
So when a login to VM if I check , the latest it show memory less than 80 for live0 and live1 and for DB it is 87 % memory utilized
Please refer the above graph , not sure why this difference is coming
May 04 2020 12:23 PM
In this section of the query we look at memory between 70 & 90 %, we take the max value seen on a given day (the BIN takes all the daily values and gives us one figure)
So when a login to VM if I check , the latest it show memory less than 80 for live0 and live1 and for DB it is 87 % memory utilized
So if memory is above 70% you will see it, based on the max value for that day, which isn't necessarily the current value.
You may need to look at the past hour broken into 1m BINS for this?
Perf
| where TimeGenerated > ago(1h)
| where ObjectName == "Memory" and CounterName == "% Committed Bytes In Use"
| extend Used_Percent_Memory = 100- CounterValue
| summarize Used_Percent_Memory = max(CounterValue) by bin(TimeGenerated,1m) , Computer, ObjectName
//| where Used_Percent_Memory < 90 and Used_Percent_Memory > 70
| render timechart
May 05 2020 12:51 AM
Hi Clive,
I can still find the difference in percentage of the memory utilized in VM .
Please let me know if the query can be modified to get the average utilization of memory so as to get accurate utilization .
May 05 2020 04:04 PM
Hi Clive , I have used the query to fetch details but it is giving me differences in the values for 10% when compared to actual memory utilization in Server and causing the invalid alert since I have set it for memory utilization >90 % based on this query.
Am check as this as well , any suggestion or helps in regards for this appreciated .
May 06 2020 06:57 AM
May 06 2020 04:20 PM
I have tried this replace max with avg as well but there is no differences here the result is same.
still not returning the correct data for memory
Was also going through this post https://sites.google.com/a/thetnaing.com/therunningone/how-to-calculate-systems-memory-utilization
Was wondering do we need to apply the correct formula to evaluate the memory ,please suggest in regards to the same.
May 07 2020 01:27 AM
Two questions,
1. what is perf collection interval for the memory counter? My thoughts are if you are collecting to a different interval to Perfmon you may see variation based in aggregation.
2. Are you using Perfmon (the counters and data is the same, apart from the collection interval); Task Manager can show different results.
Go to Log Analytics and run query
I think it unlikely Avg is the same as Max. See this example:
May 07 2020 11:00 PM
Sure to Answer the question please find below
1) Perfmon Set is as below
2) After executing the query
So both are coming same avg and max, there is no change .
Please let me know if you have any updates in regards to the same.
May 10 2020 08:48 AM
Ok, so you avg and max are the same (some workloads can be). Have you setup Perfmon to match your settings, something like?
Are you still seeing a ~10% difference?
May 12 2020 09:04 PM
Yes even after doing those changes I am seeing the difference .
And the Actual Memory Utilization is as below
So In actual the query is returning the memory committed percent the actual utilized memory (in the above image is 77 percent)
I feel we need to change the query for the actual percent.
May 13 2020 12:59 AM
Log Analytics stores the same data as Perfmon, do the Perfmon charts match your Log Analytics query? That's the apples to apples comparison. Now I'm in no way saying this is the issue, but Task Manger could be showing a different view, one example https://sqlinthewild.co.za/index.php/2016/01/19/stop-using-task-manager-to-check-sqls-memory-usage/.
I can only help you compare Perfmon with Log Analytics - are these different for the same time period?