topic Re: Single value panel with trend based on avg(count) in Splunk Search

Single value panel with trend based on avg(count)

marco_carolo — Tue, 31 Aug 2021 14:56:35 GMT

Hello there.

What I'm trying to do is the following:

search | bucket span=60s _time | stats count by _time | ...

I want to achive if possible the following:

Calculate the average per minute of count of search (if I concatenate the stats avg(count) I get the actual value) but I can't:
Have the Single Value panel inside my dashboard to correctly display the trend based on average values.

Is there any way to achive this result?

At the moment each try I do to compare those values is not going well 😞

Re: Single value panel with trend based on avg(count)

gcusello — Tue, 31 Aug 2021 15:20:32 GMT

Hi @marco_carolo,

let me understand your need:

you want to display a value (average for minute of count of events) and you want e.g. the average of the last hour and the trend respect the previous hour , is it correct?

If this is your need, try something like this:

as test you could see this:

Ciao.

Giuseppe

Re: Single value panel with trend based on avg(count)

marco_carolo — Tue, 31 Aug 2021 15:41:40 GMT

Ciao Giuseppe 🙂

What I need to do, or better, what I was asked to do is this:

Get the avarage count of calls per minute in 10 minutes.

What I wanted to do is having the single value panel displaying the value of the current average per minute, plus the trend of the previous avarages per minute in 10 minutes timespan, so I can see if the value is increasing or decreasing.

Re: Single value panel with trend based on avg(count)

gcusello — Tue, 31 Aug 2021 15:43:25 GMT

Hi @marco_carolo,

you can use my search with a different timespan

Ciao.

Giuseppe

Re: Single value panel with trend based on avg(count)

marco_carolo — Tue, 31 Aug 2021 15:56:20 GMT

It seems that I'm having different results:

search | bucket span=1m _time | stats count by _time | stats avg(count) is resulting now 386 (which seems to be correct, taken the average of all the value got by the count)

The result of your query is resulting 269...

Re: Single value panel with trend based on avg(count)

gcusello — Tue, 31 Aug 2021 15:57:53 GMT

Hi @marco_carolo,

only for debugging, fix a past period of analysis: e.g. from -80 mites to -60 minutes, otherwise the results are continously changing!

Then, probably it's an error in the message, but, you need to use two times bucket, one before each stats.

Ciao.

Giuseppe

Re: Single value panel with trend based on avg(count)

PickleRick — Wed, 01 Sep 2021 12:53:17 GMT

Bucketing is usually not needed if you just need to do a timeseries calculation. There's a separate command for this - timechart.

And I'd approach this by "adjusting" time...

<<your search>> | timechart count span=1m 
| eventstats max(_time) as maxtime
| eval _time=if(_time=maxtime,maxtime,maxtime-60)
| stats avg(count) by _time

This way you get a value for the first minute and the average value per minute for the remainder of your search period. And now you can use the single value visualisation with a trend comparison to a value "a minute before".

EDIT: As this was marked as solution - please see my other solution further down the thread because this one was based on misunderstanding on what the OP really needed.

Re: Single value panel with trend based on avg(count)

marco_carolo — Wed, 01 Sep 2021 06:37:48 GMT

@PickleRick

This search is giving me strange results... I expect result approx around 400 and I'm getting 12-16 instead...

Re: Single value panel with trend based on avg(count)

marco_carolo — Wed, 01 Sep 2021 07:40:15 GMT

@PickleRick

it seems that the value of avg is referred to the actual minute, and not to the average of all the minutes in the 10m timespan...

I've to take 10 values (one count for each minute in the last 10 minutes)

Make the average of those count

Have the treshold displaying if this average is increasing or decreasing

Re: Single value panel with trend based on avg(count)

PickleRick — Wed, 01 Sep 2021 08:26:44 GMT

I don't understand 🙂

I thought you wanted a value of count of events from the last minute and the average per-minute value from some previous minutes.

That's what this does. Firstly it calculates per-minute statistics with |timechart.

Then it adds a field containing a timestamp of the latest minute (so we can differentiate between the latest minute and the previous ones).

Then it rewrites the timestamp for the remainding minutes so they can be aggregated with stats.

And finally | stats avg(count) gives you two values - one is an "average" value from a single value from last minute and the other is an average calculated from previous minutes.

Is that not what you wanted?

Re: Single value panel with trend based on avg(count)

marco_carolo — Wed, 01 Sep 2021 08:34:41 GMT

The request I got is to calculate the average calls to a specific function per minute, in a 10 minute window.

What my team leader expects is a single value. In the last 10 minute, this function is called averagely 400 times per minute.

What I'm doing is setting the bucket, calling the count, and then calling the average over the got values.

What I want to add is the trend and the sparkiline in a single counter panel inside my dashboard, so I can see if the average value is rising or decreasing.

Re: Single value panel with trend based on avg(count)

PickleRick — Wed, 01 Sep 2021 09:36:04 GMT

Ahhh... I thought you wanted to have a value from last minute compared to an average of previous values.

But you simply want a moving average over a sliding window.

Just use the timechart (as I wrote before - it's a dedicated command for analysing time series so there's no need to fiddle manually with buckets) and do a streamstats.

<<your_search>> | timechart count span=1m | streamstats window=10 avg(count)

Alternatively you can use |trendline

<<your_search>> | timechart count span=1m |  trendline sma10(count)

Remember than with time aligned to full minutes you might want to set your search time range to full minutes otherwise you'll get "not-full" values at the ends of the range since they correspond to only fractions of a minute.

Re: Single value panel with trend based on avg(count)

marco_carolo — Wed, 01 Sep 2021 09:45:22 GMT

@PickleRick

Can you be more specific about how to get full values instead of partial for each minute?

I need to achive that...

Re: Single value panel with trend based on avg(count)

PickleRick — Wed, 01 Sep 2021 11:11:20 GMT

If you do at - let's say - 10:35:15 a |timechart with span=1m over last 5 minutes, you'll get data from 10:30:15 to 10:35:15 split into a minute-long buckets, aligned at full minutes. So you'll get 6 buckets - one containing events from 10:30:15 to :10:31:00, another one from 10:31:00 to 10:32:00 and so on up to 10:35:00-10:35:15.

Obviously, the first and last buckets will be smaller than the rest of them.

That's the default behaviour of the |timechart

You can however use partial=f option for |timechart wich will omit the not-full first and last buckets from the result. In our example case you'd get only four buckets - 10:31-10:32, 10:32-10:33, 10:33-10:34 and 10:34-10:35.