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.
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
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
Thanks, @scelikok ,
It works like a charm.
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.
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