How to merge using time range and some duplicated field values?

[evallja]

Hello,

I have a table with the following fields from an email security system that are duplicated within a time range of 3s:

_time	sender	receiver	subject	attach
2023-08-07 14:07:46	sender1@domain.com	receiver1@domain.com receiver2@domain.com	"email subject"	attach1.pdf attach2.pdf
2023-08-07 14:07:49	sender1@domain.com	receiver1@domain.com receiver2@domain.com
2023-08-07 15:10:05	sender2@domain.com	receiver3@domain.com receiver4@domain.com
2023-08-07 15:10:08	sender2@domain.com	receiver3@domain.com receiver4@domain.com	"email2 subject"	attach3.rar attach4.rar

2023-08-07 16:11:08

sender3@domain.com

receiver5@domain.com

I want to merge the duplicated fields together within the range of 3s without losing the subject and attach value, but I don't want to remove other blank values of the emails that are sent without a subject or attach. It should look like this:

_time	sender	receiver	subject	attach
2023-08-07 14:07:46	sender1@domain.com	receiver1@domain.com receiver2@domain.com	"email subject"	attach1.pdf attach2.pdf
2023-08-07 15:10:08	sender2@domain.com	receiver3@domain.com receiver4@domain.com	"email2 subject"	attach3.rar attach4.rar

2023-08-07 16:11:08

sender3@domain.com

receiver5@domain.com

Thank you.

[ITWhisperer · Accepted solution]

| streamstats window=2 range(_time) as gap global=f by sender receiver
| eval _time=if(gap <= 3, _time-gap, _time)
| stats values(subject) as subject values(attach) as attach values(receiver) as receiver by _time sender

This assumes the events are in chronological order and will use the time from the earlier of the pair of events