JDBC
Overview
The JDBC (Java Database Connectivity) Collection Agent plugin uses JDBC to gather data from a database by executing user-specified queries.
JDBC drivers
The following JDBC drivers are packaged with the JDBC plugin:
- MariaDB: mariadb-java-client
- PostgreSQL: postgresql
- Oracle: ojdbc11
- MSSQL (SQL Server): mssql-jdbc
A JDBC plugin configured to connect to the databases above will work out of the box.
For databases not listed above, add their corresponding JDBC driver jar file to the classpath when running the collection agent.
- For example, when using a Sybase JDBC driver when running a collection agent as a standalone process, you need to add
/opt/sybase/jConnect-16_0/classes/jconn4.jar
in the classpath as shown below.
java -cp /opt/sybase/jConnect-16_0/classes/jconn4.jar:<Netprobe directory>/collection_agent/collection-agent-3.2.2-exec.jar "-Dlogback.configurationFile=<Netprobe directory>/collection_agent/logback.xml" com.itrsgroup.collection.ca.Main "collection-agent-jdbc-sybase.yml"
- For collection agent started by a Netprobe, see JVM args in Managed Collection Agent.
Plugin configuration reference
collectors:
- name: jdbc
type: plugin
class-name: JdbcCollector
# Interval (in millis) between query execution (optional, defaults to 1 minute)
collection-interval: 60000
# Number of worker threads (i.e. concurrent query executors).
# Default value shown.
worker-threads: 1
# Connection details
connection:
# Mandatory. The JDBC Url which identifies the database to connect to
url: jdbc:mysql://localhost:5005/geneos_db
# Username used to connect to the database
user: <database user>
# Password of the said username
password: <database password>
# Properties file which contains connection properties. For debugging purposes/advanced users only.
properties-file: <filename of properties file>
# Array of query configurations. Multiple queries can be specified.
query-configs:
# Mandatory. Query to run
- query: "show processlist"
# Mandatory. Unique identifier of this query. This will be used in logs and signal events.
name: "Process List"
# Mandatory. Dimensions added to each datapoint. At least one dimension is required from either db-columns or static key-value pairs.
# Dimensions common to all queries defined can be configured under the common-query-config section.
dimensions:
# List of DB columns to use as datapoint dimensions.
db-columns:
- Id
- User
- Host
- db
# Map of static key-value pairs that will be added as a dimension to each datapoint.
static:
queryAlias: "Process List"
# List of DB columns that will be added as datapoints
data-points:
# Column Name
- column: Command
# Datapoint type. Possible values: StatusMetric, Gauge, Counter, EntityAttribute
type: EntityAttribute
- column: Time
type: Gauge
# unit parameter is only applicable to gauge.
# Possible values are as described in Unit class.
unit: seconds
- column: State
type: StatusMetric
- column: Info
type: EntityAttribute
# Columns that are not specified in the dimensions or datapoints config will be treated as a datapoint.
# If the SQL data is of numeric SQL type, it will be treated as a Gauge by default.
# If it is a Date, Time, or Timestamp type, it will be treated as an EntityAttribute.
# Otherwise, it will be treated as a StatusMetric.
# Common query configuration that will be applied to all queries defined in query-configs.
# These configurations can also be placed inside a specific query configuration.
# The query-specific configuration overrides the common config below 2 configs are the same.
common-query-config:
# Query timeout in seconds. Default is no timeout.
timeout: 5
# The timezone offset used to process TIMESTAMP (without time zone) data. Default is "+00:00" or UTC.
timestamp-zone-offset: "+08:00"
dimensions:
db-columns:
- ID
static:
urlAlias: "LocalHost:5005 geneos_db"
data-points:
- column: comment
type: EntityAttribute
Translation of database result set to datapoints
You must specify how to translate the data returned by the query into datapoints in each query-configuration section.
For example, the query in the example configuration above returns the result set below:
mysql> show processlist;
+----+-------------+-----------------+-----------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+-----------+---------+------+----------+------------------+
| 7 | geneos_user | localhost:51558 | geneos_db | Query | 0 | starting | show processlist |
| 8 | geneos_user | localhost:54952 | geneos_db | Sleep | 3 | | NULL |
+----+-------------+-----------------+-----------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
The configuration above specifies the following:
- The
Id
,User
,Host
, anddb
columns are dimensions. - The
queryAlias
andurlAlias
are static dimensions, which will be added to each datapoint. - The columns to be used as datapoints, and their corresponding data type. For example,
the
Command
column is an EntityAttribute and theTime
column is a Gauge with unit inseconds
.
With the configuration above, the first datapoint in the first row will be the Command
column,
which you must define as an EntityAttribute datapoint type.
The datapoint will then contain the following:
- name =
Command
- value =
Query
- dimensions:
- Id =
7
- User =
geneos_user
- Host =
localhost:51558
- db =
geneos_db
- urlAlias =
LocalHost:5005 geneos_db
- queryAlias =
Process List
- Id =
Handling of invalid data
Dimension values should never be null. If the value of any of the dimensions is null, the datapoints in that row will be ignored. If the value of a datapoint column is null, that datapoint will be ignored.
Timestamp processing
SQL has two timestamp types:
TIMESTAMP
- no timezone. The timestamp-zone-offset configuration set in the query configuration will be used to convert the timestamp into UTC. If no timestamp-zone-offset configuration is set, the data is treated as date-time in UTC.TIMESTAMP WITH TIME ZONE
By default, timestamp data types are published as Entity Attribute. However, it is also possible to specify it as a gauge or counter. In such cases, the unit will automatically be set to EPOCH_MILLISECONDS
.
Properties file
The connection: properties-file:
configuration can be used to specify a file containing database-specific configurations.
To specify database-specific properties, define one property per line in the file with the following format:
`dataSource.<property name>=<property value>`
For example, to add TLS support for MySQL, the following MySQL-specific properties should be added to the connection: properties-file:
configuration:
dataSource.sslMode=VERIFY_IDENTITY
dataSource.trustCertificateKeyStoreUrl=file:///home/mysql/certs/catruststore
dataSource.trustCertificateKeyStorePassword=mypassword
dataSource.clientCertificateKeyStoreUrl=file:///home/mysql/certs/keystore
dataSource.clientCertificateKeyStoreType=PKCS12
dataSource.clientCertificateKeyStorePassword=mypassword
Other database-specific JDBC drivers may also have their own set of properties.
Example configuration
The following collector configuration runs the show processlist
query, which returns a list of all the current processes running on the server.
connection:
url: jdbc:mariadb://ab1-23-45-678-910.ap-southeast-2.compute.amazonaws.com:1111/geneos_db
user: admin
password: password
query-configs:
- query: "show processlist"
name: "Process List"
dimensions:
db-columns:
- Id
static:
table: "processes"
database: "Geneos DB"
data-points:
- column: User
type: EntityAttribute
- column: Host
type: EntityAttribute
- column: db
type: EntityAttribute
- column: Command
type: EntityAttribute
- column: Time
type: Gauge
unit: seconds
- column: State
type: StatusMetric
- column: Info
type: EntityAttribute
This query returns a result set like the following:
mysql> show processlist;
+---------+------+------------------------+------------+----------+-------+-----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+------+------------------------+------------+----------+-------+-----------+------------------+
| 811536 | root | 119.92.192.242:51110 | geneos_db | Query | 0 | starting | show processlist |
+---------+------+------------------------+------------+----------+-------+-----------+------------------+
1 row in set (0.00 sec)
The Id
column is defined as a dimension in the example configuration above because it is the unique identifier for each process in the result set. The configuration also defines static dimensions for the table="processes"
and database="Geneos DB"
, which will be added as dimensions to the datapoints. The rest of the data are then treated as datapoints.
As an example, let’s consider the User
column. An EntityAttribute type of a datapoint will be created for this column based on the configuration. This data point will have the following fields:
- name = User
- value = root
- dimensions
- Id = 811536
- table = processes
- database = “Geneos DB”
In order to create a Dynamic Entity dataview for this configuration, it is necessary to set up a Dynamic Entity mapping as follows:
JDBC Dynamic Entity dataview:
You can also check for errors in the collector by viewing the Netprobe Info
> Dynamic Entities Health
> Signals
self-monitoring dataview. For more information, see Signals in Dynamic Entities Health.