Sending emails using sendemail and map command not sending empty emails

[assennikolov]

I have the following question regarding using the sendemail command together with the 'map' one.

Using the below search I managed to send the data to respective recipients based on the results, however, when there are no results no email is being sent.

| inputlookup 'email_addresses + name.csv'
|  map 
    [search index=test_index
    | search name="$name$" 
    | fields name title attributes
    | stats count(eval(case(attribute="A", 1))) as "Attribute A" count(eval(case(attribute="B", 1))) as "Attribure B" count by name title _time 
    | eval _time=strftime(_time, "%+")
    | appendpipe [stats count | where count=0 ]
    | sendemail bcc="$email_address$" subject="Table $name$ | _time" sendresults=true inline=true format=table 

lookup contains two columns: email_address and name
I tried different scenarios but using for example makeresults, appendpipe to insert dummy data in case no results are returned, but all caused a duplication of the email being sent, where the second email is missing the tokenization specified in the sendemail command.

[scelikok · Accepted solution]

Hi @assennikolov,

Please try below;

| inputlookup 'email_addresses + name.csv' 
| map 
    [ search index=test_index 
    | search name="$name$" 
    | fields name title attributes 
    | stats count(eval(case(attribute="A", 1))) as "AttributeA" count(eval(case(attribute="B", 1))) as "AttribureB" count by name title _time 
    | inputlookup append=t 'email_addresses + name.csv' 
    | fillnull value="" _time, title 
    | stats max(AttributeA) as "Attribute A" max(AttribureB) as "Attribure B" max(count) as count by name title _time 
    | eventstats sum(count) as totalCount
    | where (totalCount>0 AND count>0) OR totalCount=0
    | fields - totalCount
    | eval _time=strftime(_time, "%+") 
    | sendemail bcc="$email_address$" subject="Table $name$ | _time" sendresults=true inline=true format=table

[assennikolov]

Thanks, @scelikok ,

It works like a charm.

[assennikolov]

Hi @scelikok , 

Thank you for your swift reply.

The way the search is constructed always returns an additional line, which helps a lot in sending emails when there is no data. However, if I got one or more results this 'additional' line is still present. I tried to prettify it by replacing every value with "-" (fillnull). However, it looks something similar to the below table:

Name	Title	Time	Attribute A	Attribute B	Total Count
John	Vice President	Wed Feb 24	1	23	24
Bob	-	-	-	-	-

Do you think there is a way I can get rid of the data containing dashes when there are actual results and sending only them to the respective users?

Thank you very much for your assistance.

[scelikok]

Hi @assennikolov,

I couldn't test it but please try below;

| inputlookup 'email_addresses + name.csv'
|  map 
    [search index=test_index
    | search name="$name$" 
    | fields name title attributes
    | stats count(eval(case(attribute="A", 1))) as "AttributeA" count(eval(case(attribute="B", 1))) as "AttribureB" count by name title _time 
    | inputlookup append=t 'email_addresses + name.csv'
    | fillnull value="" _time, title
    | stats max(AttributeA) as "Attribute A" max(AttribureB) as "Attribure B" max(count) as count by name title _time
    | eval _time=strftime(_time, "%+")
    | sendemail bcc="$email_address$" subject="Table $name$ | _time" sendresults=true inline=true format=table