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

[Navanitha]

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

[fdi01]

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

[Navanitha]

Hi,

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

[stephanefotso]

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 

[Navanitha]

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.

[stephanefotso]

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

[stephanefotso]

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

[Navanitha]

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