Database SQL Alert

I had a customer ask if they could get alerted from DPA when any SQL statement exceeded X seconds in a 10 minute timeframe.

This is the alert (Custom SQL Alert - Multiple Numeric Return type) we ended up with and here is the SQL statement used:

SELECT 'DB: ' + db.name + ' - SQL: ' + convert(varchar(20), sw.izho), sum(sw.qp/100) timesecs
FROM ignite.consw_#DBID# sw
INNER JOIN ignite.cono_#DBID# db on db.id = sw.ixoy
WHERE sw.d >= datediff(mm, -#FREQUENCY#, current_timestamp)
GROUP BY db.name, sw.izho

Notes:

• The column named IXOY within the CONSW table (one for each monitored instance) represents the SQL Server database id
• It joins to the CONO table for the name of the database
• The #DBID# value will be substituted with the ID value from COND when the alert is executed
• The #FREQUENCY# value will also be substituted for with the execution interval value, e.g. 10 minutes in the example
• Configure the alert to execute against the repository