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

[Roynsky]

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:

_time	Result
time1	YES
time2	NO
time3	NO
time4	YES

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.

[tscroggins]

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