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.

1name: com.dynatrace.extension.sql-oracle
2version: 1.0
3minDynatraceVersion: '1.239'
4author:
5  name: Dynatrace
6
7sqlOracle:
8  - group: Number of CPU cores
9    featureSet: cpu
10    query: >
11      SELECT value AS cpu_count
12      FROM v$parameter
13      WHERE name = 'cpu_count'
14    metrics:
15      - key: com.dynatrace.extension.sql-oracle.cpu.cores
16        value: col:cpu_count
17        type: gauge
18  - group: Background CPU Usage Per CPU Per Sec
19    featureSet: cpu
20    query: >
21      SELECT
22             DECODE(metric_name, 'Background CPU Usage Per Sec',
23                 v$metric.value) AS background_cpu_usage,
24             DECODE(metric_name, 'CPU Usage Per Sec',
25                    v$metric.value) AS foreground_cpu_usage,
26             DECODE(metric_name, 'Host CPU Usage Per Sec',
27                    v$metric.value) AS host_cpu_usage
28      FROM v$metric,
29           v$metricgroup
30      WHERE v$metric.group_id = v$metricgroup.group_id
31        AND v$metric.metric_name IN ('Background CPU Usage Per Sec',
32                                     'CPU Usage Per Sec', 'Host CPU Usage Per Sec')
33    metrics:
34      - key: com.dynatrace.extension.sql-oracle.cpu.backgroundTotal
35        value: col:background_cpu_usage
36        type: gauge
37      - key: com.dynatrace.extension.sql-oracle.cpu.foregroundTotal
38        value: col:foreground_cpu_usage
39        type: gauge
40      - key: com.dynatrace.extension.sql-oracle.cpu.hostTotal
41        value: col:host_cpu_usage
42        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.

1SELECT value AS cpu_count
2FROM v$parameter
3WHERE 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.

1sqlOracle:
2  - group: Number of CPU cores
3    featureSet: cpu
4    query:
5      SELECT value AS cpu_count
6      FROM v$parameter
7      WHERE name = 'cpu_count'
8    metrics:
9      - key: com.dynatrace.extension.sql-oracle.cpu.cores
10        value: col:cpu_count
11        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:

1query: >
2  SELECT event, wait_class
3  FROM v$system_event
4dimensions:
5  - key: event
6    value: col:event
7  - key: wait_class
8    value: col:wait_class
9  - key: stage
10    value: const:dev

Metrics

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

For example:

1sqlOracle:
2  - group: Number of CPU cores
3    featureSet: cpu
4    query:
5      SELECT value AS cpu_count
6      FROM v$parameter
7      WHERE name = 'cpu_count'
8    metrics:
9      - key: com.dynatrace.extension.sql-oracle.cpu.cores
10        value: col:cpu_count
11        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.

1name: custom:example-extension-name
2version: 1.0.0
3minDynatraceVersion: "1.236"
4author:
5  name: Dynatrace
6
7metrics:
8  - key: your.metric.name
9    metadata:
10        displayName: Display name of the metric visible in Metrics browser
11        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.

1sqlOracle:
2  - group: Number of CPU cores
3    featureSet: cpu
4    query:
5      SELECT value AS cpu_count
6      FROM v$parameter
7      WHERE name = 'cpu_count'
8    metrics:
9      - key: com.dynatrace.extension.sql-oracle.cpu.cores
10        value: col:cpu_count
11        type: gauge
12  - group: Physical read bytes
13    featureSet: io
14    query: >
15      SELECT
16             DECODE(name, 'physical read total bytes', value) AS bytes_written,
17             DECODE(name, 'physical write total bytes', value) AS bytes_read
18      FROM v$sysstat
19      WHERE name IN ('physical read total bytes', 'physical write total bytes')
20    metrics:
21      - key: com.dynatrace.extension.sql-oracle.io.bytesRead
22        value: col:bytes_read
23        type: count
24      - key: com.dynatrace.extension.sql-oracle.io.bytesWritten
25        value: col:bytes_written
26        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.