Splunk Search

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

pavanae
Builder

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?

0 Karma
1 Solution

cmerriman
Super Champion

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.

View solution in original post

0 Karma

cmerriman
Super Champion

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.

0 Karma

pavanae
Builder

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

0 Karma

cmerriman
Super Champion

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

0 Karma

pavanae
Builder

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.

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...