This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom Alert - SQL with specific text executed

Description

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.

Alert Definition

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>.

SQL Statement:

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>

Execute Against: Repository

Units: Seconds of Wait

High Threshold: Min 0, Max empty