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, and db columns are dimensions.
The queryAlias and urlAlias 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 the Time column is a Gauge with unit in seconds.

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

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.

Previous article Next article