Documentation forDatabase Performance Analyzer

Configure a DPA Custom alert

Use Custom alerts to execute SQL statements or stored procedures against the monitored database or DPA repository to check for conditions not covered by other DPA alerts. Each SQL statement or procedure returns a number (or set of numbers) that can trigger an alert depending on user-defined thresholds. Custom alerts can be used to alert against a wide variety of conditions. Any parameter that can be returned to DPA using a SQL statement or stored procedure can be used as the basis for a custom alert.

For information about other DPA alert categories, see DPA alerts.

To create a custom alert, see the following sections:

Other examples of custom alerts can be found on THWACK.

Custom alert types and expected return values

Depending on what type of custom alert you select, the SQL statement or stored procedure must return one of the following values.

Alert type Expected return values

Single Numeric Return

The SQL statement or stored procedure returns a single numeric value. The alert is triggered if the value exceeds the defined High, Medium, Low, and Info thresholds.
Multiple Numeric Return

(SQL statements only.) The SQL statement returns one or more rows of data. Each row contains a string in the first column and a numeric value in the second column. For example, the query could return database names and the amount of free space for each one:

DB1   120
DB2   840
DB2    35

The alert is triggered if any value exceeds the defined High, Medium, Low, and Info thresholds.

Single Boolean Return

The SQL statement or stored procedure returns a string value of TRUE or FALSE (not case-sensitive). The alert is triggered if TRUE is returned.
Single Alert Status Return The SQL statement or stored procedure returns a string value that specifies the alert status. Valid values are NORMAL, INFO, LOW, MEDIUM, and HIGH (not case-sensitive).

Requirements for stored procedures

When you create a custom alert that calls a stored procedure, the stored procedure must include two output parameters. These output parameters must be in the following order relative to each other, and no other output parameters can be included:

  1. AlertValue OUT VARCHAR2

    The value of this parameter must be one of the expected return values for the selected alert type. (For example, if the alert type is Custom Procedure Alert - Single Boolean Return, this output parameter must be TRUE or FALSE.)

    Use the custom tag #ALERTVALUE# to include this output parameter.

  2. AlertString OUT VARCHAR2

    The value of this parameter is a description of the result of the stored procedure.

    Use the custom tag #ALERTSTRING# to include this output parameter.

The stored procedure can include any number of input parameters. The input parameters can be interspersed with the output parameters, as long as the output parameters are in the correct order relative to each other. For example:

myproc('inputParam1', #ALERTVALUE#, 'inputParam2', #ALERTSTRING#, '#DBLINK#')

Create or edit a Custom alert

  1. From the DPA main menu, click Alerts.
  2. Click the Manager Alerts tab.
  3. Do one of the following:
    • To create a new alert, select Custom as the alert category, select the alert type, and then click Create Alert.

      To find out more about each alert type, select it to display a description on the right.

    • To edit an existing alert, click the alert name.
  4. In the Alert Information section:

    1. Enter a unique name.

    2. If you want to disable the alert, clear the Active check box.
    3. Select the execution interval. (DPA recommends an execution interval of at least 10 minutes.)
    4. Enter the notification text to be sent with the email notification. Include an explanation of the issue and the suggested resolution.

      If you apply a custom email template to this alert, the email notification includes the notification text if the email template contains one of the following variables:

      • Alert Notification text: [=alert.notificationText]
      • Results: [=dpa.body] (included by default)
  5. To run the SQL statement or stored procedure against monitored database instances (instead of the DPA repository), select the database instances.

  6. In the Alert Parameters section:

    1. Enter the SQL statements to execute, or enter a call to a stored procedure.

      Use custom tags to include variables such as the database ID and to include the required output parameters for stored procedures.

    2. In the Execute Against drop-down, indicate if the SQL statement or stored procedure should be executed against the selected database instances or against the DPA repository database.
    3. If the Description field is available, you can enter a custom description for the alert. This description replaces the DPA default description for the alert type when the Description parameter is included in the email template.
    4. If the alert returns a numeric value, specify the Units for the returned value.
  7. If the alert returns a numeric value, specify the thresholds for each alert level you want to enable.

    • Leave the Max value for the highest level blank to alert on anything above the minimum value for that level.

    • If you configure multiple levels, the Max value for lower levels must equal the Min value for the next higher level.

    • When you enter a Max value for a level, DPA alerts at that level when the value is greater than or equal to the Min value but less than the Max level. For example, if the Min value is 5 and the Max value is 10, DPA will alert at that level when the value is 5 or when the value is 9.99, but not when the value is 10.

  8. Select the person or group who gets notified when each alert level is triggered and when the alert is broken. (The alert status is set to Broken if an error occurs during execution.)

    To send notifications when the alert returns to Normal, select a recipient for Normal.

    • If you have not added the person or group as a contact in DPA, click Add Contact or Add Contact Group and create the contact or group.
    • Select an SNMP contact to send SNMP traps when the alert is triggered. Select an SNMP contact for Normal to send a clearing notification when the alert status returns to Normal.
  9. Verify or change the notification policy.

    To send notifications when the alert returns to Normal, the notification policy must be Notify when level changes.

  10. Select the email template that defines the contents of the email notifications sent by this alert.
  11. Click Email Preview to see an example of the email that will be generated using the selected email template and contact information.

    If the alert applies to multiple database instances, select an instance in the Email Preview dialog box and click OK. After reviewing the email, you can select a different database instance or click Cancel to close the Email Preview dialog box.

    The email sent to users might not exactly match the preview because some alert parameters cannot be evaluated during a preview.

  12. Click Test Alert to test the alert and view the current alert level. The test does not generate an email.
  13. Click Save.

Custom tags

You can include the following custom tags in your SQL statements or stored procedure calls. DPA replaces these tags at runtime with the appropriate values.

Tag Description
#DBID#

The internal DPA ID for the monitored database instance.

  • Data type: VARCHAR2(50)

  • SQL statement usage example:

    select mycol from mytable where dbid=#DBID#

  • Stored procedure usage example: 

    myproc(..., #DBID#, ...)

#DBLINK#

A database link used to connect to an Oracle monitored database.

  • Data type: VARCHAR2(50)

  • SQL statement usage example:

    select mycol from myschema.mytable@#DBLINK#

  • Stored procedure usage example: 

    myproc(..., '#DBLINK#', ...)

#ALERTVALUE#

(Stored procedures only.) The first required output parameter for stored procedures. It returns one of the expected values based on the alert type. It must appear in the parameter list before #ALERTSTRING#.

  • Data type: VARCHAR2(500)

  • Stored procedure usage example: 

    myproc(..., #ALERTVALUE#, ..., #ALERTSTRING#)

#ALERTSTRING#

(Stored procedures only.) The second required output parameter for stored procedures. It returns a description of the alert condition.

  • Data type: VARCHAR2(4000)

  • Stored procedure usage example: 

    myproc(..., #ALERTVALUE#, ..., #ALERTSTRING#)

#FREQUENCY#

The execution interval for the alert, in minutes.

  • Data type: NUMBER

  • SQL statement usage example:

    select mycol from myschema.mytable@#DBLINK#
    where mydate > SYSDATE -
    (#FREQUENCY#/1440)

  • Stored procedure usage example: 

    myproc(..., #FREQUENCY#, ...)

Example: Create a custom DPA alert to display the name of the active node in a SQL Server cluster

This example shows how to configure two alerts that work together to cause DPA to display the physical machine name of the active node in a SQL Server failover cluster. By default, DPA shows only the cluster name.

  • The first alert gets the name of the active node.
  • The second alert appends this name to the name of the cluster that DPA displays. If the active node changes, it updates the name and also notifies recipients that a failover has occurred.

Task 1: Create an alert to get the physical machine name

This alert runs against a monitored database instance and retrieves the name of the physical machine that the instance is currently running on.

  1. On the DPA menu, click Alerts.
  2. Click the Manage Alerts tab.
  3. Choose Custom as the category and Custom SQL Alert - Multiple Numeric Return as the type.

  4. Click Create Alert.
  5. Enter a unique name and select the execution interval.

    Set the execution interval based on the average frequency of failovers in your clustered environment. The execution frequency affects the accuracy of the machine names that DPA displays. In this example, the alert interval is 10 minutes. But if failovers occur infrequently, you might want to choose a longer interval.

  6. In the Notification Text box, provide a description of the alert.

    This alert does not send notifications, but the description provides information for other users.

  7. Under Database Instances, select the SQL Server cluster.

  8. Enter the following SQL in the SQL Statement box:

    select coalesce(SERVERPROPERTY('ComputerNamePhysicalNetBIOS'), SERVERPROPERTY('MachineName')) HOST, 0

  9. Verify that Monitored Database is selected from the Execute Against drop-down menu.
  10. Enter Machine Name 0 in the Units box.

  11. Under Configure Alert Levels and Recipients:

    1. Enter 1 as the Min value for the High level.

      The query returns a numeric value of 0. Entering 1 as the threshold ensures that the status is always Normal. Because this alert only retrieves the machine name, it should not be triggered.

    2. Do not select a contact because no one needs to receive notifications.

  12. Click Save.

Task 2: Create an alert to append the machine name to the cluster name

This alert runs against the DPA Repository database. It appends ' Node: @nodeName' to the database instance name that DPA displays. Each time it runs, it determines whether the node name has changed. If so, it updates the display name and sends a notification so that you can investigate why the failover occurred.

This alert can also determine why the name has changed with different error levels. The error levels are: 0=no change, 1=node change, 2=initial update.

  1. From the Manage Alerts tab, choose Custom as the category and Custom SQL Alert - Multiple Numeric Return as the type.

  2. Click Create Alert.
  3. Enter a unique name and select the execution interval.

    This execution interval must be larger than the interval for the Get Physical Machine Name alert. The execution intervals for both alerts affect the accuracy of the name that DPA displays. In this example, the execution intervals are 10 minutes and 12 minutes, but you should determine what intervals are appropriate for your environment.

  4. Enter the email notification text.

  5. Under Database Instances, select the SQL Server cluster.

    You must select the SQL Server cluster for both alerts. If you do not select it for this alert, the instance name is not updated in DPA. If you do not select it for the Get Physical Machine Name alert, that alert does not run and has a status of Broken.

  6. Enter the following SQL in the SQL Statement box:

    declare 
    @mach_name varchar(100),
    @current_name varchar(100),
    @update_flag smallint
    
    begin 
    select @mach_name=c.name 
    from con_alert_db a, con_alert b, con_alert_db_results c
    where b.id = a.alertid
    and b.alertname = 'Get Physical Machine Name'
    and a.alertid = c.ALERTID
    and a.DBID = c.DBID
    and a.DBID = #DBID#
    
    if @mach_name is not null
      select @update_flag = CHARINDEX('  Node',NAME)  
      from COND 
      where ID = #DBID#
    
    if @update_flag != 0
      begin
      select @current_name = substring(NAME, CHARINDEX('  Node',NAME) +7, 100)
      from cond
      where ID = #DBID#    
    
      if ltrim(rtrim(@current_name)) != ltrim(rtrim(@mach_name))
        begin
          update COND 
          set NAME = substring(NAME, 1,CHARINDEX('  Node',NAME)-1 )
          where ID = #DBID#
           
          update COND
          set NAME = NAME + '  Node: ' + @mach_name
          where ID = #DBID#
           
          select @mach_name,1
        end
      end
    else
      begin
        update COND
        set NAME = NAME + '  Node: ' + @mach_name
        where ID = #DBID#
         
        select @mach_name,2
      end;
    select @mach_name, 0
    end;			
  7. Select Repository from the Execute Against drop-down menu.
  8. Enter :Node|Error Level in the Units box.

  9. Under Configure Alert Levels and Recipients:

    1. Enter 1 as the Min value for the High level.

      When the name of the active node changes, this alert is triggered at the High level.

    2. Select a contact to receive the email when this alert is triggered.

      If you have not added the person or group as a contact in DPA, click Add Contact or Add Contact Group. See Create contacts and contact groups.

  10. Click Save.