Reporting

How do you merge events with unique entries?

SplunkMasterSne
Explorer

Hi everyone,

I have a data set for incoming emails through our mail gateway. The problem is, it sends a log with the sender address (src_user) and another log with the recipient (recipient).

I want to know how many emails are being sent to an internal email address by the same sender.

I've had a look at the events and can see there is a matching string in the message field. example log below:

sender event

<141>Feb 15 10:22:05 mail.server.corp filter_instance1[27702]: rprt s=2qkyvtba71 m=1 x=2qkun2vf0b-1 mod=session cmd=data from=fake.user@domain.com
recipient log

<141>Feb 15 08:49:04 mail.server.corp filter_instance1[25779]: rprt s=2qkun2vf0b m=1 x=2qkun2vf0b-1 mod=session cmd=data rcpt=user@company.co.uk

So the only matching string is the x=2qkun2vf0b, which links the two emails together. If the same sender sends another mail to the same recipient, this obviously changes. So it's getting a bit difficult to come up with something!

What i really want is a query that will show how many emails a recipient email has received from the same sender. Is this possible with my current event log state?

Thanks

0 Karma
1 Solution

DMohn
Motivator

You could try using transaction here

<your base query> | transaction x | stats dc(rcpt) by from

This should merge the two corresponding events together, and then do a distinct count (dc) of recipients by sender.

View solution in original post

DMohn
Motivator

You could try using transaction here

<your base query> | transaction x | stats dc(rcpt) by from

This should merge the two corresponding events together, and then do a distinct count (dc) of recipients by sender.

SplunkMasterSne
Explorer

This is perfect thank you!

Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...