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!
This is a bit hacky but it should work
...|eval conversation=mvjoin(mvsort(mvappend(mailto,mailfrom)), " <--> ") | stats count by conversation
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
This is not a very flexible solution..
But thank you anyway !
This is a bit hacky but it should work
...|eval conversation=mvjoin(mvsort(mvappend(mailto,mailfrom)), " <--> ") | stats count by conversation
The idea is good but I still get 2 differents lines for the same conversation:
conversation count
A <--> B 2
B <--> A 1
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.
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 !