Forum Discussion
Need Some enhancement in query
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.
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.
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. :)
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.
