Splunk Search
Highlighted

How to write a search to get the total count of emails sent out and the count for each individual mail ID?

Explorer

I have a search which gives the total count of emails sent out from 5 different mail ids. I use a scheduled report for this and the report has the output - Count and last email sent. Now my requirement is along with the total count, I also need the count of emails sent from individual mail id's in my report. So the report looks something like,
total count, last email sent

mail sent from email1@x.com=10
mail sent from email2@y.com=15
mail sent from email3@z.com=20 and so on...
can someone tell me how to do this.
Thank you..

Tags (4)
0 Karma
Highlighted

Re: How to write a search to get the total count of emails sent out and the count for each individual mail ID?

Motivator

Lets get your first query, giving the total count of emails sent out from 5 different mail ids, please.

0 Karma
Highlighted

Re: How to write a search to get the total count of emails sent out and the count for each individual mail ID?

Explorer

index=xyz "email1@xxx.com" OR "email2@yyy.com" OR "email3@zzz.com" OR "email4@abc.com" OR "email5@xyz.com" "ready" "MID"|stats count, last(_time) as "EmailSent" | convert ctime("EmailSent") as time | table count, time

Looks something like this..

0 Karma
Highlighted

Re: How to write a search to get the total count of emails sent out and the count for each individual mail ID?

Motivator

In function of the query you gave in your coment, Try this,

index=xyz "email1@xxx.com" OR "email2@yyy.com" OR "email3@zzz.com" OR "email4@abc.com" OR "email5@xyz.com" "ready" "MID"|eval email=case(searchmatch("email1@xxx.com"), "email1@xxx.com",searchmatch("email2@xxx.com"), "email2@xxx.com",searchmatch("email3@xxx.com"), "email3@xxx.com",searchmatch("email4@xxx.com"), "email4@xxx.com",searchmatch("email5@xxx.com"), "email5@xxx.com")|stats count as totalcount, last(_time) as EmailSent by email | convert ctime("EmailSent") as time   | table email,totalcount, time 
0 Karma
Highlighted

Re: How to write a search to get the total count of emails sent out and the count for each individual mail ID?

Explorer

Hi Stephan,

I extracted a field for address and your query worked. But this time, it is only giving the individual counts of each email id but not getting the cumulative count(adding the all the email sent out from these mail ids) and last email sent time.

0 Karma
Highlighted

Re: How to write a search to get the total count of emails sent out and the count for each individual mail ID?

Motivator

Here you go:

 index=xyz "email1@xxx.com" OR "email2@yyy.com" OR "email3@zzz.com" OR "email4@abc.com" OR "email5@xyz.com" "ready" "MID"|eval email=case(searchmatch("email1@xxx.com"), "email1@xxx.com",searchmatch("email2@xxx.com"), "email2@xxx.com",searchmatch("email3@xxx.com"), "email3@xxx.com",searchmatch("email4@xxx.com"), "email4@xxx.com",searchmatch("email5@xxx.com"), "email5@xxx.com")|eventstats count as Totalemail|stats count as totalcount, last(_time) as EmailSent by email | convert ctime("EmailSent") as time   | table Totalemail email,totalcount, time 

But here the Totalemail value will be the same in each row

0 Karma
Highlighted

Re: How to write a search to get the total count of emails sent out and the count for each individual mail ID?

Motivator

try like :

index=xyz "email1@xxx.com" OR "email2@yyy.com" OR "email3@zzz.com" OR "email4@abc.com" OR "email5@xyz.com" "ready" "MID"|stats count as "total count",count(eval("email1@xxx.com")) as "count of email1@xxx.com",count(eval("email1@yyy.com")) as "count of email1@yyy.com",count(eval("email1@xyz.com")) as "count of email1@xyz.com",count(eval("email1@abc.com")) as "count of email1@abc.com", last(_time) as "EmailSent" | convert ctime("EmailSent") as time | table *

or

index=xyz "email1@xxx.com" OR "email2@yyy.com" OR "email3@zzz.com" OR "email4@abc.com" OR "email5@xyz.com" "ready" "MID"|stats count as "total count",count(eval(mailfrom="email1@xxx.com")) as "count of email1@xxx.com",count(eval(mailfrom="email1@yyy.com")) as "count of email1@yyy.com",count(eval(mailfrom="email1@xyz.com")) as "count of email1@xyz.com",count(eval(mailfrom="email1@abc.com")) as "count of email1@abc.com", last(_time) as "EmailSent" | convert ctime("EmailSent") as time | table *

or

...| eventstats count as "total count" | stats count by mailfrom|....

where mailfrom={"email1@xxx.com" ; "email2@yyy.com" ;"email3@zzz.com" ; "email4@abc.com" ; "email5@xyz.com"}

0 Karma
Highlighted

Re: How to write a search to get the total count of emails sent out and the count for each individual mail ID?

Explorer

Hi,

This query isn't working. total count is displayed under individual mail id's rather than their individual count.

0 Karma