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

[tristamaltizo]

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!

[jplumsdaine22 · Accepted solution]

Have you tried using eval?

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

[tristamaltizo]

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

[javiergn]

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

[tristamaltizo]

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!

[tristamaltizo]

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"