Alert not working in HTML

I'm hoping someone can offer me some insight on what I can do here.

I'm using this statement in an Alert email:

${SQL:Select AVG(ResponseTime.Availability) AS AVERAGE_of_Availability FROM Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID) WHERE ( DateTime BETWEEN DATEADD(DAY, -2, GETDATE()) AND DATEADD(DAY, -1, GETDATE()) ) AND ((Nodes.NodeID=${NodeID})) }%

If I send the email in plain text mode, it correctly returns the value. If I send it in HTML mode, which I prefer, it gives the following error (32 being the NodeID):

MACRO SQL ERROR - Incorrect syntax near '32'.)) %

If it looks like a parsing bug in Orion, or if there's a way to structure the statement differently to make it work. I have other SQL statements in the same alert email that are working correctly, like this:

${SQL:SELECT convert(varchar, floor(Systemuptime/86400)) + ' days, ' + convert(varchar, (convert(int,Systemuptime)%86400)/3600) + ' hours, ' + convert(varchar, (convert(int,Systemuptime)%3600)/60) + ' minutes ' FROM Nodes WHERE NodeID=${NodeID} }

Any assistance would be greatly appreciated.

Find more posts tagged with

sql

Alerts

html

Accepted answers

All comments

qle

Is it fair to assume that you're able to execute the first SQL query directly against the database using Database Manager/SQL Server Management Studio and receive proper results?

thewerd

Yeah.

If I run the query directly on the database and remove ${NodeID} and replace it with the correct appropriate number, it returns the correct result.

If I do the same in the alert itself (replacing ${NodeID} with the actual node number, it also properly spits out the correct result.

qle

I'm reaching here but does it make a difference if you remove the percent symbol at the end of your query?

thewerd

I wish. I tried the same as well, no dice.

It feels a lot like a parsing bug in Orion for HTML Advanced alerts, since the exact same statement works fine in plain text, instead of HTML.

I was hoping there might be a way to get around that, by structuring the query differently, or something. I'm at a loss.

qle

Have you tried simplifying the query to the point where the query provides a result (not necessarily the correct result)? Then build from there to see where problem pops up.

thewerd

The problem is entirely with the ${NodeID} variable in the statement, and how Orion parses it when sending out an HTML advanced alert it would seem. As the statement as-is works in plain text, and if the variable is removed, and statically replaced with it's node ID value, it also works.

ZZamazal

Hello,

this is truly reproducible in NPM 10.1.3. However changes were already submitted to the code of upcoming version. the SQL statement must work both in plain text and in html formated text.

-Zdenek