Splunk Search

Get percentage of eval case fields

JordanPeterson
Path Finder

I'm looking at a specific email recipient. I want to see the percentage of emails they receive from specific senders. I think my current query gets all the fields I need but I'm having trouble breaking the results down to stats by month. Here is my current query:

index=msexchange (recipients="user@domain.org") eventtype="smtp-mail"
| eval sender_username=lower(sender_username)  
| eval valid_sender=case(
     sender_username=="mailer-daemon" OR sender_username=="postmaster","Bounceback",
     sender_username!="mailer-daemon" OR sender_username!="postmaster","Valid") 
| eval Month=strftime(_time,"%b")

Now what I would like to do is get a total count of emails sent to the recipient each month and another column that states the percentage of those emails per month where valid_sender="Bounceback"

The end results would hopefully look something like this:

| Recipient       | Month | Count | Bounceback% |
| user@domain.org | May   | 500   | 25%         |
| user@domain.org | June  | 1000  | 30%         |
| user@domain.org | July  | 750   | 20%         |
0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

index=msexchange (recipients="user@domain.org") eventtype="smtp-mail"
| eval sender_username=lower(sender_username)  
| eval type= if(sender_username=="mailer-daemon" OR sender_username=="postmaster"), "Bounceback", "Valid") 
| bucket _time span=1mon
| top type BY Recipient _time
| eventstats sum(count) AS count BY Recipient _time
| search type="Bounceback"
| rename percent AS "Bounceback %"
| eval Month = strftime(_time, "%B")
| table Recipient Month count Bounceback*

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Give this a try as well

index=msexchange (recipients="user@domain.org") eventtype="smtp-mail"
 | eval sender_username=lower(sender_username) 
| eval Bounceback= if(sender_username=="mailer-daemon" OR sender_username=="postmaster"), 1,0)
| eval Month = strftime(_time, "%B")
| stats count as Count sum(Bounceback) as Bouceback by Recipient Month
| eval "Bouceback%"=round(Bounceback*100/Count) | fields - Bouceback

JordanPeterson
Path Finder

Both answers worked. @woodcock's appeared to run quicker so I've given him the accept. Thank you for your help!

0 Karma

lfedak_splunk
Splunk Employee
Splunk Employee

Hey @JordanPeterson, if @woodcock's solution worked please remember to close the question and award karma points by accepting the answer. 🙂

woodcock
Esteemed Legend

Like this:

index=msexchange (recipients="user@domain.org") eventtype="smtp-mail"
| eval sender_username=lower(sender_username)  
| eval type= if(sender_username=="mailer-daemon" OR sender_username=="postmaster"), "Bounceback", "Valid") 
| bucket _time span=1mon
| top type BY Recipient _time
| eventstats sum(count) AS count BY Recipient _time
| search type="Bounceback"
| rename percent AS "Bounceback %"
| eval Month = strftime(_time, "%B")
| table Recipient Month count Bounceback*
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 ...