set up a sql variable on an alert

nmsfornet

Hello,

I would like to set up an alert that will send an email with information retrieved by a SQL query. Below is the SQL that retrieves information from all table. My main request is to have the actual 'Status' value that the poller retrieved the time that was triggered and send the value.

SELECT TOP 1000 * FROM [dbo].[CustomPollerStatistics_Detail]

where custompollerassignmentid = 'e755f110-28a4-49cc-93ce-d2db6c94bcf1'

Can you inform me what is the SQL variable query that needs to be assigned? If you need further information, please let me know.

thank you.

Screen Shot 2015-02-11 at 11.49.48 PM.png

dhanson

So do you want to alert on this custom poller based on the value of this or do you want to just add this value in the alert e-mail that gets sent out?

If you're wanting to alert, in 11.5, you can do this using the web alert creation tool. You don't need a SQL query to pull it. Just observe the options for Custom Pollers, and I'm sure you can find one that returns the value you're really looking for.

If you are just adding the value to an alert action's e-mail, then you can reference my response on this post:

Adding variables from other custom pollers into an alert

Basically, the query inserted in the message would be something like:

${SQL:SELECT [a].Status FROM [dbo].[CustomPollerStatus] AS [a] WHERE [a].CustomPollerAssignmentID = (SELECT [b].CustomPollerAssignmentID FROM [dbo].[CustomPollerAssignment] AS [b] WHERE [b].NodeID ='${NodeID}' AND [b].CustomPollerID = (SELECT [c].CustomPollerID FROM [dbo].[CustomPollers] AS [c] WHERE [c].UniqueName LIKE '%<THENAMEOFYOURPOLLER>%'))}

If you wanted to test the logic of this poller, you'll have to replace ${NodeID} with a specific nodeID that you can pull from your Nodes table. For instance, one of the nodes I have that has a UnDP associated has NodeID 117. so you'd simply replace ${NodeID} with 117, and correct the '%PollerName%' entry. Since you already know the CustomPollerAssignmentID, there's a far simpler way to do this, but I designed this statement to work with any custom poller.

With your SQL Query, it's currently returning each row with a matching CustomPollerAssignmentID, which ends up being a lot of data. In order to have an alert return specifically a single value, you'll need to narrow your results to match a single row, then a single column (or vice versa).

If you don't like my messy one above, you can also do this:

SELECT TOP 1 Status FROM [dbo].[CustomPollerStatistics_Detail]

WHERE CustomPollerAssignmentID = 'e755f110-28a4-49cc-93ce-d2db6c94bcf1'

ORDER BY DateTime DESC

If this is going into an e-mail message, just encapsulate it within ${SQL:<query>}.

Now, the SQL guru's on here might tell me to stop in my tracks, as I'm kind of a newbie when it comes to SQL, but this should give you the most recent value recorded for your specific CustomPollerAssignmentID. Just keep in mind that my query above is a little more flexible, as all you'll need to know is the name of your poller to implement it.

Anyway, hope this helps, at least a little bit! If anything, it'll point you at learning some more SQL.

SQLCourse - Interactive Online SQL Training for Beginners