cancel
Showing results for 
Search instead for 
Did you mean: 
bleearg13
Level 14

Using Alert actions to update a custom property

Jump to solution

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'}

0 Kudos
3 Replies
Highlighted
tdanner
Level 19

Re: Using Alert actions to update a custom property

Jump to solution

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.

bleearg13
Level 14

Re: Using Alert actions to update a custom property

Jump to solution

Thanks!  That seems to work.  I'm going to try something else with this now and see how it works.

0 Kudos
golph_boy
Level 7

Re: Using Alert actions to update a custom property

Jump to solution

is there a way to get past the "relative UPDATE" issue you mention here as I am seeing exactly this. My counter is doubled for every event.   

 

Thanks  

0 Kudos