Reporting
Highlighted

How do you merge events with unique entries?

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
Highlighted

Re: How do you merge events with unique entries?

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

Highlighted

Re: How do you merge events with unique entries?

This is perfect thank you!