Splunk Search

Get percentage of eval case fields

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

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

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

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

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. 🙂

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