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?
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.
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.
Thanks for the response but here _time is not just a value. It's a timestamp like (11/07/2016 04:00:00)
I've updated this to convert _time to epoch before the range.
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.