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.
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
Thanks for that but it seems to not work:
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.
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".
I've just tried the following searches but both still return "No results found":
They work fine without " | timechart", though.
<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
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:
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.
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.
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.