Splunk Search

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

ctaf
Contributor

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!

Tags (2)
0 Karma
1 Solution

jplumsdaine22
Influencer

This is a bit hacky but it should work

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

View solution in original post

stephanefotso
Motivator

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

SGF
0 Karma

ctaf
Contributor

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

0 Karma

jplumsdaine22
Influencer

This is a bit hacky but it should work

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

ctaf
Contributor

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

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

0 Karma

javiergn
SplunkTrust
SplunkTrust

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.

0 Karma

ctaf
Contributor

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 !

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...