3 Replies Latest reply on May 12, 2010 10:37 AM by golph_boy

    Using Alert actions to update a custom property


      Can the SQL variables used in alerts be used to run an UPDATE query?  I've been attempting to set up an alert that posts a message to the NPM Event Log and runs an UPDATE query to change the value of a custom property and it's not working.  The query seems correct, as I can run it in Enterprise Manager.  I have a custom property called "DepLevel" which I would like to update upon the status of 'Down' for any node:

      ${SQL:UPDATE Nodes SET DepLevel = '100' WHERE NodeID = ${NodeID} AND Status = '2'}

        • Re: Using Alert actions to update a custom property

          Yes, you can do this. But you have to be sneaky.

          The ${SQL} variables are really intended for looking things up in the database, not changing the database. So as a sanity check, the alert engine checks to make sure the SQL statement actually starts with "SELECT".

          But we can trick it. SQL Server will accept multiple statements separated by a semicolon. I was able to use this in a "Log to NPM Event Log" action:

          ${SQL: SELECT 'look the other way';UPDATE Nodes SET MyCustomProperty='hello' WHERE NodeID=${NodeID} }

          One little warning: I'm not certain that the SQL variable will always be resolved exactly once per time the alert triggers. For a SELECT or a typical UPDATE like the one in your example, it should cause no problem if it gets run more than once. But if you are doing an INSERT or a relative UPDATE (such as "UPDATE Nodes SET ProblemCount=ProblemCount+1 WHERE NodeID=${NodeID}") in your action, it might get executed more than once, leading to confusion and consternation.