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% |
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*
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
Both answers worked. @woodcock's appeared to run quicker so I've given him the accept. Thank you for your help!
Hey @JordanPeterson, if @woodcock's solution worked please remember to close the question and award karma points by accepting the answer. 🙂
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*