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)?
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
| 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
As long as "start_time" is an integer, the "strftime" and "case" statement will work.
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
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