I have a set of data where I run this query:
base search| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(time*)| eval total_time=((time_complete-time_initial)/60)| timechart avg(total_time) AS "Average Time to Complete" by Module
where I would like to group the values of field total_time in groups of 0-2 / 3-5 / 6-10 / 11-20 / > 20 and show the count in a timechart.
Please help.
i think your best bet is to use an eval:
base search| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(time*)| eval total_time=((time_complete-time_initial)/60)| timechart avg(total_time) AS total_time_avg by Module|eval "Average Time to Complete"=case(total_time_avg<=120,"0-2",total_time_avg<=300,"3-5",total_time_avg<=600,"6-10",total_time_avg<=1200,"11-20",1=1,">20")|fields - total_time_avg
just understand that 3-5 is anything over 2 minutes up through 5 minutes, 6-10 is anything over 5 minutes up through 10 minutes, etc. though it can be adjusted accordingly.
It does not solve. This is how it looks:
2017-08-27 >20 1.3433333333333333
2017-08-28 >20 0.9136054421768706
2017-08-29 >20 1.1056265984654732
2017-08-30 >20 1.1308823529411764
Every value out there is ">20"
sorry that was an oversight on my part. with using timechart, total_time_avg wouldn't be a column header, your Module values would be.
try this:
base search| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(time*)| eval total_time=((time_complete-time_initial)/60)| timechart avg(total_time) AS total_time_avg by Module|foreach * [eval <<FIELD>>=case('<<FIELD>>'<=120,"0-2",'<<FIELD>>'<=300,"3-5",'<<FIELD>>'<=600,"6-10",'<<FIELD>>'<=1200,"11-20",1=1,">20")]
Sorry from my end too but there was a gap in description of the problem. I want to know the count of values that landed in these groups in a time frame. So if there's a trendline visualization, there should be 5 trendlines for each of these groups showing how many of these time averages landed in each group in that time frame.
alright, so that makes it more complicated. maybe something like this:
base search| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(time*) |bucket _time span=1d| eval total_time=((time_complete-time_initial)/60)|eventstats avg(total_time) as total_time_avg by _time Module|eval total_time_avg=case(total_time_avg<=120,"0-2",total_time_avg<=300,"3-5",total_time_avg<=600,"6-10",total_time_avg<=1200,"11-20",1=1,">20")| stats count by _time Module total_time_avg|eval {Module}=count|fields - Module count|stats values(*) as * by _time total_time_avg
Thank you for your help. This solved my problem:
base search|convert timeformat="%Y-%m-%d %H:%M:%S" mktime(time*)|eval total_time=((time_complete-time_initial))|rename total_time as total_time_avg|eval "Average Time to Complete"=case(total_time_avg<=120,"0-2",total_time_avg<=300,"3-5",total_time_avg<=600,"6-10",total_time_avg<=1200,"11-20",1=1,">20")|fields - total_time_avg| timechart count by "Average Time to Complete"
I would convert this to an answer but I don't have the permissions.
Someone please convert this comment to an answer!!!