Looping emails for multiple events

[ramanjain1983]

Hi there,

I have created a query which returns something like this :-

OrderNo CreatedBy OrderError

0001 Alice Faulty

2222 Prince None

..............................................

..............................................

9999 Text Wrong Capture

The query will return me number of records in above fashion in case of errors only. It means if there are not any error then results will be ZERO.

Now what I want to do is that if there are errors which could be single entry or multiple entries like as shown above then I would like to send an email to respective users whose names are given in field =CreatedBy. Also I need to do a lookup for email ID for that respective user.

Can anyone suggest some possible approach for this.

I was thinking that if I will have a ldap python script which will take CreatedBy field as argument and then will return an outfield = EMAILID then I would like to call sendemail command to send the result. But here the challenge is that the query may return multiple entries so in that case I am not sure how splunk will treat it.

let's say the query is :-

Primary search [secondary search | fields OrderNo] | fields OrderNo CreatedBy OrderError | someExternalPythonLDAPLookupCommand outfield=emailid "$CreatedBY" | sendemail To="$emaildid" server="" from="a@a.com"

If I use the above concept , will it solve my requirement. I doubt what will happen if the outcome is a multi value table.

Thanks

[ramanjain1983]

CLOSED AND KUDOS TO MARTIN AND LGUINN!

[martin_mueller]

In order to mark an answer as accepted, press the checkmark next to that answer.

[ramanjain1983]

I tried that way but the problem is actually with the arguments passed inside the sendemail python script. I changed the script a bit to pass custom argument in body and everything is so smooth now....

Thanks a lot Martin and Lguinn for your advise on this question. Kudos!!!

[ramanjain1983]

Thanks Martin and Lguinn for all the responses on it. Seems like this map command is really a good weapon for lot of other things. It is working for me.....I just need to change the maxsearches to repeat the number of loop which is count of total events.

The only challenge is that the results sent inside the mail does not have anything. Ideally the line should be printed. I need to send one field in that and I am not sure if you can send custom text or body in the email. Any idea on that please...

[lguinn2]

index=main sourcetype="DBMONCustom*"
| join type=inner [ | inputlookup hostlookup] 
| dedup TablespaceName 
| table * | head 5 
| map maxsearches=0 search="stats count 
| eval TablespaceName=$TablespaceName$ 
| fields - count 
| sendemail server=test@test.com from=test@test.com to=$emailto$ subject=$TablespaceName$ sendresults=true"

is what I think you will have after combining @martin_meuller's advice with your original search. I removed the first table command, as it was doing nothing. Why are you using inputlookup? Are you getting a bunch of fields from the lookup table?

[martin_mueller]

The searches launched by map don't know anything about the outer search. In order to copy over data from the row of the outer search responsible for launching a map search, you need to use $foo$ tokens. For an example, see Lisa's search:

...
| eval TablespaceName=$TablespaceName$ 
...

That creates a field which should appear in the inline results table of the email.

[martin_mueller]

The combination of join and inputlookup feels to me as if it'd be equivalent to a regular lookup while filtering for only events that actually had something look up.

[martin_mueller]

Writing a scripted alert will certainly work well, and will allow you to send more customized emails.
If however you want to avoid that for whatever reason, here's a pointer on how to make Splunk send one email per result row using an address from that very row:

your search creating the table you posted | some way of adding the email address to each row into field email | map maxsearches=0 search="stats count | eval OrderError=$OrderError$ | fields - count | sendemail server=yourserver to=$email$ subject=\"Faulty order $OrderNo$\" sendresults=true"

That map will run its search once per result row, sending an email to the address from the result with the OrderNo in the subject and OrderError in the body.
If the users are also Splunk users you can use Splunk to retrieve the email addresses from their Splunk account, avoiding the external ldap lookup thingy...

[martin_mueller]

If there are errors, chances are low that you're doing things correctly.

I see a closing quotation mark after two words of map's search, that can't be right. The quoted string should end after the end of the sendemail command, otherwise the $foo$ token replacement of map will not work back there.

[wangjianiu]

hi Martin, i am using map along with the sendemail, but Spluk gives me a authentication error like below. but it works fine if I only use sendemail, some blog said the subsearch lost the session, do you have any idea to fix this? or which version you are running on? i am on 8.0.1

endemail:1454 - [HTTP 403] Client is not authorized to perform requested action; 
File "/opt/splunk/etc/apps/search/bin/sendemail.py", line 1447, in <module>
    results = sendEmail(results, settings, keywords, argvals)
  File "/opt/splunk/etc/apps/search/bin/sendemail.py", line 403, in sendEmail
    jobResponseHeaders, jobResponseBody = simpleRequest(uriToJob, method='GET', getargs={'output_mode':'json'}, sessionKey=sessionKey)
  File "/opt/splunk/lib/python2.7/site-packages/splunk/rest/__init__.py", line 559, in simpleRequest
    raise splunk.AuthorizationFailed(extendedMessages=uri)
AuthorizationFailed: [HTTP 403] Client is not authorized to perform requested action; 

[ramanjain1983]

Am i doing it rightly ....or any other solution please.

I need to send an email to all people in field 'emailto' in above table with subject line having TablespaceName respectively.

[ramanjain1983]

index=main sourcetype="DBMONCustom*"| table TablespaceName| join type=inner [| inputlookup hostlookup] | dedup TablespaceName | table *| head 5 | map maxsearches=10 search="stats count"| eval TablespaceName=$TablespaceName$ | fields - count | sendemail server=test@test.com from=test@test.com to=$emailto$ subject=$TablespaceName$ sendresults=true

but it is returning me this error :-
command="sendemail", {'$emailto$': (501, "#5.1.3 Partial domain not allowed: ''")} while sending mail to: $emailto

[splunkhu123]

This sendmail command worked for me.😀

| rest timeout=600 splunk_server=local /servicesNS/-/-/saved/searches add_orphan_field=yes count=0
| search orphan=1 disabled=0 is_scheduled=1 title IN ("Missed_workfusion","Failed Intake Document")
| eval status = if(disabled = 0, "enabled", "disabled")
| fields title eai:acl.owner eai:acl.app eai:acl.sharing orphan status is_scheduled cron_schedule next_scheduled_time next_scheduled_time actions "action.email.to"
| rename title AS "search_name" eai:acl.owner AS owner eai:acl.app AS app eai:acl.sharing AS sharing "action.email.to" as Email_Address
| map search="| makeresults search_name owner
| sendemail
to=\"$Email_Address$\"
cc="abc@xyz.com;123@yahoo.com"
subject=\"Orphaned Searches/Reports/Alerts to be reviewed and reowned\"
message=\"Below Searches/Reports/Alerts in Splunk were orphaned. \n
Please take necessary action in changing the ownership from retired owners to new owners in getting the reports functional otherwise these reports would be disabled by SPLUNK Team after 15 days since the first trigger\n.
SEARCH NAME : \\\"$search_name$\\\" \n
OWNER OF THE SEARCH : \\\"$owner$\\\" \n
APPLICATION : \\\"$app$\\\" \n
STATUS : \\\"$status$\\\" \n
CRON SCHEDULE OF SEARCH: \\\$cron_schedule$\\\ \n
EMAIL ADDRESS OF SEARCH: \\\"$Email_Address$\\\" \n

Thanks & Regards \n
SPLUNK Team
\""

[ramanjain1983]

Hi there,

so my final command will look like this :-

index=main sourcetype="DBMONCustom*"| table TablespaceName| join type=inner [| inputlookup hostlookup] | dedup TablespaceName | table *| head 5

This is printing the outcome :-

TablespaceName emailto hostname
abc a@a.com aaa
ddd d@f.com eee

Now I changed the command like this (please see next comment as I am hitting the word limit)

[ramanjain1983]

Awesome , let me give it a shot and I will post back my response.

[lguinn2]

I would use a scripted alert to do this. Write the search, leaving out the external command. You can set the alert so that it triggers the script once for each result, or once for all results.

The script will have access to the results in a temporary file. And of course, you can write whatever you like in a script!