How to combine start_hour field and end_hour field and then calculate the average and standard deviation as mentioned below?

[pavanae]

I have my splunk search as below

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

which displays the result as below

student_id StartHour EndHour
X1234 00 19
Y6789 00 20
V5678 02 20
G4567 06 06

Now I'm trying to acheive something like below

student_id StartHour EndHour starthour_and_endhour_yesterday average(starthour_and_endhour)_for_last_7_days standard_deviation
X1234 00 19 00-18 00-03
Y6789 00 20 01-19 02-19
V5678 02 20 02-19 03-19
G4567 06 06 06-06 06-06

Where standard_deviation is if student_id accessing starthour_and_endhour is 3 times standard deviation of average

Now how can i calculate all the fields as mentioned above. Is it possible to acheive by splunk to find the suspecious behaviour of a student_id as mentioned on the above format?

[niketn · Accepted solution]

First off you would need to convert starthour_and_endhour_yesterday and average(starthour_and_endhour)_for_last_7_days to numeric field (either in hour or minutes)

For example 00-18 to be replaced with 18 (hrs), or something meaningful as per your use case. Then you can use stdev stats function on average numeric field multiplied by 3.