JDBC Active Discovery

Product Documentation

The JDBC Active Discovery method is used to detect whether databases are running on certain ports, and to filter them based on responses to SQL queries. It can also be used to create instances based on arbitrary SQL queries, that can be used to collect more detail about objects.

Parameters

Once you set the Active Discovery method to JDBC, you’ll need to configure the following parameters:

JDBC URL: This field can be filled with either the type of database engine or a fully defined URL. If you only specify the type of database engine to connect to (i.e. mysql, oracle, postgresql or mssql), LogicMonitor will attempt to build the JDBC URL using defined device properties. You can specify a fully defined URL in one of the following formats:

Using tokens, as in these examples:
- jdbc:postgresql://##HOSTNAME##:5432/##DBNAME##?user=##jdbc.postgres.user##&password=##jdbc.postgres.pass##
- jdbc:sybase:Tds:##hostname##:##jdbc.sybase.port##/##dbname##?user=##jdbc.sybase.user##&password=##jdbc.sybase.pass##
  - Please note: SyBase URLs do not require slashes before the hostname.
Or fully declaring the string replacing the italicized variables with your database-specific information, as in the following examples:
- jdbc:mysql://hostname:port?user=username&password=password
- jdbc:sqlserver://hostname:port;user=username;password=password
- jdbc:oracle:thin:username/password@//hostname:port
- jdbc:postgresql://hostname:port/database?user=username&password=password
Note that if you are using a MySQL database, ensure the password does not contain the backslash (\) character.

SQL Statement: A SQL query supported by the database engine that will be used to discover instances. For example, you might execute the following query: select * from information_schema.tables; or show slave status for mysql.
Discovery Type: There are four discovery types: Attributes in columns | Attributes in rows | database list | instance list

Attributes in columns: The SQL statement must return only one row, of multiple columns. The column name is used as the attribute name, and the entry of the first row for that column is the attribute value. These attributes will not be discovered as instances, but rather are only for filtering purposes. If the attributes and values meet the filter criteria, one instance will be discovered for each port.
Attributes in rows: The SQL statement must return two columns, with varying numbers of rows. The first column is used as attribute name, and the second as the attribute value. These attributes will not be discovered as instances, but rather are only for filtering purposes. If the attributes and values meet the filter criteria, one instance will be discovered for each port.
Database list: This option is intended to query a global database about other databases to be monitored. It expects the SQL statement to return a list of databases, which are concatenated with the port used. This value can then be used in the JDBC URL. Each listed database will be returned as an instance.
Instance list: The result of the SQL statement will be used as instances. The query must return only a single column, of varying numbers of rows (e.g. “select name from customers”).

Oracle SID: The SID to use for connecting to Oracle databases.
Port # List: A comma separated list of ports that Active Discovery should check. Any database services discovered on these ports will be returned as instances according to the discovery type specified.

If the Port # List parameter is not defined, then the port number will be retrieved from the device property

jdbc.xxxx.port

(where xxxx is mysql, mssql etc). If this device property is also not defined, then ActiveDiscovery will use these default ports:

3306	/ for MYSQL. The corresponding property is jdbc.mysql.port
1433, 1434	/ for MSSQL. The corresponding property is jdbc.mssql.port
1521, 1526	/ for ORACLE. The corresponding property is jdbc.oracle.port
5432	/ for POSTGRE. The corresponding property is jdbc.postgres.port

Note: If you have not used a default port for your database instance, you must declare the port in this field.

Separator: This field is only valid if the Discovery Type is set to database list. The separator specified here is used to create instance names by concatenating the port number and the database names in the following manner: portNumber separator databaseName.

Attributes in Rows

Using the Attributes in Rows discovery type, database services discovered on ports in the port list will be returned as instances if the SQL statement can be executed against them. If filters are defined, only those instances that pass the filters are discovered as instances.

When using this discovery method, the Active Discovery wildvalue returned is the port number.

Assume for the following examples that this database is being discovered on port 3306:

mysql> desc robin_test;
Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL	auto_increment
datasource_id	int(11)	NO		NULL
name	varchar(64)	NO		NULL
value	varchar(16384)	NO		NULL
comment	varchar(1024)	NO		NULL

mysql> select * from robin_test;
id	datasource_id	name	value	comment
1	1	test-1	test-1	comment-1
2	2	test-2	test-2	comment-2

Example 1

If the JDBC Active Discovery parameters are configured as follows:

The SQL statement will result in a two column table:

name	id
test-1	1
test-2	2

One instance will be discovered with a wildvalue of 3306.

Example 2

Assume that there is one filter defined, and that the Active Discovery parameters are specified as follows:

The SQL statement will result in a two column table:

name	id
test-1	1
test-2	2

Since the first attribute meets the filter criteria, one instance is returned with wildvalue 3306.

Example 3

Assume that there is one filter defined, and that the Active Discovery parameters are specified as follows:

The SQL statement will result in a two column table:

name	id
test-1	1
test-2	2

Since the attributes returned do not meet the filter criteria, no instances will be returned.

Attributes in Columns

Using the Attributes in Columns discovery type, a database service running on a port in the port list will be discovered as an instance if the SQL statement can be executed against it. If filters are defined, only those instances that meet the filter criteria are discovered as instances.

When using the Attributes in Columns discovery type, the Active Discovery wildvalue returned is the port number.

Example 1

Continuing with the previous example database, assume that there are no filters, and that the Active Discovery parameters are specified as follows:

The SQL statement will result in a one column table:

One instance will be discovered with a wildvalue of 3306.

Example 2

Assume that there is one filter, and that the Active Discovery parameters are specified as follows:

The SQL statement will result in a one column table:

Since the attribute meets the filter criteria, one instance is discovered with a wildvalue of 3306.

Example 3

Assume that there is one filter defined, and that the Active Discovery parameters are specified as follows:

The SQL statement will result in a one column table:

Since the attribute doesn’t meet the filter, no instances are discovered.

Database List

When using the database list discovery type, the Active Discovery wildvalue is:

port separator SQLstatementRowResult

Example 4

Assume that there are two tables in our database:

+---------------------+
| Tables_in_test      |
+---------------------+
| robin_test          |
+---------------------+
| robin_test_1        |
+---------------------+

And you have configured the following Active Discovery parameters:

The following tables are discovered as instances, and they are named as follows:

3306:robin_test
 3306:robin_test_1

Instance List

When using the instance list discovery type, the Active Discovery wildvalue is the result of the SQL statement.

This method, like the database list method, creates instances using information returned from the SQL query and the query must return a single column table with each row value equaling an instance.

Example 5

Consider the following SQL query:

mysql> select * from robin_test;
id	datasource_id	name	value	comment
1	1	test-1	test-1	comment-1
2	2	test-2	test-2	comment-2

And the following Active Discovery configuration:

The instances discovered would be 1 and 2.

Example 2

The instance list discovery type can be used to find objects within a database that can then be further queried by the datasource.

This returns an instance for each entry in the customers table, on each host the datasource is applied to, that responds to the query on the default mysql port of 3306.

The datasource can then use this instance in the main JDBC collector, either in the connection string (if each customer has their own database), or in the query:

select count(orders) from customers where customers.name='##WILDVALUE##'

Platform

Solutions

By Initiative

By Industry

About us

Get to know us

Services

Resources

Learn

Documentation

Support

Platform

Solutions

Resources

About us

Documentation

Product Documentation

JDBC Active Discovery

Parameters

Attributes in Rows

Example 1

Example 2

Example 3

Attributes in Columns

Example 1

Example 2

Example 3

Database List

Example 4

Instance List

Example 5

Example 2

In This Article