CockroachDB

Overview Copied

The CockroachDB Collection Agent plugin monitors CockroachDB clusters by polling two data sources on each collection cycle:

• HTTP Status and Admin API (/_status/nodes, /_admin/v1/cluster) — The collector calls CockroachDB’s Status and Admin JSON API over HTTP (or HTTPS on a secure cluster) and publishes each node’s liveness status and an allowlist of per-node and per-store metrics. The allowlist is aligned with CockroachDB’s Essential Metrics for CockroachDB Advanced Deployments and Essential Alerts for CockroachDB Self-Hosted Deployments.
• SQL side-channel (JDBC against crdb_internal.*) — Over a separate PostgreSQL wire-protocol (JDBC) connection, the collector queries CockroachDB’s internal system views and publishes open-session counts and nonterminal jobs.

Every data point includes a cluster dimension set to the CockroachDB cluster UUID. The plugin fetches this UUID once from /_admin/v1/cluster at startup. If that call fails, the collector fails to start.

The HTTP and JDBC clients verify the server with a shared CA certificate and authenticate with a username and password. For TLS setup and credential requirements, see Security and TLS. For the minimum system privileges that the SQL user needs, see Collector user and privileges.

Prerequisites Copied

Before you deploy the plugin, make sure that:

• A CockroachDB cluster is reachable from the Collection Agent host over the HTTP Status and Admin API port and the SQL (PostgreSQL wire protocol) port.
• You have network access to the HTTP endpoint (default 8080) and the SQL port (default 26257).
• For a secure cluster, you have the PEM CA certificate that signed the CockroachDB node certificates.
• You have a dedicated SQL user with password authentication (LOGIN enabled) and the privileges described in Collector user and privileges.

Geneos environment Copied

The latest version of the CockroachDB plugin requires:

• Gateway and Netprobe 7.11.x or higher.

• Collection Agent 6.6.x or higher.

  Note

  Starting from Collection Agent 5.x, Java 21 is the minimum required version.

The CockroachDB plugin binaries are packaged with Netprobe and are stored in the collection_agent > plugins folder. Alternatively, you can download separate binaries for the CockroachDB plugin from ITRS Downloads.

Collector user and privileges Copied

Create a dedicated SQL user for the plugin with password authentication and LOGIN enabled. For more information, see CockroachDB’s Manage Cluster User Authorization guide, CREATE USER for user creation, and GRANT for granting system privileges.

The plugin needs two system-level privileges. Membership in the admin role is not required:

Example (run as a user who can grant system privileges, for example root):

CREATE USER c2plugin WITH PASSWORD 'c2plugin_pw';
GRANT SYSTEM VIEWCLUSTERMETADATA TO c2plugin;
GRANT SYSTEM VIEWACTIVITY TO c2plugin;

Use the same user for both http.username / http.password and db.connectionConfig.username / db.connectionConfig.password.

Security and TLS Copied

TLS is controlled by the top-level tls key on the CockroachDB plugin configuration.

When tls is present, both the HTTP client and the JDBC driver use tls.caFile for server-certificate verification. Omit the entire tls key for an insecure cluster.

Client-certificate authentication isn’t supported. Authenticate with a username and password on both clients:

• HTTP — CockroachDB’s Status and Admin JSON API accepts session cookies obtained from POST /api/v2/login/. Client certificates aren’t mapped to an identity.
• JDBC — pgjdbc’s sslkey property requires a PKCS#8 DER private key, while cockroach cert create-client emits PEM. To avoid requiring that conversion, the plugin requires db.connectionConfig.username and db.connectionConfig.password for JDBC as well.

Whenever http.endpointUrl uses https://, both http.username / http.password and db.connectionConfig.username / db.connectionConfig.password are required.

The following table summarizes the settings for each cluster mode:

Configure Geneos to deploy the CockroachDB plugin Copied

The plugin supports Collection Agent publication into Geneos using Dynamic Managed Entities. To set up the plugin in Geneos:

1. Set up the CockroachDB Collection Agent plugin.
2. Configure your mappings.
3. Configure other Dynamic Entities in the Gateway.

Set up the CockroachDB Collection Agent plugin Copied

Use one of the following options to configure the plugin:

• Set up your collector in the Gateway Setup Editor by adding the configuration in Dynamic Entities > Collectors. For more information, see Collectors in Dynamic Entities.
• Add the configuration to the collection-agent.yml file on your local machine.

collectors:
  - type: plugin
    className: CockroachDbCollector

    # How often (in milliseconds) to run a collection cycle. Optional, defaults to 60000.
    collectionInterval: 30000

    # Base URL of CockroachDB's Status / Admin JSON API. Required. The collector appends
    # /_status/nodes and /_admin/v1/cluster to this prefix.
    # Use https:// against a secure cluster; the top-level tls.caFile is then required.
    http:
      endpointUrl: https://localhost:8080

      # Required whenever http.endpointUrl is https://. The plugin exchanges these for a session
      # cookie via POST /api/v2/login/ on first use and after any 401 response.
      username: c2plugin
      password: c2plugin_pw

    # Optional. Server-certificate verification for BOTH the HTTP client and the JDBC
    # driver. Omit the whole block for an insecure cluster.
    #   - HTTP: OkHttp is built with an SSLSocketFactory that trusts caFile only.
    #   - JDBC: pgjdbc is configured with sslmode=verify-full and sslrootcert=caFile.
    # No client-cert options are exposed; both clients authenticate via username/password
    # (see http.* and db.connection-config.* below).
    tls:
      caFile: /etc/c2/certs/ca.crt          # PEM CA that signed the CockroachDB node cert

    # JDBC side-channel settings.
    db:
      # JDBC connection credentials. Required.
      connectionConfig:
        # Full JDBC URL. Required. Any URL-level options are preserved.
        url: jdbc:postgresql://localhost:26257/defaultdb

        # Optional. Defaults to "root".
        # In insecure clusters (env/docker-compose.yml) leave this at root; in secure
        # clusters (env/secure/) create a dedicated user with the privileges above.
        username: c2plugin

        # Required against a secure cluster.
        password: c2plugin_pw

Note

HTTP connect and read timeouts (5 seconds connect, 10 seconds read) and the JDBC connect timeout (5 seconds) are internal defaults and aren’t currently configurable.

For descriptions of all options, see Configuration reference.

Configure your mappings Copied

You can configure your dynamic mappings using a built-in or custom mapping in Dynamic Entities > Mapping:

• To use a built-in mapping, set Option to Built in, then select CockroachDB V1 in the Built in > Name field.
• To use a custom mapping, set Option to Custom.

For more information, see Mapping and mapping group in Dynamic Entities.

Configure other Dynamic Entities in the Gateway Copied

For a more detailed procedure, see Create Dynamic Entities.

Metrics collected Copied

Each collection cycle publishes data points from the HTTP Status and Admin API and from the SQL side-channel. SQL data-point names match their source column names. Dimensions identify which source table a given data point came from.

Each SQL query runs against its own short-lived JDBC connection and is handled independently, so a failure in one query doesn’t suppress the other SQL metrics or the HTTP metrics.

HTTP Status and Admin API Copied

The allowlist is split into a node-level set and a per-store set based on CockroachDB’s Essential Metrics and Essential Alerts documentation. Metrics that aren’t on the allowlist aren’t published.

SQL side-channel Copied

Sessions Copied

One bucket is published per (user_name, application_name) pair observed in crdb_internal.cluster_sessions. Per-session rows aren’t emitted — session_id is regenerated on every reconnect and isn’t a stable dimension.

Jobs Copied

One set of data points is published per row in crdb_internal.jobs whose status is nonterminal: failed, running, paused, pause-requested, cancel-requested, reverting, retry-running. No job_type filter is applied.

Note

The JDBC client sets timezone=UTC on each connection so that job timestamps aren’t shifted by the Collection Agent JVM’s local time zone offset.

Custom SQL querying Copied

The CockroachDB Collection Agent plugin publishes a fixed set of cluster telemetry from the HTTP Status and Admin API and from selected crdb_internal views. It doesn’t run arbitrary customer SQL.

To run your own SQL against CockroachDB and surface the results in Geneos, use the JDBC Collection Agent plugin. That plugin is separate from the CockroachDB collector. You can run both on the same Collection Agent — CockroachDB for built-in telemetry, and JDBC for custom queries.

CockroachDB speaks the PostgreSQL wire protocol, so configure the JDBC plugin with a PostgreSQL-style URL. For example:

jdbc:postgresql://<host>:26257/<database>

The JDBC plugin packages the PostgreSQL driver, so no extra driver JAR file is required for CockroachDB.

With the JDBC plugin, you can:

• Define one or more SQL queries and how each result column maps to Geneos data points (for example, Gauge, Counter, StatusMetric, or EntityAttribute).
• Publish results as Collection Agent data points for Dynamic Entities (not native Netprobe dataviews).
• Use the generic JDBC Dynamic Entity mapping, typically with dimensions such as database, table, and row_key.

For full configuration, data point translation, and mapping examples, see JDBC.

The following example shows a minimal JDBC collector that runs a custom query against CockroachDB:

collectors:
  - name: jdbc-cockroachdb
    type: plugin
    className: JdbcCollector
    collectionInterval: 60000
    workerThreads: 1

    connection:
      url: jdbc:postgresql://localhost:26257/defaultdb
      user: <sql_user>
      password: <sql_password>

    queryConfigs:
      - name: custom-query
        query: "SELECT id AS row_key, status FROM my_app.orders WHERE status != 'closed'"
        dimensions:
          dbColumns:
            - row_key
          static:
            database: defaultdb
            table: orders
        dataPoints:
          - column: status
            type: StatusMetric

Map the collector with the generic JDBC Dynamic Entity mapping so dimensions such as database, table, and row_key resolve correctly.

Note

Configure TLS on the JDBC collector (URL or propertiesFile). Don’t use the CockroachDB plugin tls block for JDBC connections.

Configuration reference Copied

Collection settings Copied

HTTP settings Copied

Configured under http.

TLS settings Copied

Configured under tls. When present, both the HTTP client and the JDBC driver verify the server certificate by using caFile. Omit the entire block for an insecure cluster.

Database connection settings Copied

Configured under db.connectionConfig.