Splunk Search

Can't get timechart average to work

benhooper
Communicator

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.

Labels (1)
0 Karma

to4kawa
SplunkTrust
SplunkTrust

 

index="<client>" case_id (status!=new AND status!=under_investigation) 
| eval creation_time_epoch = strptime(creation_time, "%Y/%m/%d %H:%M:%S") 
| eval timedifference = _time - creation_time_epoch
| timechart span=1h avg(timedifference) by case_id

 

0 Karma

benhooper
Communicator

Thanks for that but it seems to not work:

  1. All sorts of unexpected columns are returned ("1156", "1157", "1232", etc - times?).
  2. A span of 1 hour returns "0 → 0".
  3. 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.

0 Karma

to4kawa
SplunkTrust
SplunkTrust

>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() 

0 Karma

benhooper
Communicator

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.

0 Karma

to4kawa
SplunkTrust
SplunkTrust

>| stats avg(timedifference_hours) is works

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

0 Karma

benhooper
Communicator

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.

0 Karma

to4kawa
SplunkTrust
SplunkTrust

<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  

0 Karma

benhooper
Communicator

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.

0 Karma

to4kawa
SplunkTrust
SplunkTrust

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.

0 Karma

to4kawa
SplunkTrust
SplunkTrust
index="<client>" case_id (status!=new AND status!=under_investigation) 
| eval creation_time_epoch = strptime(creation_time, "%Y/%m/%d %H:%M:%S") 
| eval timedifference = _time - creation_time_epoch
| eval time=_time
| bin span=1d _time
| eventstats avg(timedifference) as day_avg by _time case_id
| bin span=1w _time
| eventstats avg(timedifference) as week_avg by _time case_id
| bin span=1month _time
| eventstats avg(timedifference) as month_avg by _time case_id
| eval _time=time
| table _time case_id day_avg week_avg month_avg
| sort _time
| xyseries _time case_id day_avg week_avg month_avg

Visualization >> line chart


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

benhooper
Communicator

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.

0 Karma

benhooper
Communicator

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

Tags (1)
0 Karma

benhooper
Communicator

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.

0 Karma