SQL data source reference

This is a general description of the SQL data source based extension YAML file and ways to declare metrics and dimensions that you would like to collect using your extension.

Extension security

While the Extensions 2.0 framework is secure, the security of your extensions also depends on how you develop them and manage them in your Dynatrace environment.

We recommend the following when developing custom SQL extensions:

Security controls

Data scope

Create an inventory of the data you want to query from your database as the source for your metric and dimension values.

In our example, we create a simple extension collecting basic CPU performance details from an Oracle Database.

name: com.dynatrace.extension.sql-oracle
version: 1.0
minDynatraceVersion: '1.239'
author:
  name: Dynatrace

sqlOracle:
  - group: Number of CPU cores
    featureSet: cpu
    query: >
      SELECT value AS cpu_count
      FROM v$parameter
      WHERE name = 'cpu_count'
    metrics:
      - key: com.dynatrace.extension.sql-oracle.cpu.cores
        value: col:cpu_count
        type: gauge
  - group: Background CPU Usage Per CPU Per Sec
    featureSet: cpu
    query: >
      SELECT
             DECODE(metric_name, 'Background CPU Usage Per Sec',
                 v$metric.value) AS background_cpu_usage,
             DECODE(metric_name, 'CPU Usage Per Sec',
                    v$metric.value) AS foreground_cpu_usage,
             DECODE(metric_name, 'Host CPU Usage Per Sec',
                    v$metric.value) AS host_cpu_usage
      FROM v$metric,
           v$metricgroup
      WHERE v$metric.group_id = v$metricgroup.group_id
        AND v$metric.metric_name IN ('Background CPU Usage Per Sec',
                                     'CPU Usage Per Sec', 'Host CPU Usage Per Sec')
    metrics:
      - key: com.dynatrace.extension.sql-oracle.cpu.backgroundTotal
        value: col:background_cpu_usage
        type: gauge
      - key: com.dynatrace.extension.sql-oracle.cpu.foregroundTotal
        value: col:foreground_cpu_usage
        type: gauge
      - key: com.dynatrace.extension.sql-oracle.cpu.hostTotal
        value: col:host_cpu_usage
        type: gauge

Depending on the provider, your SQL monitoring scope definition starts with a dedicated YAML node. For Oracle Database, it's sqloracle. All the settings under the node pertain to the declared data source type (in this case, SQL).

JDBC connector

Dynatrace Extensions SQL data source enables you to query any database allowing connections using the JDBC driver on top of all the database vendors supported by default. For such databases, some additional steps are required.

Declare the JDBC connection in the extension YAML file

jdbc:
  driverClassName: “org.mariadb.jdbc.Driver”
  connectionStringPattern: “jdbc:mariadb:(. |\\s)+$"
  connectionStringPatternErrorMessage: “This isn't a correct connection string, please start with jdbc:mariadb."
  validationQuery: “SELECT 1”

Users running your extension will also need to upload a related JDBC driver to an ActiveGate belonging to the group designated to run your extension. For more information, see JDBC monitoring configuration.

SQL queries

SQL extensions rely on SQL queries. The queries declared in your extension retrieve the values for your metrics and dimensions.

For example, the following SQL query returns the number of CPU cores.

SELECT value AS cpu_count
FROM v$parameter
WHERE name = 'cpu_count'

You can use it in your extension and report the value returned by a query as the com.dynatrace.extension.sql-oracle.cpu.cores metric in Dynatrace.

sqlOracle:
  - group: Number of CPU cores
    featureSet: cpu
    query:
      SELECT value AS cpu_count
      FROM v$parameter
      WHERE name = 'cpu_count'
    metrics:
      - key: com.dynatrace.extension.sql-oracle.cpu.cores
        value: col:cpu_count
        type: gauge

Query frequency

You can set the frequency at which database provider is queried. If you don't set, the database provider is queried every minute by default.

You can use one of the two exclusive properties to control when the database provider is queried, interval or schedule. You can define it at the group or subgroup level.

Interval

ActiveGate version 1.301+

The interval value accepts an integer value expressing minutes. For example, to query the database provider every 10 minutes, add the following entry:

sqlOracle:
  - group: Number of CPU cores
    featureSet: cpu
    interval:
      minutes: 10
    query:
      SELECT value AS cpu_count
      FROM v$parameter
      WHERE name = 'cpu_count'
    metrics:
      - key: com.dynatrace.extension.sql-oracle.cpu.cores
        value: col:cpu_count
        type: gauge

Schedule

ActiveGate version 1.301+

You can use a cron expression to query your database provider using a schedule of your choice.

The expression must follow the Unix cron format:

# * * * * *
# | | | | |
# | | | | day of the week (0–6) (Sunday to Saturday)
# | | | month (1–12)
# | | day of the month (1–31)
# | hour (0–23)
# minute (0–59)

The values also support lists (1,2,3,4), steps (0-23/2), and ranges (2-5).

The format doesn't support specifying both a day-of-week and a day-of-month value. You must use the ‘?’ character in one of these fields.

For example, to run a query at 12:00 on every week day (Monday-Friday), use the following entry:

sqlOracle:
  - group: Number of CPU cores
    featureSet: cpu
    schedule: "0 12 ? * 1-5"
    query:
      SELECT value AS cpu_count
      FROM v$parameter
      WHERE name = 'cpu_count'
    metrics:
      - key: com.dynatrace.extension.sql-oracle.cpu.cores
        value: col:cpu_count
        type: gauge

Let extension users control frequency

ActiveGate version 1.303+

If you want to let your extension users control the frequency at which a database provider is queried, you can use variables instead of fixed values. Users will be able to define the variable value when activating the extensions.

Make sure you describe your variable context in detail, so that users understand how to set the variable value properly. You can also set the pattern to help users validate their entries.

For example, to let user control the interval:

Dimensions

For each level (extension, group, subgroup), you can define up to 25 dimensions.

Dimension key

The dimension key string must conform to the metrics ingestion protocol.

Dimension value

You use an SQL query to retrieve a value for your dimension (prefix with col:) or use a fixed string (prefix with const:). For example:

query: >
  SELECT event, wait_class
  FROM v$system_event
dimensions:
  - key: event
    value: col:event
  - key: wait_class
    value: col:wait_class
  - key: stage
    value: const:dev

Metrics

For each level (extension, group, subgroup), you can define up to 100 metrics.

For example:

sqlOracle:
  - group: Number of CPU cores
    featureSet: cpu
    query:
      SELECT value AS cpu_count
      FROM v$parameter
      WHERE name = 'cpu_count'
    metrics:
      - key: com.dynatrace.extension.sql-oracle.cpu.cores
        value: col:cpu_count
        type: gauge

Metric key

The metric key string must conform to the metrics ingestion protocol.

Best practices for metric keys

The metrics you ingest into Dynatrace using your extension are just some of the thousands of metrics, built-in and custom, processed by Dynatrace. To make your metrics keys unique and easy to identify in Dynatrace, the best practice is to prefix the metric name with the extension name. This guarantees that the metric key is unique and you can easily appoint a metric to a particular extension in your environment.

Metric value

The column value queried from your database.

Type

The Dynatrace Extensions 2.0 framework supports metric payloads in the gauge (gauge) or count value (count) formats. For details, see Metric payload. To indicate the metric type, use the type attribute.

Metric metadata

An Extension can define metadata for each metric available in Dynatrace. For example, you might want to add the metric display name and the unit, both of which can be used for filtering in the Metrics browser.

name: custom:example-extension-name
version: 1.0.0
minDynatraceVersion: "1.236"
author:
  name: Dynatrace

metrics:
  - key: your.metric.name
    metadata:
        displayName: Display name of the metric visible in Metrics browser
        unit: Count

Feature set

Feature sets are categories into which you organize the data collected by the extension. In this example, we create an Oracle SQL extension collecting metrics related to CPU and Input/Output performance. This is reflected by the metrics organization into related feature sets cpu and io.

sqlOracle:
  - group: Number of CPU cores
    featureSet: cpu
    query:
      SELECT value AS cpu_count
      FROM v$parameter
      WHERE name = 'cpu_count'
    metrics:
      - key: com.dynatrace.extension.sql-oracle.cpu.cores
        value: col:cpu_count
        type: gauge
  - group: Physical read bytes
    featureSet: io
    query: >
      SELECT
             DECODE(name, 'physical read total bytes', value) AS bytes_written,
             DECODE(name, 'physical write total bytes', value) AS bytes_read
      FROM v$sysstat
      WHERE name IN ('physical read total bytes', 'physical write total bytes')
    metrics:
      - key: com.dynatrace.extension.sql-oracle.io.bytesRead
        value: col:bytes_read
        type: count
      - key: com.dynatrace.extension.sql-oracle.io.bytesWritten
        value: col:bytes_written
        type: count

When activating your extension using monitoring configuration, you can limit monitoring to one of the feature sets. To work properly the extension has to collect at least one metric after the activation.

In highly segmented networks, feature sets can reflect the segments of your environment. Then, when you create a monitoring configuration, you can select a feature set and a corresponding ActiveGate group that can connect to this particular segment.

All metrics that aren't categorized into any feature set are considered to be the default and are always reported.

Oracle SQL monitoring configuration

After you define the scope of your configuration, you need to identify the network devices you'd like to collect data from and identify the ActiveGates that will execute the extension and connect to your devices.

The monitoring configuration format depends on the database provider. For more information, see Oracle Database monitoring configuration.