Group time duration by minute

[pranaynanda]

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.

[cmerriman]

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.

[pranaynanda]

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"

[cmerriman]

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")]

[pranaynanda]

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.

[cmerriman]

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

[pranaynanda]

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.

[pranaynanda]

Someone please convert this comment to an answer!!!