Write custom DPA alerts

11/29/2018 10:38 PM

6/23/2020 4:54 PM

Custom Alerts enable a user to execute SQL statements or stored procedures against a monitored database or the database repository itself to check for customized, site specific conditions and to generate an Alert when these conditions are met. Any parameter that can be returned using a SQL statement or stored procedure can be used as the basis for a Custom Alert. This product guide focuses specifically on creating Custom Alerts. The guide will cover Custom Stored Procedures, Alert Values, Custom SQL Statements, and Custom Tags.

Note: You can find examples of custom alerts in THWACK and in the "Example" section of the admin guide topic Configure a DPA Custom alert.

Database Performance Analyzer

Key Features

• Custom Alerts are unique to DPA, giving DBAs unmatched flexibility to trigger Alerts on any condition for which a SQL statement or stored procedure can be constructed.
• Custom alerts are designed to give the DBA the ability to check for conditions outside the normal bounds of pre-defined DPA alerts.
• Alerts can be provided via email. They can also be integrated with enterprise monitoring tools via SNMP.

Benefits Overview

DPA combines the best of three sources of Alert information: Wait-time data, existing database vendor supplied statistics, and custom SQL statements or stored procedures. Designed for any level DBA, they can generate Alerts at multiple severity levels based on numeric ranges, text values or true/false results from the custom code. This gives the DBA advance notice of a condition that affects a specific user community, and the ability to make changes before the users are impacted.

• Custom Alerts give DBAs proactive control of their database, notifying them of issues before they become problems for database customers.
• Custom Alerts allow DBAs to create Alerts on their own PL/SQL queries, giving flexibility to Alert on unique information of interest to the expert DBA.  
• The result is improved customer service, fewer trouble tickets, and increased compliance with database SLAs.

Custom Stored Procedure Alerts

Custom stored procedures can be added to the database that holds the DPA repository. It will be run from that database as the DPA admin user, so the user needs visibility and privileges to execute the custom code.
Once you have written the stored procedure and have added it to the database, use a call to the stored procedure in the Create/Modify Alert screen making sure the Alert Category field has one of the “Custom Procedure Alert” alert types selected.

Required Stored Procedure Parameters

Your custom stored procedure may include any number of parameters, but it must include exactly 2 output parameters (the alert will break if there are more than 2 output parameters), and in exactly this order relative to each other, (i.e. other parameters can be intermixed, but these 2 must be in this relative order):

• AlertValue    OUT VARCHAR2
• AlertString    OUT VARCHAR2

AlertValue is the result of the stored procedure conforming to the Return Type that you specified when creating the alert:

Return Type	Use
'Info', 'Low', 'Medium', 'High'	The return value is case-insensitive, so returning ‘low’ is treated the same as ‘Low’.
Number	Fractional and negative numbers are valid.  Note that the numeric value is returned in the AlertValue of type VARCHAR2.
'TRUE' or 'FALSE'	The value of 'TRUE' (case insensitive) signals that the desired condition has been met, causing the alert level to be set to the level specified on the Create/Modify Alert screen.

AlertString is defined as the result of the stored procedure. The value of this will be sent as the content or body of the alert and can contain anything allowed by the local SMTP or SNMP servers.

Both AlertValue and AlertString are specified using Custom Tags (see discussion below). 

For Oracle databases, a very useful custom tag for stored procedures is #DBLINK#. It is a placeholder for the database link used to query the monitored database. It must be specified in single quotes and passed into the stored procedure as a VARCHAR2 input parameter.

Note: In DPA, database links must be created and maintained by the DBA group.

Here is an example of a stored procedure call using custom parameters:

myproc('param1', #ALERTVALUE#, 100, #ALERTSTRING#, '#DBLINK#')

The corresponding stored procedure definition could look like:

procedure myproc(    myp1            IN     VARCHAR2,
                     alertvalue     OUT     VARCHAR2,
                     myp3            IN     NUMBER,
                     alertstring    OUT     VARCHAR2,
                     dblink          IN     VARCHAR2 )

The stored procedure could then make use of these values. The following is a coding example that passes back a Return Type (i.e., alertvalue) of TRUE or FALSE:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

-- Assume the alert condition is not met
 alertvalue  := 'FALSE';
 alertstring := '';
 
 -- Query based on input values
 EXECUTE IMMEDIATE
      'select x from myschema.mytable@' || dblink
      || ' WHERE y = ' || myp1
      INTO myvar;
 
 -- Test the desired condition
 IF myvar > myp3 THEN
     -- Condition has been met
     alertvalue := 'TRUE';
     alertstring := 'My condition met: value = ' || myvar;
 END IF;

For concrete examples on how to write custom stored procedures, you can look at some of our example alerts in the DPA section of our thwack community forum.

Stored Procedures Errors

Your custom stored procedure may produce expected or unexpected errors. Errors raised from your stored procedure will result in the alert level being set to Broken. The error text will be available on the Alert History screen accessible from the History link on the main Alerts page.

To avoid errors while writing and debugging your stored procedure, do not activate the alert until it works the way you want.  Also, test the alert (via the Test Alert button on the Create/Modify Alert screen) to determine if an error or the desired results are produced.

Custom SQL Statement Alerts

You can create two types of Custom SQL Statement Alerts:

• Single Return Type – the SQL statement must return one row and one column of numeric data.  An example is a query that retrieves the current value of a database parameter.
• Multiple Return Type – the SQL statement can return many rows of data with two paired-value columns of data.  An example is a query that returns the amount of freespace for several tablespaces (Oracle) or databases (SQL Server) and looks similar to the following:
  • APP1        500
  • APP2        760
  • APP3        120
  • APP4           5

Thresholds are tested against each row of data.  For example, if a threshold of 150 were specified, the APP3 and APP4 rows would be flagged and sent in the alert text.

You can also use custom tags in your SQL statements. A common used for Oracle custom alerts is #DBID# which enables querying of DPA-specific wait time performance data from the repository. For example:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

SELECT myvalue FROM myschema.mytable@#DBLINK#

Each monitored database has a unique set of performance data tables defined in the DPA repository.  This tagged value would allow generic SQL Statements to be used for each.

SQL Statement Errors

Errors produced by your SQL statement will result in the alert’s level being set to Broken, just as occurs with Stored Procedure alerts. See the Stored Procedure Errors section above for more details.

Custom Tags

To provide additional capabilities to your custom stored procedures and SQL statements, custom tags have been provided. You can embed custom tags in your statement or procedure call, which are replaced by the DPA alerting engine with the appropriate runtime value. Valid custom tags are shown below.

#DBID#	The internal DPA ID for the monitored database.
Data Type	VARCHAR2(50)
Use	SQL Statement: select col from ignite_table where dbid=#DBID#  Stored Procedure: myproc(…,#DBID#,  …)

 

#DBLINK#	Database link used to connect to an Oracle monitored database.
Data Type	VARCHAR2(50)
Use	SQL Statement: select mycol from                             myschema.mytable@#DBLINK#  Stored Procedure: myproc(…,'#DBLINK#',  …)

 

#ALERTVALUE#	Required output VARCHAR2 parameter for stored procedures (not used in SQL Statements).  On return from the stored procedure, it contains a value corresponding to the Return Type.  Must appear in the parameter list before #ALERTSTRING#.
Data Type	VARCHAR2(500)
Use	myproc(…, #ALERTVALUE#, …)
Comment	If Return Type is set to ‘Number’, the maximum value is defined by the valid range for type NUMBER in Oracle (as defined on the DPA repository database).

 

#ALERTSRTING#	Required output VARCHAR2 parameter for stored procedures (not used in SQL Statements).  On return from the stored procedure, it contains a description of the alert condition.
Data Type	VARCHAR2(4000)
Use	myproc(…, #ALERTSTRING#, …)

 

#FREQUENCY#	Optional input parameter to your SQL statement or stored procedure.  It contains the value, in minutes, for the Run Alert Every field on the Create/Modify Alert screen.
Data Type	NUMBER
Use	SQL Statement: select mycol from                       myschema.mytable@#DBLINK#                   where mydate > SYSDATE –                                        (#FREQUENCY#/1440)  Stored Procedure: myproc(…, #FREQUENCY#, …)