Splunk Search

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

tristamaltizo
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

jplumsdaine22
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

jplumsdaine22
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
0 Karma

tristamaltizo
New Member

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

0 Karma

javiergn
Super Champion

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

tristamaltizo
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

tristamaltizo
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
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...