topic Re: Compute time difference between events in Splunk Search

Compute time difference between events

pranaynanda — Tue, 16 May 2017 07:44:16 GMT

I have events like

Event EndDateTime
Launch 2017-05-16 13:00:00
.
.
.
Open 2017-05-16 13:00:30

I want to subtract time between these two events.

I want to implement something like

index="myindex" sourcetype="mysourcetype"  | transaction host startswith="Launch" endswith="Open"|convert timeformat="%Y-%m-%d %H:%M:%S" mktime(EndDateTime)| eval difference=[subtract EndDateTime where Event=Open - EndDateTime where Event=Launch| chart  avg(difference)

I just can't understand how can I work with the eval part about calculating difference.

Re: Compute time difference between events

somesoni2 — Tue, 16 May 2017 18:20:56 GMT

The transaction command should already be giving you duration field, and it would be correct as long as your _time field was extracted based of values of EndDateTime field (both _time and EndDateTime values are same).

Re: Compute time difference between events

adonio — Tue, 16 May 2017 18:21:51 GMT

looks like you are looking for the duration between events
the "duration" field is extracted with the transaction command
you can just | table duration after your transaction command and you can see the "difference in time"
hope i understand your question correctly

Re: Compute time difference between events

pranaynanda — Wed, 17 May 2017 06:39:40 GMT

That's the thing. They are not.

Re: Compute time difference between events

pranaynanda — Wed, 17 May 2017 06:40:22 GMT

The _time and EndDateTime values are not same. duration won't work in that case. Also, I did try what you're telling before posting this question and there were differences in answers which makes sense as _time did not add the time taken by last event in duration.

Re: Compute time difference between events

dineshraj9 — Wed, 17 May 2017 07:50:24 GMT

You can try creating a TRANID manually and get the difference -

index="myindex" sourcetype="mysourcetype" "Launch" OR "Open" | eval TRANID=if(like(EVENT,"%Launch%"),1,0) | streamstats sum(TRANID) as TRANID | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(EndDateTime) | eval start_time=if(like(EVENT,"%Launch%"),EndDateTime,0)  | eval end_time=if(like(EVENT,"%Open%"),EndDateTime,0) | stats sum(start_time) as start_time,sum(end_time) as end_time by TRANID | eval diff=end_time=start_time

Re: Compute time difference between events

pranaynanda — Thu, 18 May 2017 10:07:18 GMT

Did not work! 😕

It says: Error in 'eval' command: Fields cannot be assigned a boolean result. Instead, try if([bool expr], [expr], [expr]).

Re: Compute time difference between events

dineshraj9 — Thu, 18 May 2017 10:23:20 GMT

My bad, in the last eval mistakenly gave "=" instead of "-"

 index="myindex" sourcetype="mysourcetype" "Launch" OR "Open" | eval TRANID=if(like(EVENT,"%Launch%"),1,0) | streamstats sum(TRANID) as TRANID | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(EndDateTime) | eval start_time=if(like(EVENT,"%Launch%"),EndDateTime,0)  | eval end_time=if(like(EVENT,"%Open%"),EndDateTime,0) | stats list(EVENT) as EVENTS,sum(start_time) as start_time,sum(end_time) as end_time by TRANID | eval diff=end_time-start_time

Re: Compute time difference between events

pranaynanda — Tue, 29 Sep 2020 14:07:59 GMT

It only returns all values TRANID, start_time, end_time and diff as '0'

Re: Compute time difference between events

dineshraj9 — Thu, 18 May 2017 10:42:36 GMT

Can you provide few sample data for a single transaction?

Re: Compute time difference between events

pranaynanda — Thu, 18 May 2017 11:06:19 GMT

Test Name Start Date Start Time Time Interval Stop Date/Time Start Time(ms) Stop Time(ms) Run Time(min) Run Time(sec) Run Time(ms)

Launch 5/18/2017 14:00:37 14 5/18/2017 14:01 2.98235E+11 2.98331E+11 0.51 30.87 30865.67
Login 5/18/2017 14:01:34 14 5/18/2017 14:03 2.98401E+11 2.98741E+11 1.74 104.41 104412.77
Search 5/18/2017 14:04:08 14 5/18/2017 14:04 2.98854E+11 2.98884E+11 0.17 10.24 10244.62
CheckOut 5/18/2017 14:04:48 14 5/18/2017 14:04 2.98969E+11 2.98977E+11 0.05 2.71 2713.89
Expand 5/18/2017 14:06:47 14 5/18/2017 14:07 2.99316E+11 2.99476E+11 0.92 54.97 54971.75
LaunchApp 5/18/2017 14:08:36 14 5/18/2017 14:20 2.99636E+11 3.01742E+11 12.01 720.87 720872.82
Open 5/18/2017 14:21:32 14 5/18/2017 14:31 3.01903E+11 3.03665E+11 10.05 603.24 603235.27

The Stop Date/Time column is extracted in Splunk as EndDateTime.

Re: Compute time difference between events

dineshraj9 — Thu, 18 May 2017 12:03:03 GMT

I tested with your data this way and it is working for me this way -

  basesearch  | rename "Stop Date/Time" as EndDateTime,"Test Name" as EVENT | eval EndDateTime=round(strptime(EndDateTime,"%m/%d/%Y %H:%M")) | eval TRANID=if(like(EVENT,"Launch"),1,0) | streamstats sum(TRANID) as TRANID | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(EndDateTime) | eval start_time=if(like(EVENT,"Launch"),EndDateTime,0)  | eval end_time=if(like(EVENT,"Open"),EndDateTime,0) | stats list(EVENT) as EVENTS,sum(start_time) as start_time,sum(end_time) as end_time by TRANID | eval diff=end_time-start_time

Re: Compute time difference between events

pranaynanda — Thu, 18 May 2017 13:18:27 GMT

Thanks for your support. I just relaized that when I have the RunTime in seconds for each event, I can also get a sum of all of them by each Transaction. The thing is that sum(TimeSec) add all of them where I want it by transaction.

Re: Compute time difference between events

pranaynanda — Thu, 18 May 2017 13:50:43 GMT

O I basically did this and got what I wanted:

transaction host startswith="Launch" endswith="Open" | streamstats sum(TimeSec) as dur window=1 |

Thank you for your support. I had been such a stupid to not observe this earlier.

Re: Compute time difference between events

pranaynanda — Thu, 18 May 2017 13:50:43 GMT

So I basically did this and got what I wanted:

transaction host startswith="Launch" endswith="Open" | streamstats sum(TimeSec) as dur window=1 |

Thank you for your support. I had been such a stupid to not observe this earlier.

Re: Compute time difference between events

DalJeanis — Thu, 18 May 2017 14:34:38 GMT

@pranaynanda - I've converted your comment to an answer. Thanks for posting it.

Please accept your answer so the question will show as closed.