SQL query to Alert

[kbender]

I have been working for a week on this and have not figured it out. What I want is to have an alert fire from a SQL query.

It is for my CallManager. I have found everything that shows me the number of calls. I am wanting to have SolarWinds send an alert when the number of calls reaches a certain number or higher.

What I have is a Custom SQL Alert. Here is what I have typed in the box:

where custompollerassignmentid = 'e9XXXX2f-0XX8-4XX4-9e07-cXXXXebb32bb' and rowid = '20'

It returns one value in SQL, and I want to find out how to make the Alert system fire when the number returned is 50 or higher.

[zackm]

would it not be easier to use the GUI for this?

[kbender]

I have tried for many days to use the GUI, but to no avail....... it looks like custom SQL queries are the way to go.

[zackm]

Can you show us a screenshot of the results of this query?

SELECT * FROM CustomPollerStatusTable WHERE CustomPollerAssignmentID = 'e9XXXX2f-0488-4XX4-9XX7-ca3eXXXXX2bb' AND RowID = '20'

[kbender]

[kbender]

The Rate is what I need to Alert on

[zackm]

OK, so you have 2 options:

1) as njoylif‌ mentioned, you can just add your rate limitation to your current Custom SQL:

SELECT CustomPollerStatusTable.CustomPollerAssignmentID, CustomPollerStatusTable.CompressedRowID, CustomPollerStatusTable.FullLabel
FROM CustomPollerStatusTable
WHERE CustomPollerAssignmentID = 'e9XXXX2f-0488-4XX4-9XX7-ca3eXXXXX2bb'
AND RowID = '20'
AND Rate >= '50'

2) You can use the GUI, following the screenshot I posted earlier, and get the same end result. (The GUI does nothing more than build the SQL query for you)

Either way you choose should result in the same alert conditions.

[kbender]

I like the one where we build the query.

But how do we make SolarWinds Alert from that?

[kbender]

This is what happens when I add the rate => '50'. I did it both with the tick marks and not.

[hebron1212]

How do you enable advanced alerts? I don't have Custom SQL Query listed.

[zackm]

Try this:

WHERE CustomPollerStatusTable.CustomPollerAssignmentID = 'e9XXXX2f-0488-4XX4-9XX7-ca3eXXXXX2bb'  
AND CustomPollerStatusTable.RowID = '20'  
AND CustomPollerStatusTable.Rate >= '50'

[kbender]

It worked.

[adatole]

Can you past the full query which works in SQL Management studio? and then some sample rows for what you get as a result?

That would help.

[kbender]

SELECT CustomPollerStatusTable.CustomPollerAssignmentID, CustomPollerStatusTable.CompressedRowID, CustomPollerStatusTable.FullLabel FROM CustomPollerStatusTable

where custompollerassignmentid = 'e9XXXX2f-0488-4XX4-9XX7-ca3eXXXXX2bb' and rowid = '20'

But I just found out that it does not return what I thought. If I change the query to:

SELECT * FROM CustomPollerStatusTable

It returns the Rate field. But I cannot get the Rate field from the alert thingy because it defines the top portion of this email.

[njoylif]

you can include that in your WHERE clause even if not returned.

should be able to just add:

AND Rate >=50

[kbender]

How do I add it in the where clause? How do I make an Alert out of it?