Splunk Search

Duration between two events with start event re-occuring

splunkswede
Explorer

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
Explorer

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.

0 Karma

andrey2007
Contributor

May be this help using stats with eval

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...