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

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
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...