How to calculate shift users from SSL VPN login sessions

[elxbee]

I am wanted to calculate shift Analysts VPN session start and end time duration to exactly capture the shift during 24 hours as I have 3 shifts with following timings

Morning Shift time = 7am  to 3pm

Evening Shift time = 3pm to 11pm 

night shift time duration = 11pm to 7am next morning

Currently I constructed following query that is having wrong data whenever i increase time more than 24 hours how i can put if condition in this query to add a column Shift time (morning ,evening ,night ) based on Start and end time if condition time range ?

index=it sourcetype=pulse:connectsecure vendor_product="Pulse Connect Secure" realm=Company-Domain+DUO1001 earliest=-24
| iplocation src
| eval Attempts= if(vendor_action="started","Session_Started","Session_Ended")
| stats values(Attempts) AS All_Attempts values(src) AS src count(eval(Attempts="Session_Started")) AS Started count(eval(Attempts="Session_Ended")) AS Ended min(_time) AS start_time max(_time) AS end_time by user
| eval Duration=end_time-start_time
| search user=Analyst1 OR user=Analyst2 OR user=Analyst3 OR user=Analyst4 OR user=Analyst5 OR user=Analyst6 OR user=Analyst7 OR user=Analyst8 OR user=Analyst9
| convert ctime(start_time)
| convert ctime(end_time)
| eval totall_duration=tostring(Duration,"duration")
| table user,All_Attempts,src,Started,Ended,start_time,end_time,totall_duration

In excel I am using following formula to calculate the shift duration from ticket close time

=IF(HOUR(E2)<7,"Night Shift",IF(HOUR(E2)<15,"Morning Shift",IF(HOUR(E2)<23,"Evening Shift","Night Shift"))) 

How I can insert similar condition in splunk to get the result intron of  a new calculated column called shift with Session started and session End (time  duration between both times)?

@manjunathmeti  @woodcock 

[woodcock]

Somethings like this:

| makeresults 
| eval user="Day",All_Attempts=10,src="src1",Started=now()-(24*60*60),Ended=now(),start_time=Started,end_time=Ended,totall_duration=end_time-start_time 
| table user,All_Attempts,src,Started,Ended,start_time,end_time,totall_duration
| eval HOUR_E2=strftime(start_time, "%H")
| eval shift=case(
HOUR_E2<7,"Night Shift",
HOUR_E2<15,"Morning Shift",
HOUR_E2<23,"Evening Shift",
true(),"Night Shift")
| fields - HOUR_E2

[elxbee]

| makeresults 
| eval user="Day",All_Attempts=10,src="src1",Started=now()-(24*60*60),Ended=now(),start_time=Started,end_time=Ended,totall_duration=end_time-start_time 

above code is rejected by splunk so i omitted it 

this is generating error i made the following changes and its showing all  last 24 results in night shifts 

below code is only generating night shift for all last 24 hours which is i am getting through another code as well

index=it sourcetype=pulse:connectsecure  vendor_product="Pulse Connect Secure" realm=Domain+DUO1001  earliest=-16h
| iplocation src
| eval Attempts= if(vendor_action="started","Session_Started","Session_Ended") 
| stats  values(Attempts) AS All_Attempts  values(src) AS src count(eval(Attempts="Session_Started")) AS Started count(eval(Attempts="Session_Ended")) AS Ended min(_time) AS start_time max(_time) AS end_time by user
| eval Duration=end_time-start_time
| search  user=username-l2 OR user=username-l1 OR user=username1 OR user=username2 OR user=username3 OR user=username4
 
| convert ctime(start_time)
| convert ctime(end_time)

| eval totall_duration=tostring(Duration,"duration")
| table user,All_Attempts,src,Started,Ended,start_time,end_time,totall_duration
| eval HOUR_E2=strftime(start_time, "%H")
| eval shift=case(
HOUR_E2<7,"Night Shift",
HOUR_E2<15,"Morning Shift",
HOUR_E2<23,"Evening Shift",
true(),"Night Shift")
| fields - HOUR_E2

Second code that I tried give me the same night shift result only

index=it sourcetype=pulse:connectsecure  vendor_product="Pulse Connect Secure" realm=Domain+DUO1001  earliest=-24h
| iplocation src
| eval Attempts= if(vendor_action="started","Session_Started","Session_Ended") 
| stats  values(Attempts) AS All_Attempts  values(src) AS src count(eval(Attempts="Session_Started")) AS Started count(eval(Attempts="Session_Ended")) AS Ended min(_time) AS start_time max(_time) AS end_time by user
| eval Duration=end_time-start_time
| search user=username-l2 OR user=username-l1 OR user=username1 OR user=username2 OR user=username3 OR user=username4
| convert ctime(start_time)
| convert ctime(end_time)|eval iHour=strftime(strptime(TIMESTAMP,"%Y-%m-%d %H:%M:%S"),"%H")
|eval iDay=strftime(strptime(TIMESTAMP,"%Y-%m-%d %H:%M:%S"),"%Y-%m-%d")
|eval iDay=round(strptime(iDay,"%Y-%m-%d"),0)
|eval shiftstart=if(start_time >= 7 AND start_time < 15,"Morning",if(start_time >= 15 AND start_time < 23,"Evening ","Night")) 
|eval shiftend=if(end_time >= 7 AND end_time < 15,"Morning",if(end_time >= 15 AND end_time < 23,"Evening ","Night")) | eval total_duration=tostring(Duration,"duration")
| table user,shiftstart,shiftend,All_Attempts,src,Started,Ended,start_time,end_time,total_duration

[woodcock]

As long as "start_time" is an integer, the "strftime" and "case" statement will work.

[elxbee]

Hi @woodcock  still with all my tries the result is just showing night in shift column

can you please test or check is there any logic wrongly defined in my query for attendance as I copy it from siem admin dashboard and just wanted to add correct shift in that query 

[elxbee]

@woodcock 

Thank you so much for your kind reply 

I am trying to run this on Pulse Secure integrated log which has two columns only session started and ended

is it possible if you can append the code which is generating the attendance with added shift calculation time range (session started and session ended ) a full query ?

looking forward