I am trying to do a timechart on the number of rows on a particular location as shown below.
Pivot Query | search location="site 1" | timechart count(count) as count
But I would also like to find the average of all locations.
Pivot Query | timechart avg(count) as avg
I am trying to perform the above in the one query, basically I want the average to be across all locations but as an overlay on the chart which has the count of a specific location. I have been trying to perform the above with a join on _time and another go with eventstats but i don't think its accurate.
Any help would be greatly appreciated.
Like this:
Pivot Query | timechart count(eval(location="site 1" AND isnotnull(count))) AS count avg(count) AS avg
Like this:
Pivot Query | timechart count(eval(location="site 1" AND isnotnull(count))) AS count avg(count) AS avg
This worked great thanks 🙂
try something like this:
Pivot Query |appendpipe [| timechart avg(count) as avg] | timechart count values(avg) as avg by location|rename "count: *" as * "*: NULL" as *|fields _time "site 1" avg
This did work after i put in limit=0
after the by location
statement. Its because we have alot of locations and the one selected was grouped in the OTHER category. Also, this solution ran a little slower then the other proposed solution which i think is because of the appendpipe command.