topic How to search the count of emails sent between two addresses using mailto and mailfrom fields? in Splunk Search

How to search the count of emails sent between two addresses using mailto and mailfrom fields?

ctaf — Mon, 07 Dec 2015 11:11:59 GMT

Hello,

I would like to count the number of emails by couples.
For example:
A sent 2 emails to B
B sent 1 email to A
B sent 1 email to C

I would like to get a table with:

A <--> B : 3
B <--> C : 1

I have the fields: mailto and mailfrom.
If I do:

... | stats count by mailto,mailfrom

I get:
A --> B : 2
B --> A : 1
B --> C : 1

How can I avoid this ?

Thanks!

Re: How to search the count of emails sent between two addresses using mailto and mailfrom fields?

jplumsdaine22 — Mon, 07 Dec 2015 12:56:01 GMT

This is a bit hacky but it should work

...|eval conversation=mvjoin(mvsort(mvappend(mailto,mailfrom)), " <--> ") | stats count by conversation

Re: How to search the count of emails sent between two addresses using mailto and mailfrom fields?

ctaf — Mon, 07 Dec 2015 13:49:17 GMT

The idea is good but I still get 2 differents lines for the same conversation:

conversation count
A <--> B 2
B <--> A 1

Re: How to search the count of emails sent between two addresses using mailto and mailfrom fields?

javiergn — Mon, 07 Dec 2015 13:56:01 GMT

Hi,

Just tested @jplumsdaine22 query on my ES Cloud instance and it works fine. I'll copy and paste the whole thing:

index=* sourcetype="stream:smtp" earliest=-1d
| eval conversation=mvjoin(mvsort(mvappend(sender,receiver)), " <--> ")
| stats count by conversation

Keep in mind you might need to split your mailto field first if it contains multiple comma-separated values, but other than that the query works fine.

Re: How to search the count of emails sent between two addresses using mailto and mailfrom fields?

stephanefotso — Mon, 07 Dec 2015 13:57:34 GMT

Hello. Try something like this:

base search|eval couple=if((mailfrom="A" AND mailto="B") OR (mailfrom="B" AND mailto="A"),"AB",if((mailfrom="B" AND mailto="C") OR (mailfrom="C" AND mailto="B"),"BC",if((mailfrom="A" AND mailto="C") OR (mailfrom="C" AND mailto="C"),"AC",0)))|stats count  by couple

Thanks

Re: How to search the count of emails sent between two addresses using mailto and mailfrom fields?

ctaf — Mon, 07 Dec 2015 14:07:36 GMT

Oh my bad, it was a case sensitive problem. I just added

| eval mailfrom=lower(mailfrom) | eval mailto=lower(mailto)

And it works fine. Thank you guys !

Re: How to search the count of emails sent between two addresses using mailto and mailfrom fields?

ctaf — Mon, 07 Dec 2015 14:08:31 GMT

This is not a very flexible solution..
But thank you anyway !