Forum Discussion

Brass Contributor

Apr 30, 2020

Report on selected servers for a week on the memory usage

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...

Azure Log Analytics

azure monitor

CliveWatson

Silver Contributor

Apr 30, 2020

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

Arslan11

Brass Contributor

Apr 30, 2020

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

CliveWatson

Silver Contributor

May 01, 2020

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.

SamsonJohn
Copper Contributor
May 04, 2020
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

Please refer the above graph , not sure why this difference is coming
- CliveWatson
  Silver Contributor
  May 04, 2020
  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
Arslan11
Brass Contributor
May 01, 2020
CliveWatsonThanks, you are a star, if I have any more Kusto querys, I will let you know, thanks again