Conditional email addresses within email actions SWQL query

This may belong in the alert lab - but it's not explicitly an alert of xml file type and is in essence a SWQL query.

Regardless I wanted to show how you can use the custom SWQL variables within the to / cc / bcc fields in email actions to send email conditionally to specific folks. This could also be a custom SQL variable also but works out better to be SWQL. Similar custom variables can also be leveraged in other alert actions such as filling in the data of a text custom property conditionally also.

Some caveats with this - you cannot use parenthesis within the queries, the email actions do not like them and won't accept the input.

###############################################################
##### Based off node custom property and alert ID
##### For a Node alert assigned action
 
${N=SWQL;M=SELECT TOP 1 CASE WHEN ${N=Alerting;M=AlertID} = 1 AND n.CustomProperties.node_cp_name LIKE '%I-MANAGE-THIS%' THEN 'newemail@localhost.test' WHEN ${N=Alerting;M=AlertID} = 2 AND n.CustomProperties.node_cp_name LIKE '%I-MANAGE-THIS%' THEN 'newemail@localhost.test' END AS [NewMail] FROM Orion.Nodes AS n WHERE n.NodeID = ${N=SwisEntity;M=NodeID}}
 
 
 
###############################################################
##### Based off node custom property and alert custom property
##### For a Node alert assigned action
 
${N=SWQL;M=SELECT TOP 1 CASE WHEN ac.CustomProperties.alert_cp_name LIKE '%WE-WANT-THIS-ALERT%' AND n.CustomProperties.node_cp_name LIKE '%I-MANAGE-THIS%' THEN 'newemail@localhost.test' END AS [NewMail] FROM Orion.Nodes AS n INNER JOIN Orion.AlertConfigurations AS ac ON ac.AlertID = ${N=Alerting;M=AlertID} WHERE n.NodeID = ${N=SwisEntity;M=NodeID}}
 


###############################################################
##### Based off node custom property and alert custom property
##### For a Volume alert assigned action
 
${N=SWQL;M=SELECT TOP 1 CASE WHEN ac.CustomProperties.alert_cp_name LIKE '%WE-WANT-THIS-ALERT%' AND v.Node.CustomProperties.node_cp_name LIKE '%I-MANAGE-THIS%' THEN 'newemail@localhost.test' END AS [NewMail] FROM Orion.Volumes AS v INNER JOIN Orion.AlertConfigurations AS ac ON ac.AlertID = ${N=Alerting;M=AlertID} WHERE v.Node.NodeID = ${N=SwisEntity;M=Node.NodeID}}

Anonymous