Splunk Search

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

evallja
Path Finder

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.

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| 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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
| 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

Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...