Global - database monitoring

Database Monitoring refers to monitoring and analysis of SQL database traffic. In this section you set thresholds for SQL database traffic monitoring and settings for the query auto-learning algorithm.

General

For any given NAM Probe, you can change global settings for all supported database protocols (TDS, DRDA, Informix, and Oracle) so that the settings are inherited by all user-defined software services for these protocols.

  1. Open NAM Console ► Deployment ► Manage devices.
  2. Select NAM Probe Configuration ► Open configuration.
  3. Go to Global ► Database Monitoring ► General to set global settings for database analyzers. The settings are either common for each analyzer or are dedicated to a particular database (which is indicated in brackets beside the option's label).

SQL query time threshold

The number of seconds after which a database query is classified as slow.

Report cursor-based queries as single queries

Select this option to have cursor-based queries reported as separate operations. Otherwise, all cursor operations are grouped and reported under their respective query operations.

Report database name

Enable this option to report the database name, which is used in the NAM Server reports as a module. For more information, see Hierarchy levels for database monitoring. Select the check box beside the protocol name for which you want to report the database name.

User Identification

Select Global ► Database Monitoring ► User Identification and enable the NAM Probe to attempt to extract the database user name for the selected database protocols (TDS, DRDA, Informix, MySQL and Oracle). Next, select the protocols for which to extract the database user name.

Note

Some authentication methods do not allow to extract the database user name or database name, for example NTLM authentication for TDS. Also, if the connection traffic is not available, as in case of long-standing connections, the user name and the database name extraction may be not possible.

User identification

You can enable the NAM Probe to attempt to extract the database user name for all supported database protocols (TDS, DRDA, Informix, and Oracle).

Select Global ► Database Monitoring ► User Identification and enable the NAM Probe to attempt to extract the database user name for the selected database protocols (TDS, DRDA, Informix, MySQL and Oracle). Next, select the protocols for which to extract the database user name.

Query reporting

The query auto-learning feature enables you to define the set of queries appearing in per-query reporting statistics.

The queries found (learned) to fall into at least one of these three categories are reported:

  • the most frequently executed queries;

  • queries with the longest average execution time;

  • queries with the longest overall execution time.

The learning process occurs on the NAM Probe. You can turn this feature off, thus causing all queries to be reported, but normally it is desirable to report queries complying one of the above requirements optionally extended by a set of always reported queries defined manually.

The query auto-learning configuration in the NAM Console consists of the following properties:

High-level Reporting

Simplified reporting based on the autodiscovered software services. In this scenario, operation types are used for both tasks and operation names, which saves the system resources. Detailed operation names are reserved for monitoring based on user-defined software services. The autodiscovery feature detects the following operation types:

Operation Types Informix Oracle TDS DRDA MySQL
Database statement execution cancel X X      
Database login X X X X X
Database logout X X X X X
SQL select X X X X X
SQL select distinct X X X X X
SQL select count X X X X X
SQL create statement X X X X X
SQL execute statement X X X X X
SQL insert X X X X X
SQL update X X X X X
SQL delete X X X X X
SQL alter X X X X X
SQL other statements (such as describe, lock, explain, noaudit, grant, purge, declare, savepoint, rollback, comment, begin, etc) X X X X X
APPS X
SYS X
Database other RPC statements X X X
SYSPROC X
DBXXX X
SYS X
APPS X
Prepared statement definition X X X X
Prepared statement execution X X X X X
Prepared statement destruction X X X X
Database other operations X X X X
Transaction rollback X X
Transaction commit X X
Transaction other operations X
Cursor definition X X X X
Cursor execution X X X X X
Cursor destruction X X X X

Detailed Reporting (All Operations Reported)

Auto-learning disabled and all operations are reported.

Auto-Learn Reporting

Size of reported URLs pool.

The limit of reported URLs, default value: 500

Advanced settings

  • Percentage of new URLs (default: 25%)
    Percentage of reported URLs pool that is freed at the beginning of each interval and is reserved for new highly active URLs. This property controls how fast the mechanism adapts to changes.
  • Size of candidates pool (default: 4)
    Size of the additional pool of tracked URLs (candidates to become monitored URLs). It is defined as multiplier of monitored URLs pool. Their popularity over time is observed and URLs which reach specified thresholds become monitored.
  • Page loads thresholds (default: 25%)
    Threshold, defined as percentage of monitored URLs, which tracked URL has to surpass in popularity in order to be promoted to monitored pool.
  • Slow pages weight (default: 1.00)
    Controls whether slow loads (pages that exceeded thresholds) should have the same impact on the mechanism as normal pages. Default factor is 1, which means the same as normal loads.
  • Cleanup interval (default: 1440)
    Interval at which member list cleanup and rehash is done. Default is 24h represented in minutes.
  • Use host names (default: checked)
    If set to unchecked, the hostname is disregarded and only paths take part in auto learning process.

Tuning guidelines

If you want a small and stable number of the most active queries, you need a large set of potential candidates. Use the following settings as guidelines to obtain this goal:

Number of queries tracked in addition to the reported queries

Set to a high value (for example, 500) to give you a large set of potential candidates.

Number of executions a query has to reach before being learned

Set to a high value (for example, a few thousand) to define a threshold that only a few queries will attain.

Limit of queries that can be learned per server

Set to a low value.

If you want to explore queries (to get a large number of queries) and if you do not mind if they disappear after some time, adjust the parameters the opposite way:

Number of queries tracked in addition to the reported queries

Set to a low value.

Number of executions a query has to reach before being learned

Set to a low value.

Limit of queries that can be learned per server

Set to a high value.

Query aggregation

SQL query normalization means, for example, truncation of repeating patterns.

Query normalization can be useful if you want to diminish the number of similar queries logged. If, for example, the queries differ only in the value of parameters, they can be truncated at a defined keyword and then only the unique queries will be processed and reported. You can choose an alternative way of gaining uniform, unique queries: mask literal values with a special symbol.

You can configure the normalization methods globally, at the software service level or per individual static query, with the query level having the highest priority and the global setting, the lowest. To configure normalization methods, first open configuration settings for a NAM Probe in the NAM Console, and then choose how you want them to be configured:

  • Globally

    Select Global ► Database Monitoring ► Query Aggregation.

  • At the software service level

    In the Rule Configuration window, click the Query Aggregation tab;

  • At the query level

    In the Query Monitoring tab, add or edit a query to open the Monitored Query dialog and choose Regular expression as Query type.

SQL query personal data masks

Literal values (text values) in SQL queries may be pieces of information that you do not want to be visible at any stage of database monitoring (for example, passwords or credit card numbers). If you know that such strings are part of your monitored database traffic, configure literal masking on the NAM Probe using the NAM Console. The table SQL query personal data masks must be filled with strings that will be used to mask literals in SQL queries.

Such a string is a SQL query (or a significant fragment of a query) that unambiguously defines literals that have to be masked. The literal to be masked is represented by the “#” symbol, which is an obligatory element in the defined mask, while the “%” symbol stands for an ordinary literal. Note that a backslash in this format has a special meaning and must be escaped: to indicate a backslash, a double backslash (“\”) must be used.

When literal masking is defined, all the matching classified data will be removed and will never be processed by the NAM Probe.

Adding the following mask:

 WHERE user=\% AND password=\#

will result in masking the password literal, but will report user . Adding the following mask:

 INSERT INTO users (id, name, cardnumber) VALUES (\%, \%, \#)

will result in reporting users' ID and name but will mask their card numbers.

SQLQuery normalization method

The panel SQL query normalization method enables you to choose:

Queries will be cut before keyword “set”, “values”, “where”, “(”

Cutting means that queries will be truncated before a specific keyword: where, set, values, or “(”. If the resulting string is longer than 1024 bytes and the Log SQL Statements option is turned on, it will be further truncated so it does not exceed the default limit.

All query literals will be masked using the “?” character

All literal values will be replaced by the “?” symbol. This normalization method is useful for aggregation of queries - types of queries are grouped regardless of the literals they contain.

Optionally, you can exclude certain literals from masking by adding them to SQL query literals to exclude from masking table. As in sensitive literals masking, you can use “%” symbol as an obligatory element, and optionally “#” symbol

Figure 2. Excluding Literals from Masking

Adding following the pattern to the SQL query literals to exclude from masking table

 user=\%

will result in masking all the literals except user .

Note that, although only unique, normalized queries will be reported, the whole query with unmasked literals will still be recorded if the option Log Full Queries was set in the database monitoring global settings.

Availability

In the Availability section you set options for availability failures reporting.

This section describes availability for TDS, DRDA, Oracle, MySql, Informix, and SAP HANA DB.

To view information about database failures in DMI reports, access the Availability tab and configure the appearance of a specific failure type in a DMI report .

You can configure database availability globally or at the software service level.

For global configuration, open the NAM Probe configuration and go to Global ► Database Monitoring ► Availability ► . For the software service level, select the Availability tab in the Edit Rule window.

Transport failures

No response

Disabled by default.

Aborted response

Enabled by default.

Application failures

Database errors

The number of database errors. Enabled by default.

Database warnings

The number of database warnings. Enabled by default.

Note

For DRDA, MySQL, Oracle, and Informix, any detected database errors are grouped together with warnings. As a result, the aggregated error and warning count for these database types appear in the Database Warnings column in a DMI report.

For TDS databases, however, errors are differentiated from warnings.

Fault domain isolation

In the FDI section you set options for fault domain isolation reporting.

This section describes fault domain isolation for TDS, DRDA, Oracle, MySql, Informix, and SAP HANA DB.

Use the following threshold settings to accurately identify the true source of the problem:

Server time threshold
The Server time threshold relates to the server time portion of an overall operation time. Server times above the threshold limit are considered to be slow due to poor datacenter performance.

Idle time threshold
Threshold for the time during the operation execution when there is no network or server activity related to the operation. It is assumed that Idle time is caused by the user's software not sending requests because user's PC is busy.

Network time threshold
Threshold for the time the network (between the user and the server) takes to deliver requests to the server and to deliver page information back to the user. In other words, Network time is the portion of transaction time that is due to the delivery time on the network.

Retransmissions threshold
Percentage of retransmissions regarding all observed transmissions.

Network time affected by high retransmission threshold
Percentage of the network time affected by high retransmission threshold.

Request size threshold Threshold for the request where anything larger would be considered a big request.

Network time affected by the transfer of a big request threshold
Threshold for the request where anything larger would be considered a big request.

Response size threshold
Threshold for the response where anything larger would be considered a big response.

Network time affected by the transfer of a big response threshold Threshold for the network time that is affected by the transfer of a big response threshold.

Number of hits threshold
Threshold for the number subcomponents of error-free operations or transactions.

Single hit duration threshold
Threshold for a hit duration as a percentage of operation time.

Rtt threshold
Threshold for the time it takes for a SYN packet to travel from the client to a monitored server and back again.