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

[ctaf]

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!

[stephanefotso]

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

[ctaf]

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

[jplumsdaine22 · Accepted solution]

This is a bit hacky but it should work

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

[ctaf]

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

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

[javiergn]

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.

[ctaf]

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 !