topic Can't get timechart average to work in Splunk Search

Can't get timechart average to work

benhooper — Mon, 10 Aug 2020 12:00:10 GMT

I'm trying to get the average time that a case is open in a system.

To get the latest event per case that's closed and calculate the time between open and close, I use the following search (I'll refer to this as "<base command>") which works:

index="<client>" case_id | dedup 1 case_id sortby -_time | search (status!=new AND status!=under_investigation) | convert mktime(_time) as modification_time_epoch | eval creation_time_epoch = strptime(creation_time, "%Y/%m/%d %H:%M:%S") | eval timedifference_seconds = modification_time_epoch - creation_time_epoch | eval timedifference_minutes = timedifference_seconds / 60 | eval timedifference_hours = timedifference_minutes / 60

Appending ' | timechart avg(timedifference_hours)' doesn't work as expected because it simply uses the last value instead of the average across all of the values.

Appending ' | stats avg(timedifference_hours)' does correctly calculate the average but I can't get that output to be accepted by timechart.

I've tried every solution that I can find online but none of them have worked, hence this post.

Re: Can't get timechart average to work

to4kawa — Mon, 10 Aug 2020 09:31:46 GMT

Re: Can't get timechart average to work

benhooper — Mon, 10 Aug 2020 09:40:06 GMT

Thanks for that but it seems to not work:

All sorts of unexpected columns are returned ("1156", "1157", "1232", etc - times?).
A span of 1 hour returns "0 → 0".
A span of 7 days returns "0 ⇲ -199,122", etc.

Also, it may have been unclear in the original post but I'd like an all-time average, if possible. So far, I've just been using the time-picker to do so but I'm unsure how the timechart span plays into that.

Re: Can't get timechart average to work

to4kawa — Mon, 10 Aug 2020 09:50:46 GMT

>unexpected columns are returned

you wanted | timechart avg(timedifference_hours), so I make the query timechart span=1h avg

>all time average

try | eventstats avg()

Re: Can't get timechart average to work

benhooper — Mon, 10 Aug 2020 10:54:19 GMT

Sorry, I mostly use timecharts in the single value visualisation (including in this case) so I forgot that the timechart span is the unit on the x axis and not the search timeframe.

I tried " | eventstats avg()" and " | eventstats avg(timedifference_hours)" but both return "Your search isn't generating any statistic or visualization results".

Thanks.

Re: Can't get timechart average to work

to4kawa — Mon, 10 Aug 2020 11:39:33 GMT

>| stats avg(timedifference_hours) is works

you don't create table, so Your search isn't generating any statistic or visualization results

Re: Can't get timechart average to work

benhooper — Mon, 10 Aug 2020 11:57:02 GMT

I've just tried the following searches but both still return "No results found":

"<base command> | eventstats avg(timedifference_hours) as timedifference_hours_average | table timedifference_hours_average | timechart span=1d avg(timedifference_hours_average)"
"<base command> | stats avg(timedifference_hours) as timedifference_hours_average | table timedifference_hours_average | timechart span=1d avg(timedifference_hours_average)"

They work fine without " | timechart", though.

Thanks.

Re: Can't get timechart average to work

to4kawa — Mon, 10 Aug 2020 12:03:34 GMT

<base command> | eventstats avg(timedifference_hours) as timedifference_hours_average

Have you tried the query line by line?
eventstats avg() makes whole average. why do you do | timechart span=1d?
Nothing changes per day.

>"<base command> | stats avg( ...

timechart needs _time field. your query remove _time by stats

Re: Can't get timechart average to work

benhooper — Mon, 10 Aug 2020 12:28:33 GMT

Okay, I've managed to generate a timechart which does seem to be correct (tested by exporting and calculating average in Excel) using the following search:

<base command> | eventstats avg(timedifference_hours) as timedifference_hours_average | table _time, timedifference_hours_average | timechart avg(timedifference_hours_average)

However, there seems to be no real trend / history to it. For example:

All-time: "31 ↗ 31"
Month to date: "10 ↗ 10"
Last 7 days: "6 ↗ 6"

Using a custom trend comparison and a different time range doesn't make a difference because, for some reason, the timeline values are either 0 or the highest number.

Re: Can't get timechart average to work

to4kawa — Mon, 10 Aug 2020 12:46:40 GMT

https://docs.splunk.com/Documentation/Splunk/8.0.4/SearchReference/Timechart

I have no idea why you are using timechart, but since you are using eventstats, of course it won't change.

Re: Can't get timechart average to work

benhooper — Mon, 10 Aug 2020 13:04:11 GMT

Because I'd like to see whether the average time to close a case is improving or worsening but, since I'm new to all of this, I'll take suggestions for better ways to do so.

Re: Can't get timechart average to work

to4kawa — Mon, 10 Aug 2020 13:13:52 GMT

Visualization >> line chart

but I don't see the logs , it can't work, I guess.

Re: Can't get timechart average to work

benhooper — Mon, 10 Aug 2020 14:02:56 GMT

That does work and I've created it as a separate panel because it may be useful in the future but it's too complicated for what we want for now which is why we're trying to use the Single Value visualisation.

Thanks.

Re: Can't get timechart average to work

benhooper — Mon, 10 Aug 2020 15:01:10 GMT

It seems that using streamstats in place of eventstats has achieved what I want as the average updates with each event.