How to find in between duration between three transaction event?

[limalbert]

Hi,

How can I find in between duration between three transaction event? For example, the duration1 between mod1 and mod2, and duration2 between mod2 and mod3. My current query is taking a while because I'm appending two searches. how can I improve it
Ex:

user type time
user1 mod1 10:00
user1 mod2 11:00
user1 mod3 13:00

Expected result

user      durationMod1Mod2  durationMod2Mod3
user1          1 hour           2 hours

Current code:

base search ...

| transaction user startswith=eval(status="mod1") endswith=eval(status="mod2")  
| rename duration as duration1
| append [base search ...
  | transaction user startswith=eval(status="mod2") endswith=eval(status="mod3")
   | rename duration as duration2 ]
| stats values(duration1), values(duration1)  by user

[to4kawa · Accepted solution]

base search ...
| stats range(eval(if(status="mod1" OR status="mod2" OR status="mod3", _time, NULL))) as duration by user

[limalbert]

Thank you! This works for me.

base search ...
| stats range(eval(if(status="mod1" OR status="mod2" OR status="mod3", _time, NULL))) as duration1, range(eval(if(status="mod2" OR status="mod3", _time, NULL))) as duration2 by user

[arjunpkishore5]

Can you post your expected output? I also don't see the field duration in your data, nor do i see it being calculated here.

[limalbert]

Expected result is added. Transaction command actually created duration.