How to edit my search to calculate the average time per each field value?

[pavanae]

I have a search as follows which displays the total number of students who accessing college website outside of college hours.

My Search | where date_hour>=16 OR date_hour<9 | convert ctime(_time) as Date_and_Time | stats dc(student_id) as Total

Now I'm trying to determine the range/average of out of college accessing times per each student from the above search result?

I'm trying to get the result as below

student_id Average_accessing_time_range

A1111 18:00 - 20:00
B3211 00:00 - 2:00

Will it be possible to get the result like above by using the Splunk?

[cmerriman · Accepted solution]

UPDATED AGAIN

My Search | where date_hour>=16 OR date_hour<9| convert ctime(_time) as Date_and_Time |eval StartHour=strftime(_time,"%H")|eval EndHour=strftime(_time,"%H")  |stats earliest(StartHour) as StartHour latest(EndHour) as EndHour by student_id 

try something like that

UPDATED

My Search | where date_hour>=16 OR date_hour<9 | convert ctime(_time) as Date_and_Time|convert timeformat="%m/%d/%Y %H:%M:%S" mktime(_time) as time  |eventstats range(time) as duration by student_id| stats dc(student_id) as TotalStudents avg(duration) as avgDurationPerStudent

this should get you (in seconds) the average duration each student was on the website.

[pavanae]

Thanks for the response but here _time is not just a value. It's a timestamp like (11/07/2016 04:00:00)

[cmerriman]

I've updated this to convert _time to epoch before the range.

[pavanae]

Thanks for your time. Sorry for the wrong question. I just updated my question. Please check it. All I'm looking is for average time accessed like in my question and not the regullar average.