Report on selected servers for a week on the memory usage

Brass Contributor

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();

15 Replies

@Arslan11 

 

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 

 

Annotation 2020-04-30 201621.jpg

 

 

 

 

@CliveWatson  Thanks , I tired your query but it didn't answer my question, I have  pasted the query.

 

| where TimeGenerated > startofday(ago(1d))
| 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
 
 
what I really want , I want my ""VM-WVD-REL86-5.networkhg.org.uk" server to show me the results of memory usage for 5 days.
 
 

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

 

@Arslan11 

 

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

 
| where TimeGenerated > startofday(ago(5d))
 
and

 

| summarize Used_Percent_Memory = max(CounterValue)by bin(TimeGenerated,1d) , Computer, ObjectName

 

 

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.

 

Annotation 2020-05-01 084653.jpg

 

 

@CliveWatsonThanks, you are a star:smile:, if I have any more Kusto querys, I will let you know, thanks again

@CliveWatson 

 

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

Perf
| where TimeGenerated > startofday(ago(5d))
| where ObjectName == "Memory" and CounterName == "% Committed Bytes In Use"
| 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

 

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

Capture1.PNG

 

Please refer the above graph , not sure why this difference is coming

 
 

 

 

 

@SamsonJohn 

 

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)

 

summarize Used_Percent_Memory = max(CounterValue)by bin(TimeGenerated,1d) , Computer, ObjectName
where Used_Percent_Memory < 90 and Used_Percent_Memory > 70

 

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 

 

@CliveWatson 

 

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 .

 

@CliveWatson 

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 . 

Swap this line

max(CounterValue)

to say

avg(CounterValue)

@CliveWatson 

 

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.

 

@SamsonJohn 

 

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.

Annotation 2020-05-07 090616.jpg

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

Perf
| where TimeGenerated > ago(1h)
| where Computer == "retailEUS7"
| where ObjectName == "Memory" and CounterName == "% Committed Bytes In Use"
| summarize maxUsed_Percent_Memory = max(CounterValue),
avgUsed_Percent_Memory = avg(CounterValue)
by bin(TimeGenerated,1m) , Computer
| project-away Computer
| render timechart

 

I think it unlikely Avg is the same as Max. See this example:

 

Annotation 2020-05-07 091802.jpg

 

 

@CliveWatson

Sure to Answer the question please find below 

1) Perfmon Set is as below 

SamsonJohn_0-1588916327951.png

 

2) After executing the query

SamsonJohn_1-1588917458788.png

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.

 

@SamsonJohn 

 

Ok, so you avg and max are the same (some workloads can be).  Have you setup Perfmon to match your settings, something like?

Annotation 2020-05-10 164613.jpg

Are you still seeing a ~10% difference?

 

@CliveWatson 

Yes even after doing those changes I am seeing the difference .

 

SamsonJohn_1-1589342259525.png

And the Actual Memory Utilization is as below

SamsonJohn_2-1589342431463.png

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.

 

 

 

 

@SamsonJohn 

 

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?