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