Trigger Action - Email SWQL/SQL in body

I have SWQL code that creates a table from two UnDP with an output of a percentage. The RawStatusRatio and the the SecondAssignment is what I need in an email for alerting purposes. The original SWQL for both the table and the Alert Condition itself seems to not have an issue but I cannot get the table to display in the email action. At worst I would be willing to get the two requirements (RawStatusRatio and the the SecondAssignment) but I'm trying to get the information from the code itself.

Eventually I would like to use the ${NodeID} variable so I can associate with multiple devices but that is for another day.

SELECT
    a.NodeID,
    a.AssignmentName AS FirstAssignment,
    a.RawStatus AS FirstRawStatus,
    b.AssignmentName AS SecondAssignment,
    b.RawStatus AS SecondRawStatus,
    (a.RawStatus * 1.0 / b.RawStatus) AS Percentage
FROM Orion.NPM.CustomPollerStatusOnNode a
JOIN Orion.NPM.CustomPollerStatusOnNode b
    ON a.NodeID = b.NodeID
    AND a.AssignmentName < b.AssignmentName  -- Ensures distinct assignments for comparison
WHERE a.NodeID = '18656'
AND a.AssignmentName LIKE 'fw%'
AND b.AssignmentName LIKE 'fw%'
ORDER BY a.AssignmentName, b.AssignmentName;

In turn, I have created an SWQL alert where the Trigger Condition works. I can show that it triggers when the condition is below 6% but does not above 6%. I get the email and log message.

The issue that I have is that I believe I should be able to add the SWQL/SQL code in the email message, delayed by 5 minutes, but it keeps returning the code itself. I've had no issue using variables in other alerts but this one is stumping me. Changing it from SQL to SWQL seems to have the same issue.

${N=SQL;M=SELECT 
    a.NodeID,
    a.AssignmentName AS FirstAssignment,
    a.RawStatus AS FirstRawStatus,
    b.AssignmentName AS SecondAssignment,
    b.RawStatus AS SecondRawStatus,
    (a.RawStatus * 1.0 / b.RawStatus) AS RawStatusRatio
FROM Orion.NPM.CustomPollerStatusOnNode a
JOIN Orion.NPM.CustomPollerStatusOnNode b 
    ON a.NodeID = b.NodeID 
    AND a.AssignmentName < b.AssignmentName  -- Ensures distinct assignments for comparison
WHERE a.NodeID = '18656' 
AND a.AssignmentName LIKE 'fw%' 
AND b.AssignmentName LIKE 'fw%'
ORDER BY a.AssignmentName, b.AssignmentName;}

Any help would be greatly appreciated. I will also upload the alert and documentation once I get this last part working.

Thank you,

Find more posts tagged with

checkpoint

swql query

advanced_alert

Alert

undp_advanced_alert

sql

dashboard

SWQL

alert_condition

UnDP

sql alerts

HCO

email action

Accepted answers

Lofstrand

Try using the variable ${N=SwisEntity;M=NodeID} instead of ${Nodeid}

I found that this made a difference.

smalleggo

The issue appears to be needing to change the ${nodeID} to the ${N=SwisEntity;M=NodeID} Good catch @Lofstrand

For whatever reason this seems to have resolve the issue. I'll need to dig into it and determine why that is the case. Good catch and much appreciated.

Thank you everyone for looking into this. Plan is to post the what and why once I figured this out so hopefully this helps the community.

All comments

maciej.sandecki

Have you tried this?:

change N=SQL to N=SWQL (you are using SWQL syntax, so keeping SQL will cause errors because entities like Orion.NPM.CustomPollerStatusOnNode are not known)
make it one-liner (remove all new lines, tabs, keep only spaces) and remove comments
remove the trailing ; (before closing } ), not sure if it's important, but I don't use it in my queries in actions
I'm not sure on this one, but I use queries that return only single value (and single row), so maybe check SELECT TOP 1 (again, not sure if this is a limitation, I just haven't tried with more rows)

smalleggo

I've made it super simple in the attempt to get it to display. Doing the SELECT into a widget displays the single number I am expecting however the email using SQL/SWQL and <p> </p> provides the result below. Interesting that the ${NodeID} adjusts the right NodeID but the rest does not run. Looking at SDK, CP, and pulling in said CP to make it work but I'd think that wouldn't be needed. This is also HTML formatted for reference.

${N=SQL;M=SELECT RawStatus FROM Orion.NPM.CustomPollerStatusOnNode WHERE NodeID = 18656 AND AssignmentName LIKE '%fwConn%'

vonzoom

I'm not a DBA, but we have a working SWQL query in an email from a UPS Alert. I'm not sure it will help, but I'll put the code we use here for your review. Nothing special in the alert email config, just set to HTML, which you said yours is set to as well.

Alerting Device Name: ${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
			CHARINDEX('mtrapargsOctetString02', '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+24,
			(CHARINDEX(', mtrapargsString04', '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('mtrapargsOctetString02', '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')-24)
			) AS Message
FROM Orion.Nodes n}

Lofstrand

Hi, as @maciej.sandecki said, you are stating that it's a SQL query when it's actually a SWQL query.

Here is a simple one:

${N=SWQL;M=SELECT N.caption from Orion.Nodes as N WHERE N.nodeid=${N=SwisEntity;M=NodeID} }

smalleggo

Sorry, I have attempted both ways as shown below. This same query works without issue on the dashboard or going against the DB itself. The issue is specifically the email body which has other variables working. The NodeID is the ${NodeID} variable so you can see that SolarWinds is reading the text, just not the script itself.

I am attempting the script to return the number for two different UnDP associated with the NodeID in question. Due to using a Custom SWQL alert The UnDP macro or variable also do not seem to work correct. I am going down that path along with using SDK to update custom properties and suck in the data that way but I'd prefer the script to just output the data it should be gathering.

${N=SWQL;M=SELECT RawStatus FROM Orion.NPM.CustomPollerStatusOnNode WHERE NodeID = ${NodeID} AND AssignmentName LIKE '%fwConn%'