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!

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