Splunk Search

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
Explorer

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.

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

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

View solution in original post

woodcock
Esteemed Legend

Like this:

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

View solution in original post

ahallak2016
Explorer

This worked great thanks 🙂

0 Karma

cmerriman
Super Champion

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

ahallak2016
Explorer

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.

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.