Submit a ticketCall us

WebinarUpcoming Webinar: Know What’s Changed – with NEW Server Configuration Monitor

Change management in IT is critical. But, even with a good change management process, changes are too often not correctly tracked, if at all. The configuration of your servers and applications is a key factor in their performance, availability, and security. Many incidents can be tracked back to an authorized (and sometimes unauthorized) configuration change, whether to a system file, configuration file, or Windows® Registry entry. Join SolarWinds VP of product management Brandon Shopp to discover how the new SolarWinds® Server Configuration Monitor is designed to help you.

Register now.

Home > Success Center > Database Performance Analyzer (DPA) > DPA - Knowledgebase Articles > Custom SQL alerts

Custom SQL alerts

Table of contents

Overview

This article provides information on custom SQL alerts.

Environment

All DPA versions

Detail

  • Custom SQL Alert - Single Numeric Return returns a single number.
  • Custom SQL Alert - Multiple Numeric Return returns a set of name or value pairs.

 

Examples:

An example of a query returning a single number may be a query to alert if the number of failed orders per hour exceeded 10.

Select count(*) from order where failed=y and date=current_time – 1 hr;

However, sometimes more advanced logic is needed such as “if/then” or “while/loop”.  In those cases, they will typically write a function in the native language of the database and create the function manually in the monitored database.  It would be called like this:

Oracle

Select mycustomfunction(parm1) from dual

MSSQL

Select mycustomfunction(parm1)

Sybase

mycustomprocedure(parm1)

DB2

select dbo.mycustomfunction(parm1) from SYSIBM.SYSDUMMY1

Note the Sybase uses a procedure instead of a function.  User defined functions can be written in java in version 12 but are not supported as more standard SQL until 15.0.2.

In some cases you also may want to have the custom SQL return multiple rows.  In that case choose the ‘Custom SQL Alert - Multiple Numeric Return’ alert type.  The SQL may look like this:

Select territory, count(*)   from order where failed=y and date=current_time – 1 hr
 Group by territory;

It would then alert if individual territories exceeded the boundaries.

 

The following are testing information to try out:
 

Sybase stored procedure
create procedure testproc
as select 1234

 

This would be the text you would enter in DPA
testproc

 

MSSQL
CREATE FUNCTION junk
(@Mynum int)
RETURNS int
AS
BEGIN
return @Mynum
end

 

This would be the text you would enter in DPA
select dbo.junk(57)

 

DB2 function
create function JUNK(INSTR int)
returns int
specific JUNK
deterministic no external action contains sql

 

This function just returns the number passed in 
begin atomic
  return INSTR;
end
@

 

This would be the text you enter in DPA
select junk(1234) from SYSIBM.SYSDUMMY1

Last modified

Tags

Classifications

Public