Splunk Search

Compute time difference between events

pranaynanda
Path Finder

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.

Tags (1)
1 Solution

pranaynanda
Path Finder

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.

View solution in original post

0 Karma

pranaynanda
Path Finder

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

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

0 Karma

pranaynanda
Path Finder

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.

0 Karma

dineshraj9
Builder

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 
0 Karma

pranaynanda
Path Finder

Did not work! 😕

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

0 Karma

dineshraj9
Builder

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 
0 Karma

pranaynanda
Path Finder

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

0 Karma

dineshraj9
Builder

Can you provide few sample data for a single transaction?

0 Karma

pranaynanda
Path Finder

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.

0 Karma

dineshraj9
Builder

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 
0 Karma

pranaynanda
Path Finder

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.

0 Karma

adonio
Ultra Champion

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

0 Karma

pranaynanda
Path Finder

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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).

0 Karma

pranaynanda
Path Finder

That's the thing. They are not.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...