Plot the average of a field across all locations and have it as an overlay on a chart with the count of a field across a specific location

[ahallak2016]

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.

[woodcock · Accepted solution]

Like this:

Pivot Query | timechart count(eval(location="site 1" AND isnotnull(count))) AS count avg(count) AS avg

[ahallak2016]

This worked great thanks 🙂

[cmerriman]

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

[ahallak2016]

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.