How to get elapsed time between two events

[politrons]

I'm trying to calculate the milliseconds between two events by same transactionId, and then show in a timechart

Here my current query

 "My event 1" | stats latest(_time) as time_login by transactionId |join transactionId [search "My event 2" | stats latest(_time) as time_finish by transactionId] | eval difference=time_finish-time_login 

This query works really slow and half of the time it does not work, but if I try to add this to the end of the query

| timechart avg(difference) 

[gcusello]

Hi @politrons,

did you tried to do all the calculation in one stats?

something like this:

 "My event 1" OR "My event 2" 
| stats 
   earliest(eval(if(searchmatch("My event 1"),_time,"")) as time_login 
   latest((eval(if(searchmatch("My event 2"),_time,"")) as time_finish 
   BY transactionId
| eval difference=time_finish-time_login 

the only problem (present also in your solution) is if one of the time_login or time_finish is outside the search time period.

Remember that Splunk isn't a DB, so join command (that all the people coming from SQL used to use) is a command to use only when there isn't any other solution!

Ciao.

Giuseppe