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!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...