Submit a ticketCall us

Don’t fall victim to a ransomware attack
Backups are helpful, but sometimes that’s not enough to protect your business against ransomware. At our live webcast we will discuss how to protect against ransomware attacks with SolarWinds® Patch Manager and how to leverage log data to detect ransomware. Register now for our live webcast.

Home > Success Center > Database Performance Analyzer (DPA) > Exclude data from DPA collection

Exclude data from DPA collection

Created by Interspire Import, last modified by MindTouch on Jun 23, 2016

Views: 59 Votes: 0 Revisions: 9

 

This article applies to all versions of DPA and Ignite 7.0 and later.

 

When monitoring Oracle, SQL Server, or DB2, DPA can exclude from monitoring specific SQL statements coming from a certain program, user, host, or a combination thereof. For example, DPA can exclude a SQL statement when run by a specific user or exclude all SQL statements coming from a user on a certain computer. This is often used to exclude long running SQL statements such as backups, data loads, and so on.

Warning: Adding too much logic to the QUICKPOLL_WHERE_CLAUSE can cause the query to run longer than intended or disrupt statistic gathering. This is true if you are trying to adjust the clause for when the DPA database repository is on a monitored instance. Contact support if you require more guidance on this option. 

 

Note: All SQL statements below are against the database repository. 

 

Exclude by SQL only

See this article on exluding a SQL statement from DPA data collection.

Note: DPA only supports exclusion by a DPA hash value for Sybase. Other exclusion criteria are not supported.

Exclude by other criteria

You can exclude by the executing program, user, host, and so on:

  1. In DPA, click Options > Administration.
  2. Under Configuration, click Advanced Options.
  3. Next to Option Filters, select the Support Options check box.
  4. Select the database instance:
    • For DPA 9.0 and later: Click the DB Instance Options tab, and select a database from the instance list.

    • For Ignite 8.0 and earlier: Scroll down to the monitored database section, and select a database from the instance list.

  5. Click the QUICKPOLL_WHERE_CLAUSE option.

  6. Enter a phrase based on your database instance type, and click Update.

  7. Restart the DPA monitor.

SQL Server monitored instance syntax 

Example 1: Exclude all SQL from the TSQL program logging in from the server HPSERVER.

 and not (s.program_name='TSQL' and s.hostname='HPSERVER')

 

Example 2: Exclude a SQL statement if it comes from a certain user, but do not exclude it if coming from other users. In this case you must find the SQL_handle of the SQL statement. You can exclude using the DPA SQL hash by following the directions in the previous section.

and not (s.loginame='Bob' and s.sql_handle=0x00987097097897)

 

Example 3: Exclude all SQL statements executed in the master database by the dataload program.

and not (db_name(s.dbid)='master' and s.program_name='dataload')

Oracle monitored instance syntax

Example 1: Exclude all SQL statements from the SAP.exe program logging in from the server HPSERVER.

and not ("u".ksusepnm ='SAP.exe' and "u".ksusemnm='HPSERVER')

 

Example 2: Exclude a SQL statement if it comes from a certain user, but do not exclude it if coming from other users.

and not ("u".ksuudlna='Bob' and "u".ksusesqh =97097897)

DB2 monitored instance syntax

Example 1: Exclude all SQL statements from the SAP.exe program logging in from the server HPSERVER.

and not (ai.appl_name ='SAP.exe' and ai.client_nname='HPSERVER')

 

Example 2: Exclude a dynamic SQL statement if it comes from a certain user, but do not exclude it if coming from other users.

and not (ai.auth_id='Bob' and s. stmt_text  like '%insert into bad_table%')

 

Example 3: Exclude a static SQL statement if it comes from a certain user, but do not exclude it if coming from other users.

and not (ai.auth_id='Bob' and st. text  like '%insert into bad_table%')

Last modified
19:14, 22 Jun 2016

Tags

Classifications

Public