How to edit my eval statements to find the difference between Start and Finish times and convert to a human readable format?

[thoban]

I'm looking to show the duration of logons through VDI logs. I convert _time into something better for the Start and Finish Times, but I'm unable to evaluate the difference.

I have tried to convert the Splunk duration fields back to a "human" readable format, but I have not been successful.

EVAL Start_Time=strftime(if(EventType="AGENT_CONNECTED",_time,null()), "%H:%M:%S") | EVAL Finish_Time=strftime(if(EventType="AGENT_ENDED",_time,null()), "%H:%M:%S") | transaction UserDisplayName MachineName startswith="AGENT_CONNECTED" endswith="AGENT_ENDED" | EVAL Duration=Finish_Time-Start_Time | Table UserDisplayName MachineName Start_Time Finish_Time Duration

[richgalloway · Accepted solution]

Start_Time and Finish_Time are strings so you can't do math with them. To get the different between two timestamps, always use epoch form (what's passed in to strftime()). Something like this:

EVAL startTime=if(EventType="AGENT_CONNECTED",_time,null())| eval Start_Time=strftime(startTime, "%H:%M:%S") | EVAL finishTime=strftime(if(EventType="AGENT_ENDED",_time,null()), | eval Finish_Time=strftime(finishTime,"%H:%M:%S") | transaction UserDisplayName MachineName startswith="AGENT_CONNECTED" endswith="AGENT_ENDED" | EVAL Duration=finishTime-startTime | Table UserDisplayName MachineName Start_Time Finish_Time Duration

[thoban]

I tried your suggestions with a change or two:

EVAL StartTime=if(EventType="AGENT_CONNECTED",_time,null()) | EVAL Start_Time=strftime(StartTime, "%H:%M:%S") | EVAL FinishTime=if(EventType="AGENT_ENDED",_time,null()) | EVAL Finish_Time=strftime(FinishTime, "%H:%M:%S") | transaction Start_Time Finish_TIME UserDisplayName MachineName startswith="AGENT_CONNECTED" OR "AGENT_RECONNECTED" endswith="AGENT_ENDED" | EVAL Duration=FinishTime-StartTime | Table UserDisplayName MachineName Start_Time Finish_Time Duration

I guess the duration is in seconds, but not truly usable.

Here is the output for example:

Start_Time Finish_Time Duration
13:55:40 14:09:12 812

11:20:49 13:48:58 8889

11:52:35 16:01:53 14958

11:40:48 15:17:07 12979

09:59:02 15:45:36 20794

09:42:48 14:58:08 18920

16:16:57 16:37:31 1234

11:58:54 13:45:29 6395

11:28:13 13:54:58 8805

09:10:04 15:15:16 21912

I then added this to see:

|  EVAL duration=FinishTime-StartTime | EVAL Duration=strftime(duration, "%H:%M:%S") |

The below durations seem to have correct minutes and seconds, but not hours.

Start_Time Finish_Time Duration
13:55:40 14:09:12 19:13:32

11:20:49 13:48:58 21:28:09

11:52:35 16:01:53 23:09:18

11:40:48 15:17:07 22:36:19

09:59:02 15:45:36 00:46:34

09:42:48 14:58:08 00:15:20

16:16:57 16:37:31 19:20:34

11:58:54 13:45:29 20:46:35

11:28:13 13:54:58 21:26:45

09:10:04 15:15:16 01:05:12

[somesoni2]

Use this as your last eval

| eval Duration=tostring(duration,"duration")

[thoban]

Exactly what was needed!
Thank you!