3 Replies Latest reply on Sep 10, 2008 12:44 PM by euphorik

    Advanced SQL Help (Macro ???)

    KGaudineer

      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.

        • Re: Advanced SQL Help (Macro ???)
          joehanly

          Hi


           


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


           


           


          -J

            • Re: Advanced SQL Help (Macro ???)
              euphorik
              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?
                • Re: Advanced SQL Help (Macro ???)
                  euphorik
                  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.