Duration between two events with start event re-occuring

[splunkswede]

I have the following types of events, all tied together with a unique id.

GetMember #6 contains unique ID XYZ
GetMember #5 contains unique ID XYZ
EndEvent contains unique ID XYZ
GetMember #4 contains unique ID XYZ
GetMember #3 contains unique ID XYZ
GetMember #2 contains unique ID XYZ
GetMember #1 contains unique ID XYZ

To measure the duration of the transaction I need to take EndEvent - GetMember #1.

If I use the transaction command I get the duration between GetMember #4 and EndEvent, which is not correct. The transaction command takes the last Event (before the End Event) by default. I have not figured out a way to pick the earliest event.

I have also tried to use stats:
search | eval EmailID=lower(EmailID) | stats earliest(_time) as E,latest(_time) as L by EmailID| eval duration=L-E | where duration > 0 | where duration < 200| eval Time=strftime(E, "%m/%d %H:%M") | chart avg(duration) by Time

But this command will return a duration even though the EndEvent has not yet happened. Maybe there is a way to only return values when the EndEvent has been found (more like how the Transaction command works)?

Any advice is appreciated.

[splunkswede]

Ok, with the answer from Somesoni2 (thanks!!!) I am now able to only get the result in case the EndEvent exists.

search | eval EmailID=lower(EmailID) | eval HasEnd=if(match(_raw,".i422AdminClubNotificationResponse."),1,0) | stats earliest(_time) as E,latest(_time) as L sum(HasEnd) as HasEnd by EmailID | where HasEnd>0 | eval duration=L-E | where duration > 0 AND duration < 200 | eval Time=strftime(E, "%m/%d %H:%M")

Now the problem is that, in case there is a new GetMember request AFTER the EndEvent then the GetMember event is used as the end to the duration. I always want the first GetMember request and the first EndEvent to be used to calculate the duration.

[andrey2007]

May be this help using stats with eval

| stats latest(eval(if(like(_raw,"%end_event_specifier%"), _time, NULL))) as endtime by emailID

[somesoni2]

Try like this

search | eval EmailID=lower(EmailID) | eval HasEnd=if(match(_raw,".*EndEvent.*),1,0) | stats earliest(_time) as E,latest(_time) as L sum(HasEnd) as HasEnd by EmailID | where HasEnd>0 | eval duration=L-E | where duration > 0 AND duration < 200| eval Time=strftime(E, "%m/%d %H:%M") | chart avg(duration) by Time