How to display zero if there are null values reporting for a mail id?

[Navanitha]

Hi,

I have a search similar to the one below which gives the total count of emails sent out. Here, when there are no emails sent out from a particular email id say email1@xxx.com, I need to display the count as zero under the table. Can someone help me here?

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

Thanks in advance.

[somesoni2]

For this solution to work, you would need to have the field extracted which will contain the email address you're searching. Let's assume the field name is emailAddr. Then try this

index=xyz emailAddr="email1@xxx.com" OR emailAddr="email2@yyy.com" OR emailAddr="email3@zzz.com" OR emailAddr="email4@abc.com" OR emailAddr="email5@xyz.com" "ready" "MID"|stats count, max(_time) as "EmailSent" by emailAddr| convert ctime("EmailSent") as time | table count, time emailAddr | append [| gentimes start=-1 | eval emailAddr="email1@xxx.com,email2@yyy.com,email3@zzz.com,email4@abc.com,email5@xyz.com" | table emailAddr | makemv emailAddr delim="," | mvexpand emailAddr | eval count="0" | eval time="NA" ] | stats list(*) as * by emailAddr | eval count=mvindex(count,0) | eval time=mvindex(time,0)

[Navanitha]

I tried it but to my surprise the count from all the email id's is displayed as '0' which should not be the case coz there are mails sent out from all the id's with variation in the count. and the email sent time is showing "NA".

I have a field with email_id extraction.

[alacercogitatus]

Fill null might help you here. I'm assuming sender is present as a field, and contains the email address used to send the email.

<your search> | stats count last(_time) as lt by sender | fillnull value=0 count | convert ctime(lt) as time

[jkat54]

Can you please provide a sample of the data with PII / sensitive data redacted?

From what I can tell now, you'll need to use stats without the table, and maybe do some eval work to insure 0's show up.

[Navanitha]

index=sample "email1@xxx.com" OR "email2@yyy.com" OR "email3@aaa.com" OR "email4@bbb.com" OR "email5@ccc.com" "ready" "MID"|stats count as Count by Email_ID | append [ search index=sample "email1@xxx.com" OR "email2@yyy.com" OR "email3@aaa.com" OR "email4@bbb.com" OR "email5@ccc.com" "ready" "MID"| stats count as "Total Emails sent out", last(_time) as "EmailSent" | convert ctime("EmailSent") as "Last email sent" | table "Last email sent" , "Total Emails sent out" ]

This is my query..I know this looks weird but this is how I made things work till now 😉 Well in the above query, first stats gives the individual count of emails sent out from each email id, and the second search gives the total count of emails sent from all the individual.