When to use SQL or SWQL in email alerts?

devmac94

I am currently receiving a Macro SQL error in my alert message. I have received multiple errors when I switch between SQL and SWQL in the trigger action section on my alert configuration. My goal is to fill the Node.CustomProperties.Department variable to 'Network Engineering' if the value is empty or null. How can I accomplish this?

Here is my current alert message: ${N=SwisEntity;M=Node.Caption} is underperforming. Transmit utilization is now at ${N=SwisEntity;M=OutPercentUtil}. Please investigate ${N=SwisEntity;M=CustomProperties.Monitor_Category} ${N=SwisEntity;M=Caption} for potential network or hardware issues. For more information, contact ${N=SwisEntity;M=Node.CustomProperties.Department} department.

I know I need to use IsNull function in SWQL but my syntax is not working as expected and when I switch to SQL then I receive the macro error. Thanks!

james.kluza

You'd have to to something like this is the custom variable builder [SWQL]: *EDITED to addd ELSE in CASE statement*

SELECT CASE WHEN Department IS NULL OR Department = '' THEN 'Network Engineering' ELSE Department END as [Department]

FROM Orion.NodesCustomProperties 

WHERE nodeid = ${N=SwisEntity;M=NodeID}

Here is the output variable:

${N=SWQL;M=SELECT CASE WHEN Department IS NULL OR Department = '' THEN 'Network Engineering' ELSE Department END as [Department]

FROM Orion.NodesCustomProperties

WHERE nodeid = ${N=SwisEntity;M=NodeID} }