I'm looking for a way to filter search results based on calculating time deltas between 2 rows (goal is to extract contiguous events based on 0 or greater time delta from the end of one event to the start of the next). The problem is that I need to evaluate each row pair and filter in a single operation and then repeat for the entire data series. The only way I have been able to achieve this so far is to use searchstats, and filter out a row at time and rerun the streamstats until the result set no longer reduces. Is there a better way?
Example data set (in reverse chronological 'end time' order):
Event Start_Time End_Time Delta
Event_1 13:10:00 13:20:00 -
Event_2 13:07:00 13:15:00 -5:00
Event_3 13:06:00 13:14:00 -7:00
Event_4 13:00:00 13:10:00 -4:00
Event_5 12:50:00 13:00:00 0
Desired Output
Event Start_Time End_Time Delta
Event_1 13:10:00 13:20:00 -
Event_4 13:00:00 13:10:00 0
Event_5 12:50:00 13:00:00 0
Actual Output
Event Start_Time End_Time Delta
Event_1 13:10:00 13:20:00 -
Event_5 12:50:00 13:00:00 0
Original Query
<query> | streamstats current=f window=1 global=f last(Start_Time) as Next_Start | while (Next_Start - End_Time) >= 0
-- Next_Start is the 'Start_Time' from the first row, and End_Time is from the second row
Problem is that this filters out Event 4 because it evaluates the entire data set in one operation before evaluating the filter
Current Working (but inefficient) Query, and I don't really know how many times to call the filter (although strangely enough it doesn't seem to cause a significant time impact even with 30+ calls!)
<query> | streamstats current=f window=1 global=f last(Start_Time) as Next_Start | eval diff = Next_Start - End_Time | streamstats current=f window=1 global=f last(diff) as prev_diff | eval diff = if(diff<0 AND prev_diff<0,0,diff) | search diff >= 0
| streamstats current=f window=1 global=f last(Start_Time) as Next_Start | eval diff = Next_Start - End_Time | streamstats current=f window=1 global=f last(diff) as prev_diff | eval diff = if(diff<0 AND prev_diff<0,0,diff) | search diff >= 0
| streamstats current=f window=1 global=f last(Start_Time) as Next_Start | eval diff = Next_Start - End_Time | streamstats current=f window=1 global=f last(diff) as prev_diff | eval diff = if(diff<0 AND prev_diff<0,0,diff) | search diff >= 0
... <repeat until data set doesn't reduce any further>
-- Only consider the first two rows with a negative result. If there are contiguous rows with a negative result zero out all but the first and let the next searchstats call filter them one by one.
Any suggestions would be greatly appreciated. Thanks
... View more