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

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

Navanitha — Mon, 08 Jun 2015 08:21:22 GMT

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

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

stephanefotso — Mon, 08 Jun 2015 09:13:45 GMT

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

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

stephanefotso — Mon, 08 Jun 2015 09:39:21 GMT

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

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

Navanitha — Mon, 08 Jun 2015 09:40:17 GMT

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

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

fdi01 — Mon, 08 Jun 2015 10:27:56 GMT

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 *

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 *

...| 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"}

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

Navanitha — Mon, 08 Jun 2015 10:56:07 GMT

Hi,

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

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

Navanitha — Mon, 08 Jun 2015 14:05:01 GMT

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.

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

stephanefotso — Mon, 08 Jun 2015 16:00:25 GMT

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