Splunk Search

How to get total of unique senders and recipients from Index=msexchange

New Member

I think I was able to get the total number of unique senders and unique recipients. But, now I need the total of unique communicators (senders + recipients). Looking for formula to add the two numbers.

index=msexchange | stats dc(sender) as Distinct_Sender
index=msexchange | stats dc(recipients) as Distinct_Recipients

Thanks for any help!

0 Karma
1 Solution

Influencer

Have you tried using eval?

index=msexchange | stats dc(sender) as Distinct_Sender dc(recipients) as Distinct_Recipients | eval Total=Distinct_Sender + Distinct_Recipients

View solution in original post

0 Karma

Influencer

Have you tried using eval?

index=msexchange | stats dc(sender) as Distinct_Sender dc(recipients) as Distinct_Recipients | eval Total=Distinct_Sender + Distinct_Recipients

View solution in original post

0 Karma

New Member

The is what I was looking for....Thanks @jplumsdaine22 !

0 Karma

SplunkTrust
SplunkTrust

What about this?

index=msexchange
| fields _time, sender, recipients
| eval sender_recipient = sender. "<-->" . recipients
| stats dc(sender_recipient) as Distinct_Senders_Recipients

If your recipient field is a multivalued one then the following should work:

index=msexchange
| fields _time, sender, recipients
| mvexpand recipients
| eval sender_recipients = sender. "<-->" . recipients
| stats dc(sender_recipients) as Distinct_Senders_Recipients

The obviously a problem with this. It won't treat as one those conversations where the recipient and the sender are swapped.
If you want to achieve this the following example might help:

index=msexchange
| fields _time, sender, recipients
| mvexpand recipients
| eval conversation=mvjoin(mvsort(mvappend(sender,recipients)), " <--> ")
| stats count by conversation

More details here: https://answers.splunk.com/answers/331939/how-to-search-the-count-of-emails-sent-between-two.html

0 Karma

New Member

This is a search that meets one of the email requirements I'm tasked with even if I wasn't initially asking for it! The second option is what I'm using. Thank you for your input and for the reference link!

0 Karma

New Member

Hi @javiergn

It doesn't look like the mvexpand is separating the email addresses that are semi-colon delimited. So, it's counting the entire multi-value email value for recipients <--> sender pairs. Is there another way?

recipients="user1@co.com;user2@co.com;user3@co.com;user4@co.com;user5@co.com"

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!