Splunk Search

Calculating duration of several events in a row with the same field value

Roynsky
Engager

I'm having some trouble coming up with the SPL for the following situation:

I have some series of events with a timestamp. These events have a field extracted with a value of either "YES" or "NO". When sorted by _time we end up with a list like the following:

_timeResult
time1YES
time2NO
time3NO
time4YES

 

I'd like to count the duration between the "NO" values and the next "YES" value. So in this case we'd have a duration equal to time4 - time2. 

 

index=* sourcetype=*mantec* "Computer name" = raspberry_pi06 "Risk name" = WS.Reputation.1
| sort _time
| eval removed = if('Actual action' == "Quarantined", "YES", "NO")
| streamstats reset_before="("removed==\"YES\"")" last(_time) as lastTime first(_time) as firstTime count BY removed
| eval duration = round((lastTime - firstTime)/60,0)
| table removed duration count _time

 

 

I've tried to lean on streamstats but the result is resetting the count at the last "NO" and doesn't count the time of the next "YES". We end up with a duration equal to time3 - time2. Also in the case of a single "NO" followed by a "YES" we get a duration of 0 which is also incorrect.

I feel like I'm missing something extremely obvious.

Labels (2)

tscroggins
Influencer

Hi @Roynsky,

With your sample data represented by the following events:

2023-11-10 17:00:10 Result=YES
2023-11-10 17:00:07 Result=NO
2023-11-10 17:00:05 Result=NO
2023-11-10 17:00:00 Result=YES

and sorted by _time descending (the default event sort order), here are two options:

1.

| streamstats reset_before="("Result==\"YES\"")" max(_time) as end_time
| eval duration=end_time-_time
| stats max(duration) as duration by end_time

=>

end_time,duration
1699635600,0
1699635610,5

The delta between 17:00:05 and 17:00:10 is 5 seconds ending at 17:00:10.

2.

source="Roynsky_time_delta.txt" host="splunk" sourcetype="roynsky_time_delta"
| transaction endswith=eval(Result=="YES")
``` or | transaction endswith=Result=YES for an exact term match ```
| table _time duration

_time,duration
1699635605,5
1699635600,0

The delta between 17:00:05 and 17:00:10 is 5 seconds starting at 17:00:05.

I don't have Symantec Endpoint Protection sample data available, but if actions have correlation identifiers associated with each sequence of quarantine events, you might also use stats:

| stats range(_time) as duration by correlation_id ``` or whatever the field is called ```

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...