SOLVED
Home

Need Some enhancement in query

Gourav Kumar
Contributor

Hi Team,

 

I am using below queries to check data of top 10 CPU, Memory and Disk information from OMS.

 

let TopComputers = Perf
| where ObjectName == 'Processor' and CounterName == '% Processor Time' and InstanceName == '_Total'
| summarize AggregatedValue = avg(CounterValue) by Computer
| sort by AggregatedValue desc
| limit 10
| project Computer;
Perf
| where ObjectName == 'Processor' and CounterName == '% Processor Time' and InstanceName == '_Total' and Computer in (TopComputers)
| summarize AggregatedValue = avg(CounterValue) by Computer, bin(TimeGenerated, 1h) | render timechart


-----------------------------------------------------------------------------------------------------------------------------------------------------


let TopComputers = Perf
| where ObjectName == 'LogicalDisk' and CounterName == '% Free Space' and InstanceName == 'C:'
| summarize AggregatedValue = avg(CounterValue) by Computer
| sort by AggregatedValue desc
| limit 10
| project Computer;
Perf
| where ObjectName == 'LogicalDisk' and CounterName == '% Free Space' and InstanceName == 'C:' and Computer in (TopComputers)
| summarize AggregatedValue = avg(CounterValue) by Computer, bin(TimeGenerated, 1h) | render timechart

 

-----------------------------------------------------------------------------------------------------------------------------------------------------

let TopComputers = Perf
| where ObjectName == 'Memory' and CounterName == 'Available MBytes'
| summarize AggregatedValue = avg(CounterValue) by Computer
| sort by AggregatedValue desc
| limit 10
| project Computer;
Perf
| where ObjectName == 'Memory' and CounterName == 'Available MBytes' and Computer in (TopComputers)
| summarize AggregatedValue = avg(CounterValue) by Computer, bin(TimeGenerated, 1h) | render timechart

 

 

But when i am running this for last 6 hour 1 hour and so on, it will giving me one computer many time (as it giving me information on all samples). So is there any way i can get information once with a computer name. Like if i will run for 1 day it will give me output the current time counter or an average value.

 

Please suggest on it, Because when i am giving this to any one who do not know about SCOM or OMS then first from his/her side is why servers names are two time. If i will say it is giving all samples then they asked run for one current sample.

 

So can i modified in this way.

 

Help would be highly appreciated. 

5 Replies

Hi Gourav, 

I do not understand your question(s) in the last two paragraphs. Are you trying to give query to someone and only want to summarize the total aggregated value over a time period?

 

you're queries worked fine in my environment they returned back the top ten, as expected.

Hi Billy,

 

Query is working fine, have you noticed its in the output servers name appears many time. I want to reduce this as single time with aggregated value.

 

Generally we could say, i am not looking for every sample data. It would be great be if i can query and summarize the computer name once for a time period.

 

I have attached a screenshot where we could see a servers has 4 samples, one has 3 and others has 2 samples so on.

 

So i want result in the form of single aggregated sample.  No server should not be repeat in output.

 

Hope i made it clear now. :)

 

 

Solution

I get it now, for a single aggregated value, remove bin(TimeGenerated, 1h) and | render timechart

as the render timechart would be useless in this instance and only give you a single dot per computer.

 

So you'll need two separate queries for charting that uses the bin(TimeGenerated, 1h) and one without for the single aggregated value over whatever timespan you want. I hope that answers your question.

Hi Billy,

Thanks for the prompt answer got your point now.

 

Could be made the below query in same way:-

Perf
| where (ObjectName == "Memory" and CounterName == "% Committed Bytes In Use")
or (ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total")
and TimeGenerated < now(24h)
| summarize avg(CounterValue) by bin(TimeGenerated, 1h), Computer, ObjectName
| evaluate pivot(ObjectName, avg(avg_CounterValue))
| project TimeGenerated, Computer, Processor, Memory

 

 

I have removed bin(TimeGenerated, 1h) in the above query but then it would not worked. and added bin(TimeGenerated, 1h) by computer as well but no luck :)

 

If you removed bin(TimeGenerated, 1h) from that query, you would still need to remove the TimeGenerated after the project.

 

try this

Perf
| where (ObjectName == "Memory" and CounterName == "% Committed Bytes In Use")
or (ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total")
and TimeGenerated < now(24h)
| summarize avg(CounterValue) by Computer, ObjectName
| evaluate pivot(ObjectName, avg(avg_CounterValue))
| project Computer, Processor, Memory