This alert will watch for SQL statements that execute and contain a specific string. For example, if you think a specific table is no longer being used and you want DPA to let you know if it sees any SQL with that table name in it, you can use this alert.
To create this alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert similar to below.
Note: When DPA executes this alert, it will substitute the proper data for the keywords #DBID# (database ID internal to DPA) and #FREQUENCY# (alert execution interval).
Alert Name: SQL Containing <your string>
Execution Interval: 10 MInutes
Notification Text: The following list of SQLs contained the string <your string>.
SELECT 'SQL Name: ' +ISNULL(n.NAME, sw.IZHO)+' - SQL Text: '+SUBSTRING(st.ST, 1, 40) AS SQLNameText, SUM(QP)/100 AS timesecs
FROM ignite.CONSW_#DBID# sw
INNER JOIN ignite.CONST_#DBID# st ON st.H = sw.IZHO AND st.P = 0
LEFT OUTER JOIN ignite.CON_SQL_NAME n ON n.HASH = sw.IZHO
WHERE sw.D >= CURRENT_TIMESTAMP - (#FREQUENCY#/60.0/24.0)
AND st.ST LIKE '%<Your String>%'
GROUP BY st.ST, ISNULL(n.NAME, sw.IZHO)
Note: the only customization needed for this SQL statement is the "AND st.ST LIKE '%<Your String>%'" line, 2nd from bottom. Put the text string you want in place of <Your String>
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.