BMC Defender SQL Table Monitor adapter

This section provides a detailed description of the BMC Defender SQL Table Monitor adapter. It is an optional set of files and executables added to the BMC Defender Server to expand the role of the BMC Defender to include event monitoring of changes or additions to one or more relational database tables.

The adapter executes user-defined SQL queries, reads and processes the results of these queries, and converts these results into syslog messages that are used by BMC Defender Server.

This space also provides information on specific features and capabilities of this special software, including installation procedures, operating theory, application notes, and certain features not documented elsewhere.

The BMC Defender SQL Table Monitor consists of several components. A background process continuously executes a series of SQL statements at periodic intervals. The BMC Defender SQL Table Monitor compares the results of these statements to match patterns. It sends messages to the BMC Defender Server when patterns match. Additionally, you can inspect specific SQL responses.

This space is intended for BMC Defender users who operate the system as well as for system administrators responsible for installing the software components. This information might also be of interest to program developers and administrators who want to extend the range of the BMC Defender to include monitoring of log messages stored in SQL tables.

The BMC Defender SQL Table Monitor extends the BMC Defender system to enable periodic checking of data within a relational database. It allows BMC Defender to actively monitor event data (and other data) residing in a database, to check and alert when changes to this data occur selectively. The SQL Table Monitor works with any ODBC enabled database including MS-SQL, Oracle, IBM DB2, and others. 

The BMC Defender SQL background process periodically executes SQL statements on ODBC data sources, compares SQL responses to threshold values (in the form of match patterns), and then sends syslog messages (of user-specified severity and content) to the main BMC Defender Server when patterns match. It gives BMC Defender more awareness of the network and enterprise state by monitoring the data that might reside in ODBC compliant databases.

The BMC Defender SQL background process is configured and monitored using a tightly coupled integration with the main BMC Defender web interface. You can configure simple or complex SQL statements and define the message and severity that is sent to BMC Defender when responses match.

Components

The BMC Defender SQL Table Monitor comes as a single downloadable package in a self-extracting WinZip format. This package is installed at the BMC Defender Server, and contains the following specific components:

• CO-sqlmon.exe program—This is the polling agent that is responsible for gathering SQL information on the system. The process is configured to run on BMC Defender system startup (using the System > Schedule screen, as documented).

• SQL Table Monitor configuration screen—This is a support screen, available under the Messages > Adapters > SQLMon tab of the BMC Defender web interface as part of the Windows component installation. This screen allows the user to configure the SQL queries to be performed, as well as configure match patterns and messages that are sent when events are detected. 

These two items co-operate to allow full monitoring of events that are stored in SQL databases, including different databases residing on remote platforms. The CO-SQLMon.exe program runs as a persistent background process, and the SQL Table Monitor Configuration screen allows the operator to configure this process and the queries it executes.

Additional notes

• The CO-SQLMon.exe program polls a database using a query composed on the AddNew screen. For any query results that match the specified pattern, a syslog message issues to BMC Defender containing the matched row.
• Three different types of monitoring is possible, selected when you create the SQL Monitor entry. You can specify a monitor type Update, Change, or Tail.
• You can use the Update and Change monitor types to determine whether a change has occurred in a database result set. The expected result set is small (typically 100 lines or less) and is less than the Max Results setting of the monitor.

• You can use the Tail monitor type to tail records of a possibly large table and report any new records that are added to the table. This monitor type requires an index to the table that contains an increasing value such as a timestamp or record number.

• You can use the Advanced screen to adjust certain parameters such as the Poll Cycle Wait time. This parameter affects the rate where configured queries are executed on the system, and affects the loading of the database.

• Any changes to the Advanced screen restart all monitoring, discarding any pending data that might exist, and reset the message times, error counts, and other items of each SQL monitor. The process resumes exactly as it restarts.

• You can determine the poll time and response time for the CO-SQLMon.exe program by drilling down into the BMC Defender SQL Table Monitor named hyperlink, that shows the current response time values for all devices during the last poll cycle.
• When configuring a BMC Defender alert, ensure that the Alert Interval is greater than the Poll Interval value to prevent multiple tickets from being opened for a single incident. Additionally, ensure that the Auto-Learn function for the alert is disabled.

System diagram

The BMC Defender SQL Table Monitor process consists of a single background process that is executed at the BMC Defender Server. This process reads configuration data that has been configured by the operator and continuously executes a list of SQL statements at a particular poll rate (by default each 30 seconds). These SQL statements are applied against ODBC compatible databases. The databases can be local to the BMC Defender Server or reside on other platforms on the network. 

When each SQL statement is executed, the results of the statement are fetched and compared to match patterns and previously queried results. The SQL Table Monitor treats each row of the results as a single event record (that is, a single line) and compares to match patterns configured by the operator. If the match pattern is satisfied, the matched record sends to BMC Defender as an event message with a severity specified by the operator. 

Once the Main BMC Defender Server receives the message, it treats it like any other syslog message. The server can further correlate the message using threads, alerts, triggers, and tickets.

The following diagram illustrates the CO-SQLMon.exe process (installed and configured as described in the next topics) continuously sends SQL statements to one or more ODBC database. The databases can be local or remote, and you can have a single database in the enterprise or many different databases, including Access, Oracle, MS-SQL, and other database systems.

The polling process is completely controlled and monitored by data that is configured by the operator using the Messages > Adapters > SQLMon screen of the main BMC Defender Server web interface.

Constraints

The BMC Defender SQL Table Monitor is designed to allow a large amount of flexibility. However, there are some constraints to its operation that you must consider and they are as follows:

• The BMC Defender SQL Table Monitor requires an ODBC interface.

• The BMC Defender SQL Table Monitor is limited to a maximum number of results per polling cycle (by default 100 records per 30 seconds). Any data returned from a database query preceding this value can be lost. 

  Note

  This limit applies only to the results, and not necessarily the number of records scanned or queried. This value is adjustable to a maximum of 1000 results for each SQL query.

• The BMC Defender SQL Table Monitor tracks data only while the BMC Defender Server is running and has no provision for obtaining records that are logged if the BMC Defender Server reboots or temporarily shuts down.

This section provides information about the following topics:

• Types of SQL monitors
• Installing BMC Defender SQL Table Monitor adapter
• Configuring BMC Defender SQL Table Monitor Adapter
• Using BMC Defender SQL Table Monitor Adapter