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).

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

Interval

The database provider is queried at a fixed interval of one minute. The interval for the SQL data source is not configurable.

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.