I have a MySQL query that looks up the data in the performance_schema and returns the SQL statements that do not meet my performance criteria. This query returns multiple rows and columns. I want to create a SolarWinds alert that will run this query once a day and, if it returns any rows, email me the result as an alert. If the query returns zero rows for that day, then there is no alert.
What I tried:
On the DPA create alert screen, I saw that I can write a custom MySQL query for the alert as long as the SQL returns a single or multiple numeric columns, or a single Boolean value, etc. The SolarWinds UI does not say but I guess the custom SQL must also return only one row.
But I want an alert based on a custom query that returns multiple rows and multiple columns. So I also tried the DPA create report screen. There, I found no place where I can enter a custom query. (Note: For example, the Top SQL report type has a radio button labeled "User-Defined SQL Statements". But this is the list of SQL statements to monitor. A different thing.)
How can I create a DPA alert or report with a custom MySQL query that returns multiple rows and columns?
I don't believe there is a capability to return multiple rows as part of a custom alert. I believe the reason being that the trigger condition in the alert is expecting to act against one or multiple fields and isn't setup to deal with multiple rows.
The only idea I can think of is that since your trigger condition is to get at least one result back, perhaps you could write your query to concatenate all the rows into a single semi-colon separated list that is returned.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process. Learn more today by joining now.