Advanced SQL Help (Macro ???)

Question

I was reading several post and found some info about using Advanced SQL statements with the advanced Alert engine to use when monitoring UPS'.  All of those post have proven to be extremely helpful but I am having a problem with one statement and I can not figure this out and I am curious if someone can give me a hint on where AI went wrong.  I am using this statement.

Current utility input voltage is ${SQL:Select Rate
From CustomPollerAssignment as Assignment, CustomPollerStatus as Status, CustomPollers
where Assignment.PollerID = CustomPollers.PollerID and Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${Node.ID} and Assignment.AssignmentName like 'upsInputVoltage%'} VAC.

I found the statement above in a post and the text in bold type is where I substituted my custom poller name.  Where I am getting stuck is with the error that is returned when I trigger the alert.  The error I am getting is below?

Current utility input voltage is MACRO SQL ERROR - Line 3: Incorrect syntax near '{'. VAC

We are still using Orion SLX V8.5.1 and I am not sure how to trouobleshoot that error.  If anyone has any thoughts or suggestions I would greatly appreciate your input.

euphorik · Answer

Sometimes it's the small things. You were referencing an invalid variable.

Remove the DOT from the NodeID field within the statement.    ${Node.ID} to ${NodeID}

Current utility input voltage is ${SQL:Select Rate
From CustomPollerAssignment as Assignment, CustomPollerStatus as Status, CustomPollers
where Assignment.PollerID = CustomPollers.PollerID and Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${NodeID} and Assignment.AssignmentName like 'upsInputVoltage%'} VAC.

euphorik · Answer

I think the issue is the nesting of SQL statements within the macro statement, specifically the nesting of ${Node.ID} which is required to substitute the alarming node.ID into the proper select statement. If you substitute the ${Node.ID} with the actual node ID of the device in question (ie. 181) or whatever NodeID your UPS is.

I just picked this up recently when one of my UPS's actually alarmed and I got the same information multiple times since I'd accidentally left my static NodeID in my SQL query.

I'll see if there is another way to escape and double nest substitutions....Anyone?

joehanly · Answer

Hi

At the end of your Query can you try removing the } in the line and Assignment.AssignmentName like 'upsInputVoltage%'} VAC

-J