Splunk Search

Displaying average from a timechart

Path Finder

I've read many posts on the subject of displaying an average line across a chart. But I can't find a solution that doesn't require performing the search twice, as in a join. This seems like a real common problem based on the number of similar posts. So my question is can you display the average somehow over the chart of discrete values without the extra search?

The search looks like
index=.. search params earliest=-1h | timechart span=1m cont=f avg(DPS) as DocsPerSec

So my table data looks like this:
time DocsPerSec
9:23 120
9:24 (null)
9:25 1545
9:26 756

So what I'm looking for is a column chart for the individual data points, and the average of all data points for the entire time. It's like having a span value of 1m for the discrete data and a span value of 1h for the average. I know the trendline can produce a running average, but that's not the same thing.

Maybe it's not possible because the average isn't known until all time has been processed? I think I could use the search in a post process event in a dashboard to get a single value for the average - to be displayed elsewhere. Maybe that's the best bet.

SplunkTrust
SplunkTrust

Since you want ultimately to end up charting the raw points themselves, you have to not timechart at all, because timechart will throw away the full granularity. You can however use a combination of bin and eventstats to calculate the same averages, and most importantly, do it without a join or append.

<searchterms> | eval bucketedTime=_time | bin bucketedTime span=1m | eventstats avg(DPS) as DocsPerSec by bucketedTime | fields _time DPS DocsPerSec

Coming out of that, you'll have basically the same rows that went in, except that each and every event row will have a field called "DocsPerSec" that represents the average DPS for the given minute.

There are or at least used to be a lot of answers posts that tried to talk people out of "charting the raw events". for one thing it can end badly when one day there are a huge number of rows. Also the jackson-pollock effect is less awesome than you might think.

So I would encourage you to take another look at more statistical approaches.

<searchterms> | timechart span=1m avg(DPS) perc95(DPS) perc5(DPS)

The perc95 and perc5 and percN terms can serve as nice clean ways to see the distribution spiking over time, withouth having to chart thousands of points. ymmv

UPDATE:

it might work better to have a working example of what I mean that you can paste in.
You can paste this in and you'll have this data locally (well, you'll have to be an admin user in splunk)

index=_internal component=metrics group=per_sourcetype_thruput series=splunkd | table _time kb | eval binnedTime=_time | bin binnedTime span=10min | eventstats avg(kb) by binnedTime | fields - binnedTime

the idea here is the same. We ultimately want to see both the raw kb values plotted over time, as well as the average-per-ten-minute-period plotted alongside.

Run that search and then switch to "visualization" and then set it to "Line" and hopefully this will help .

Path Finder

I removed the timechart and added your phrases, but the events came back raw, not tabularized. There must be some other prep work for charting.

0 Karma

SplunkTrust
SplunkTrust

it's because you're in the default splunk ui I think. Change the | fields _time DPS DocsPerSec to | table _time DPS DocsPerSec and it'll force it to think of the rows as transformed rows.

0 Karma

Path Finder

Still no luck, but I'm not married to one display option. I've also been trying to show the average as a single value. Not sure how to do "eval" type operations outside of a search:

< module name="HTML" >
< param name="html" >DPS average: sum($results.DocsPerSec$)/$results.count$< /param >
< /module >

Is there a module that can do aggregation functions and basic math with results fields?

Path Finder

Also, how do you get those handy code callout blocks to appear?

0 Karma

SplunkTrust
SplunkTrust

In the answers site you can either have a newline and then four space characters, or you can enclose it in backtick chars (just like a splunk macro). The backtick char is just to the left of the "1" on most US keyboards and is commonly mistaken for an apostrophe.

BTW I updated my answer to include a working example of this against index=_internal data.

0 Karma

Path Finder

Thanks! That's really close. The only thing is that the average has the same bin size as the raw data. I decided to show the average as a single value - with a post process module.

0 Karma

SplunkTrust
SplunkTrust

This should do the trick

index=.. search params earliest=-1h | timechart span=1m cont=f avg(DPS) as DocsPerSec | eventstats avg(DocsPerSec) as HourAvg
0 Karma

Path Finder

Just adding on the eventstats clause did not provide any more series data. Is something else implied that I'm not aware of?

0 Karma

SplunkTrust
SplunkTrust

The field names after the timechart execution would be _time and DocsPerSec and then eventstats would give your a new field/series called HourAvg. If the field name is different (if you're not using exact query which I wrote), then you need to update the same in both timechart and eventstats.
OR if you can provide the query that you ran.

0 Karma