How do you email alerts for 50+ different systems and users?

[UMDTERPS]

Organization	System	Scan Due  Date	email of SA
ABC	Jack	7-Feb-21	jack@email.com
ABC	Jill	9-May-20	jill@email.com
123	Bob	Unspecified	bob@email.com
123	Alice	Unspecified	alice@email.com
456	James	10-Jan-21	james@email.com

| inputlookup scan_due_date.csv
| eval date = strptime('Scan Date', "%d-%b-%Y")
| eval duedate = if(isnull(date) OR date="", "Unspecified", date)
| eval status=case(duedate >= now(),"Not Expired",duedate="Unspecified",duedate true(),"Overdue")
| fields date duedate status
| stats count by status

I am trying to set up email alerting for systems, the above table and search work to show which systems are passed or current their scan date. I want to make an alert for each system that is overdue for their scan date or Unspecified, without having to write an alert for each individual system.  Each individual system has their own unique email. 

Is there a way to write an eval statement that will look at the status field and email the system owner that the system is overdue or Unspecified without having to write out 50 different eval statements for each system email to generate an alert?

Thanks!

[anilchaithu]

@UMDTERPS 

use map along with sendemail command to send alerts.

base search generating results | map search="...| sendemail to="$email$""

[UMDTERPS]

@anilchaithu 

| inputlookup scan_due_date.csv 
| eval date = strptime('Scan Date', "%d-%b-%Y") 
| eval duedate = if(isnull(date) OR date="", "Unspecified", date) 
| eval status=case(duedate >= now(),"Not Expired",duedate="Unspecified",duedate true(),"Overdue") 
| search status=Unpecified OR status=Overdue 
| map [| sendemail to="$email$" subject= Alert - Shame on you! Your System Scan is Either Overdue or Date Unspecified" from=bob@mail.com message="Shame!" ] 

When run the search I get "0 results found." I'm assuming it has to do with the sub-search data not getting into the map command?  How do I get the search above into the map command?

| map search= "| inputlookup scan_due_date.csv
| eval date = strptime('Scan Date', "%d-%b-%Y")
| eval duedate = if(isnull(date) OR date="", "Unspecified", date)
| eval status=case(duedate >= now(),"Not Expired",duedate="Unspecified",duedate true(),"Overdue")
| search status=Unpecified OR status=Overdue
| sendemail to="$email$" subject= Alert - Shame on you! Your System Scan is Either Overdue or Date Unspecified" from=bob@mail.com message="Shame!""

If do it similar to what you have in your spl, none of commands highlight and the search won't run. 

Any ideas?  Thanks!

[anilchaithu]

@UMDTERPS 

Do you have "email" field in your base search? You should have email field in your query. If the field name is not email, Please change the token on map search command accordingly. The output for this search query will be zero. 

The syntax is not correct in your query. Its not a sub search. I guess I was not clear earlier. Try this 

| inputlookup scan_due_date.csv 
| eval date = strptime('Scan Date', "%d-%b-%Y") 
| eval duedate = if(isnull(date) OR date="", "Unspecified", date) 
| eval status=case(duedate >= now(),"Not Expired",duedate="Unspecified",duedate true(),"Overdue") 
| search status=Unpecified OR status=Overdue 
| map search="| sendemail to="$email$" subject= "Alert - Shame on you! Your System Scan is Either Overdue or Date Unspecified" from="bob@mail.com" message="Shame!" "

Hope this helps.

[UMDTERPS]

@anilchaithu 

| inputlookup email_test.csv
|map [
| eval date = strptime('Scan Date', "%d-%b-%Y")
| eval duedate = if(isnull(date) OR date="", "Unspecified", date)
| eval status=case(duedate >= now(),"Not Expired",duedate="Unspecified",duedate true(),"Overdue")
| search status=Unpecified OR status=Overdue
| sendemail server=test.email.com to="$email$" subject= "Alert - Shame on you! Your System Scan is Either Overdue or Date Unspecified" from=bob@mail.com.com message="Shame!"
]

It has to look something like this for it to work (similar to your first post).  I get confused if I don't see the full syntax of the SPL.😎 

[anilchaithu]

@UMDTERPS 

Hope it gave some direction

[gjanders]

sendresults (splunkbase) can also do this...and it's a nicer alternative to map in many cases...