The JDBC ActiveDiscovery 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.

JDBC URL: this is not generally a true url, but rather the type of database engine to connect to: mysql, oracle, postgresql or mssql. However, you can fully specify the url: e.g. jdbc:postgresql://##HOSTNAME##:5432/##DBNAME##?user=##jdbc.postgres.user##&password=##jdbc.postgres.pass##

SQL statement: the sql statement (or statement supported by the engine, such as "show slave status" as an example for mysql.)

Discovery Type: Attributes in columns |database-list| Attributes in rows | instance list

"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 value, for filtering purposes.
"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 is the value for filtering purposes.
"Instance List": the result of the sql query will be used as instances. The query must return only a single column, or varying numbers of rows (e.g. "select name from customers"). Filtering does not apply to this method - instead, filtering should be done in the SQL query.
"database-list" is intended to query a global database about other databases to be monitored. It expects the query to return a list of databases, which are concatenated with the port used. This value can then be used in the connection URL.

Oracle SID: the SID to use for connecting to Oracle databases.

Port # List: a list of ports to attempt a connect on, and then to try to execute the statement.

Usage

Discovering Databases

For "Attributes in rows" and "Attributes in Columns", a port in the port list will be discovered as an instance if the SQL statement is able to be executed against it. If filters are defined, only those instances that pass the filters are discovered as instances.

e.g. the datasource whose parameters are displayed above would execute "show slave status", using the mysql engine, for all hosts that respond to TCP port 3306.

The output would be interpreted as multiple columns.

The filter would be applied to check that a column existed with the name Master_host - this would ensure that this datasource is only applied to databases with valid Mysql masters.

Discovering Objects within Databases

"Instance List" discovery is used to find objects within a database, that can then be further queried by the datasource.

e.g.

This would return 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##"