EMS Database Monitor

The EMS Database Monitor allows you to collect event and time series data from database tables used by Enterprise Management Systems (EMS) by performing a query through a JDBC connection. The data retrieved is then processed and sent to Topaz.

Each time the Database Monitor runs, it returns the monitor’s status, the time it took to perform the query, the number of rows in the query result set, and the first two fields in the first row of the result and writes them in the monitoring log file.

Usage Guidelines

What to monitor

Use the EMS Database Monitor in order to integrate database records into Topaz. Examples of data that can be integrated into topaz using the EMS Database monitors:

Events from monitoring applications event tables or views
Open Tickets from ticketing systems applications
Time series from monitoring applications measurement tables

What is being sent to Topaz

The EMS Database monitor keeps count of the database rows (using the table of the user defined primary key) it already sent to Topaz. A row will only be sent to Topaz if it changed. This can be in one of the following cases:

The row was returned for the first time by the current update of the monitor.
The row was already returned by a prior update but the value of one of the selected columns changed
The row was returned by a prior update but not by the current update of the monitor.

Use the configuration file in order to control the data that is sent to topaz. Refer to the EMS Configuration file format for more details on the file structure and syntax.

Note: when referring to data arriving from the EMS Database Monitor in the config file, use the column name prefixed by the dollar sign (‘$’).

For example, for the query:

SELECT height,width FROM some_table WHERE width > 0

You can refer to the columns returned as $height and $width

Setup Requirements

The steps for setting up a EMS Database Monitor will vary according to what database software you are trying to query. The following is an overview of the requirements for using the Database Monitor:

You must install or copy a compatible database driver or database access API into the appropriate SiteScope directory location. Many database driver packages are available as compressed (zipped) archive files or .jar files. Database drivers in this form must NOT be extracted and must be installed into the <SiteScope install path>/SiteScope/java/lib/ext subdirectory. If you extract drivers from their compressed files (not recommended), the individual driver files must be placed in the <SiteScope install path>/SiteScope/classes subdirectory instead of the java directory.

You need to know the syntax for accessing the database driver. Examples of database driver path strings are:

sun.jdbc.odbc.JdbcOdbcDriver - (JDBC-ODBC Bridge Driver from Sun Microsystems)
com.inet.tds.TdsDriver - (TDS driver from i-net Software for Microsoft SQL databases)
oracle.jdbc.driver.OracleDriver - (JDBC thin driver for Oracle 7 and 8 databases)

You need to know the syntax for the Database Connection URL. The Database Connection URL normally includes the class of driver you are using, some key name relating to the supplier of the driver software, followed by a combination of server, host, and port identifiers. Examples of database connection URL's are:

jdbc:odbc:dsname - (where dsname is the data source name in the system environment or configuration)
jdbc:inetdae:hostname:port - (where hostname is the name of the host where the database is running and port is the port on which the database interfaces with the driver)
jdbc:oracle:thin:@hostname:port:dbname - (where hostname is the name of the host where the database is running, port is the port on which the database interfaces with the driver, and dbname is the name of the Oracle database instance)

The database you want to query must be running, have a database name defined, and have at least one named table created in the database. In some cases, the database management software needs to be configured to allow connections via the middleware or database driver.

You need a valid username and password to access and perform a query on the database. In some cases, the machine and user account that SiteScope is running on must be given permissions to access the database.

You need to know a valid SQL query string for the database instance and database table(s) in the database you want to extract data from. Consult your database administrator to work out appropriate queries to use.

Completing the EMS Database Monitor Form

To display the EMS Database Monitor Form, either click the Edit link for an existing Database Monitor in a monitor table, or click the Add a new Monitor to this Group link on a group's detail page and click the Add Database Monitor link.

Complete the items on the Database Monitor form as follows. When the required items are complete, click the Add Monitor button.

Database Connection URL

Enter a URL to a Database Connection. The easiest way to create a database connection is to use ODBC to create a named connection to a database. For example, first use the ODBC control panel to create a Data Source Name (DSN) called test. Then, enter jdbc:odbc:test in this box as the connection URL.

Query

Enter the SQL query to test. For example, select * from sysobjects.

Database Driver

Enter the java class name of the JDBC database driver. The default, sun.jdbc.odbc.JdbcOdbcDriver, uses ODBC to make Database connections. SiteScope uses the same database driver for both primary and backup database connections. If a custom driver is used, the driver must also be installed in the SiteScope/java directory.

Database Username

Enter the username used to login to the database. If you are using Microsoft SQL server, you can leave this blank and choose NT Authentication when you setup the ODBC connection. With NT Authentication, SiteScope will connect using the login account of the SiteScope service.

Database Password

Enter a password used to login to the database. If you are using Microsoft SQL server, you can leave this blank and choose NT Authentication when you create the ODBC connection. With NT Authentication, SiteScope will connect using the login account of the SiteScope service.

Update every

Select how often the monitor should check the Database server. The default interval is to run or update the monitor once every 10 minutes. Use the drop-down list to the right of the text box to specify another update interval in increments of seconds, minutes, hours, or days. The update interval must be 15 seconds or longer.

Title

Enter a title text for this monitor. This text is displayed in the group detail page, in report titles, and other places in the SiteScope interface. If you do not enter a title text, SiteScope will create a title based on the host, server, or URL being monitored.

Advanced Options

The Advanced Options section presents a number of ways to customize monitor behavior and display. Use this section to customize error and warning thresholds, disable the monitor, set monitor-to-monitor dependencies, customize display options, and enter other monitor specific settings required for special infrastructure environments. The options for this monitor type are described below. Complete the entries as needed and click the Add or Update button to save the settings.

Disable

Check this box to temporarily disable this monitor and any associated alerts. To enable the monitor again, clear the box.

EMS Configuration File Path

Enter the path to the EMS integration configuration file. For more information about format of the file see EMS Configuration file format. The default location is: SiteScope\ems\JDBC\main.config.

Primary Keys

Enter a semicolon delimited subset of fields selected from the database that can act as a unique identifier for a row. This is an optional parameter and is needed only if the queried table does not define a primary key. An example a case where this field should be used is when trying to retrieve data from a view.

Note: when this field is used you must include all the columns used as the primary key in the SELECT clause. Failing to do so results in an error.

Enable Topaz Synchronization

Check this checkbox if you wish the monitor to resend database snapshots at the intervals selected below. Enabling Topaz synchronization, along with the ‘Topaz Synchronization Interval’ allows the monitor to sometimes send a complete snapshot of the database in stead of the usual ‘delta’ mode.

Topaz Synchronization Interval

Sets the time interval in which the EMS Database monitor sends a snapshot image of the returned rows.

File Path

The EMS Database Monitor can read the SQL query from a file. Enter the name of the file that contains the query you want to run. The file should be a simple text format. Use this feature as an alternative to the Query entry above for complex queries or queries that change and are updated by an external application.

Connection Timeout

Enter a timeout value, in seconds, that the monitor should wait for a database connection.
Note: The sum of the Connection Timeout value and Query Timeout value should always be less than the Update every value for the monitor.

Query Timeout

Enter a timeout value, in seconds, that the monitor should wait for a database query to return results.
Note: The sum of the Connection Timeout value and Query Timeout value should always be less than the Update every value for the monitor.

Note: Some commonly used databases and database drivers do not support the query timeout feature. In these cases the Query Timeout value should be set to zero.

Update Every (on error)

You use this option to set a new monitoring interval for monitors that have registered an error condition. For example, you may want SiteScope to monitor this item every 10 minutes normally, but as often as every 2 minutes if an error has been detected. Note that this increased scheduling will also affect the number of alerts generated by this monitor.

Schedule

By default, SiteScope monitors are enabled every day of the week. You may, however, schedule your monitors to run only on certain days or on a fixed schedule. Click the Edit schedule link to create or edit a monitor schedule. For more information about working with monitor schedules, see the section on Schedule Preferences for Monitoring.

Monitor Description

Enter any additional information to describe this monitor. The Monitor Description can include HTML tags such as the <BR> <HR>, and <B> tags to control display format and style. The description will appear on the Monitor Detail page.

Report Description

Enter an optional description for this monitor that will make it easier to understand what the monitor does. For example, network traffic or main server response time. This description will be displayed on with each bar chart and graph in Management Reports and appended to the tool-tip displayed when you pass the mouse cursor over the status icon for this monitor on the monitor detail page.

Depends On

To make the running of this monitor dependent on the status of another monitor or monitor group, use the drop-down list to select the monitor on which this monitor is dependent. Select None to remove any dependency.

Depends Condition

If you choose to make the running of this monitor dependent on the status of another monitor, select the status condition that the other monitor or monitor group should have in order for the current monitor to run normally. The current monitor will be run normally as long as the monitor on which it depends reports the condition selected in this option.

List Order

By default, new monitors are listed last on the Monitor Detail page. You may use the drop-down list to choose a different placement for this monitor.

Error if

Set the conditions under which the EMS Database Monitor should report an error status. Use the drop-down list to select a criteria based on the content of the results or a performance value. Next select the logic operator(s) for the error criteria. Then enter the value to be used as the threshold or trigger for this condition. These include:

Status – error if status does not equal 200
Rows – number of rows returned from database
Result column 1,2 – test the values returned in the columns
Round trip time – time it takes to execute the query

Warning if

Set the conditions under which the EMS Database Monitor should report a warning status. Use the steps outlined in the Error if section above.

Good if

Set the conditions under which the EMS Database Monitor should report a status of "good". Use the steps outlined in the Error if section above.

Accessing Oracle Databases Without Using ODBC

For more information about accessing the oracle Database without using ODBC see the Database monitor documentation.

Accessing Informix Database

For more information about accessing the informix Database using the Informix JDBC driver see the Database monitor documentation.

Accessing MySQL Database

For more information about accessing the MySQL Database using the MySQL JDBC driver see the Database monitor documentation.

Accessing Sybase Database

For more information about accessing the Sybase Database using the Sybase JDBC driver see the Database monitor documentation.