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