Need Some enhancement in query

[GouravIN]

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. 

[#2]

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.

[#3]

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

[#4 · Accepted 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.

[#5]

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 :)

[#6]

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