What is the Custom SQL Alert Trigger?

Question

I have a vaild custom sql query that checks the database for information and returns either a 1 or a 0 depending on the table values recieved. How to I actually set the trigger using custom sql and only apply it to one device? When I go to apply the alert it wants to trigger over 200 objects which makles me question how the trigger is set?

Below is the query and it works if I run it in SQL Management Studio 2014. It gets the latest 30 temperatures reported by a device, counts the number of distinct numbers. It returns a 1 if all the numbers are the same and a 0 if the device is functioning normally.

I figure since I am calling out the specific ID's that I want, that is how I would choose which devices this applies to, but when I go to save the alert, Solarwinds tells me it wants to alert over 200 devices....

if((SELECT COUNT (DISTINCT S.Status)

FROM (SELECT TOP 30 STATUS

FROM CustomPollerStatistics

WHERE (CustomPollerAssignmentID = '8205D583-40D8-4E51-8BB8-B1DA856E87BA' OR CustomPollerAssignmentID = '32F24A4F-6B58-43B7-B109-39438D7FC2AF' OR CustomPollerAssignmentID = 'BEE16B39-541C-42B1-AA02-96B17BF2F6E2')

AND (Archive = '0') ORDER BY DateTime DESC)S) = 1)

begin

select 1 as [Result]

end

else

begin

select 0 as [Result]

end

airfoiladmin · Answer

What you said makes alot of sense. After playing with it some more, I can't seem to return any sort of variable or ID that makes this alert happy and not trigger 200 devices. The only way I've been able to make it happy is if I have 1 line that says:

[PRE-AMBLE] SELECT CustomPollerAssignmentView.CustomPollerAssignmentID, CustomPollerAssignmentView.AssignmentName FROM CustomPollerAssignmentView

[CUSTOM] WHERE (CustomPollerAssignmentID = '8205D583-40D8-4E51-8BB8-B1DA856E87BA')

If i try an If statement that returns the CustomPollerAssignmentID if the number of distinct numbers is 1, it doesn't work. I may just be trying to use the alerts in a way that isn't supported.

Thank you for any and all help!

RichardLetts · Answer

there is a preamble to the custom SQL query for alerts, and you have not included that.

The bit that says 'select .... from ....' that is immutable based on the type of object that you are alerting on.

so your query really looks something like

select nodeiid form nodes where ... if(...  from above)

so an alert fires for every node if there is any node that ...

You are going to need to write a query that returns nodeid and the count of distinct status, something like

(SELECT COUNT (DISTINCT S.Status),nodeid

FROM (SELECT TOP 30 STATUS,nodeid

FROM CustomPollerStatistics CPS inner join CustomPollerAssignment CPA on CPA.CustomPollerAssignmentID=CPS.CustomPollerAssignmentID

WHERE (CPS.CustomPollerAssignmentID in ( '8205D583-40D8-4E51-8BB8-B1DA856E87BA' , '32F24A4F-6B58-43B7-B109-39438D7FC2AF' , 'BEE16B39-541C-42B1-AA02-96B17BF2F6E2')

AND (Archive = '0') ORDER BY DateTime DESC)S) = 1)

)

[note: I think you would be better off not using the uuid, but instead matching the specific poller name, in case you accidentally unassign/reassign the custom  poller and then wonder why the alert is never firing]