Alert Email Subject Control

[KAC]

I am producing data like this in an alert that will throw an email, which is needed. I'm attempting to control the email Subject and Message. I need to make an adjustment though. If all of the statuses are "SUCCEEDED" then I need to show that in the Subject and in the message. However, if any of the status are something other than "SUCCEEDED" then I need the Subject and the message to show that.

NOTE: There will always be 5 items; That part is working as needed.

item Status Message

---

1 SUCCEEDED Success Message
2 SUCCEEDED Success Message
3 SUCCEEDED Success Message
4 FAILED Failure Message
5 SUCCEEDED Success Message

Approach creating the above

| eval subject= if(status="Failure","FAILED","SUCCEEDED")
| eval message= if(status="Failure","Failure Message","Success Message")
| rename affected_ci as URL, subject as Status, event_date_time as Date
| table item, status, message,

What I'm needing is

item Status Message Subject_Value Email_Message

---

1 SUCCEEDED Success Message Failure Failure Message
2 SUCCEEDED Success Message Failure Failure Message
3 SUCCEEDED Success Message Failure Failure Message
4 FAILED Failure Message Failure Failure Message
5 SUCCEEDED Success Message Failure Failure Message

The idea here is, I need to pass the subject and email message into every row, then use the

$result.Subject_Value$ and $result.Email_Message$ in the appropriate field.

[KAC · Accepted solution]

After many attempts, I took a weekend off to clear my mind and came up with the following

1) Create a search that ends in a table that determines if the entire process is a Pass or a Fail. To ensure every scenario worked, I needed to include the following

makeresults | eval record_count=null
| fields - _time
| append
Search | eventstats count as record_count | eval PASSFAIL=if(record_count=1,"Succeeded","Failed") | table PASSFAIL

2) append my original search to the results above

3) add a Stats command that brings the results into one record, rather than 5 records

| stats values(PASSFAIL) as Overall_Status, list(URL) as URL, list(Status) AS URL_Status, List(message) as URL_Message

The result are 1 row, but with the results from the 5 records consolidated into the one row
- I can pass the PASSFAIL value into the email
- I can include an inline table showing all the details.

I hope this helps others