Docs » Configure application receivers » Configure application receivers for languages » SQL

SQL 🔗

Description 🔗

The Splunk Distribution of OpenTelemetry Collector provides this integration as the sql monitor via the Smart Agent Receiver. You can use this monitor to run arbitrary SQL queries against a relational database and use the results to generate data points.

Installation 🔗

This monitor is available in the SignalFx Smart Agent Receiver, which is part of the Splunk Distribution of OpenTelemetry Collector.

To install this integration:

  1. Deploy the Splunk Distribution of OpenTelemetry Collector to your host or container platform.

  2. Configure the monitor, as described in the next section.

Configuration 🔗

This Splunk Distribution of OpenTelemetry Collector allows embedding a Smart Agent monitor configuration in an associated Smart Agent Receiver instance.

Note: Providing a SQL monitor entry in your Smart Agent or Collector configuration is required for its use. Use the appropriate form for your agent type.

Smart Agent 🔗

To activate this monitor in the Smart Agent, add the following to your agent configuration:

monitors:  # All monitor config goes under this key
 - type: sql
   ...  # Additional config

See Smart Agent example configuration for an autogenerated example of a YAML configuration file, with default values where applicable.

Splunk Distribution of OpenTelemetry Collector 🔗

To activate this monitor in the Splunk Distribution of OpenTelemetry Collector, add the following to your agent configuration:

receivers:
  smartagent/sql:
    type: sql
    ...  # Additional config

See configuration examples for specific use cases that show how the collector can integrate and complement existing environments.

Note: Monitors with dimension property and tag update functionality allow an associated dimensionClients field that references the name of the exporter you are using in your pipeline. If you do not specify any exporters through this field, the receiver attempts to use the associated pipeline. If the next element of the pipeline isn’t compatible with the dimension update behavior, and if you configured a single exporter for your deployment, the exporter is selected. If no dimension update behavior is desired, you can specify the empty array [] to disable.

Configuration settings 🔗

The following tables show the configuration options for this monitor:

Option Required Type Description
host no string
port no integer (default: 0)
params no map of strings Parameters to the connectionString that can be templated into that option using Go template syntax (e.g. {{.key}}).
dbDriver no string The database driver to use. Valid values are postgres, mysql, sqlserver, and snowflake.
connectionString no string A URL or simple option string used to connect to the database. For example, if using PostgreSQL, see the list of connection string parameters.
queries yes list of objects (see below) A list of queries to make against the database that are used to generate data points.
logQueries no bool If true, query results are logged at the info level. (default: false)

The nested queries configuration object has the following fields:

Option Required Type Description
query yes string A SQL query text that selects one or more rows from a database
params no list of any Optional parameters that replace placeholders in the query string.
metrics no list of objects (see below) Metrics generated from the query.
datapointExpressions no list of strings A set of [expr] expressions that convert each row to a set of metrics. Each of these run for each row in the query result set, allowing you to generate multiple data points per row. Each expression must evaluate to a single data point or nil.

The nested metrics configuration object has the following fields:

Option Required Type Description
metricName yes string The name of the metric as it appears in Splunk Observability Cloud.
valueColumn yes string The column name that holds the data point value
dimensionColumns no list of strings The names of the columns that make up the dimensions of the data point.
isCumulative no bool Whether the value is a cumulative counters (true) or gauge (false). If you set this to the wrong value and send in your first data point for the metric name with the wrong type, you have to manually change the type, as it is set in the system based on the first type seen. (default: false)
dimensionPropertyColumns no map of lists The mapping between dimensions and the columns to be used to attach respective properties

Use cases 🔗

For example, if you have the following customers database table:

id name country status
1 Bill USA active
2 Mary USA inactive
3 Joe USA active
4 Elizabeth Germany active

Use the following monitor configuration to generate metrics about active users and customer counts by country:

monitors:
  - type: sql
    host: localhost
    port: 5432
    dbDriver: postgres
    params:
      user: admin
      password: s3cr3t
    # The `host` and `port` values shown in this example (also provided through autodiscovery) are interpolated
    # to the connection string as appropriate for the database driver.
    # Also, the values from the `params` configuration option above can be
    # interpolated.
    connectionString: 'host={{.host}} port={{.port}} dbname=main user={{.user}} password={{.password}} sslmode=disable'
    queries:
      - query: 'SELECT COUNT(*) as count, country, status FROM customers GROUP BY country, status;'
        metrics:
          - metricName: "customers"
            valueColumn: "count"
            dimensionColumns: ["country", "status"]

Using this configuration generates a series of time series, all with the metric name customers that includes a county and status dimension. The value is the number of customers that belong to that combination of country and status. You can also specify multiple metrics items to generate more than one metric from a single query.

Metric expressions 🔗

Note: Metric expressions are a beta feature and might break in subsequent non-major releases. The example documented will be maintained for backwards compatibility, however.

If you need to do more complex logic than simply mapping columns to metric values and dimensions, you can use the datapointExpressions option to the individual metric configurations. You can use the [expr] expression language to derive data points from individual rows using more sophisticated logic. These expressions must evaluate to data points created by the GAUGE or CUMULATIVE helper functions available in the expression’s context. You can also have the expression evaluate to nil if you don’t need to generate a data point for a particular row.

The signature for both the GAUGE and CUMULATIVE functions is (metricName, dimensions, value), where metricName must be a string value, dimensions must be a map of string keys and values, and value must be any numeric value.

Each of the columns in the row is mapped to a variable in the context of the expression with the same name. For example, if there is a column called name in your SQL query result, there is a variable called name that you can use in the expression. Note that literal string values used in your expressions must be surrounded by ''.

For example, the MySQL SHOW SLAVE STATS query does not let you pre-process columns using SQL, but you can convert the Slave_IO_Running column, which is a string Yes/No value, to a gauge data point that has a value of 0 or 1 using the following configuration:

   - type: sql
     # This is an example discovery rule. Your environment might be different.
     discoveryRule: container_labels["mysql.slave"] == "true" && port == 3306
     dbDriver: mysql
     params:
       user: root
       password: password
     connectionString: '{{.user}}:{{.password}}@tcp({{.host}})/<database>'
     # You can also use '.user:.password@tcp(.host)/' if you don't want to specify a database.
     queries:
      - query: 'SHOW SLAVE STATUS'
        datapointExpressions:
          - 'GAUGE("mysql.slave_sql_running", {master_uuid: Master_UUID, channel: Channel_name}, Slave_SQL_Running == "Yes" ? 1 : 0)'

Using this configuration generates a single gauge data point for each row in the slave status output, with two dimension, master_uuid and channel, and with a value of 0 or 1, depending on if the slave’s SQL thread is running.

Supported drivers 🔗

The dbDriver option must specify the database driver to use. These are equivalent to the name of the Golang SQL driver used in the agent. The connectionString option is formatted according to the driver that is going to receive it. Here is a list of the drivers currently supported:

  • postgres

  • mysql

  • sqlserver

  • snowflake

Parameterized connection string 🔗

The connectionString option acts as a template with a context consisting of the variables: host, port, and all the values from the params option map. You interpolate variables into it with the Go template syntax {{.varname}}.

Snowflake performance and usage metrics 🔗

Do the following to configure the agents to collect Snowflake performance and usage metrics:

  1. Copy the pkg/sql/snowflake-metrics.yaml file from this repo into the same location as your agent.yaml file (for example, /etc/splunk).

  2. Configure the SQL monitor as follows:

monitors:
  - type: sql
    intervalSeconds: 3600
    dbDriver: snowflake
    params:
      account: "account.region"
      database: "SNOWFLAKE"
      schema: "ACCOUNT_USAGE"
      role: "ACCOUNTADMIN"
      user: "user"
      password: "password"
    connectionString: "{{.user}}:{{.password}}@{{.account}}/{{.database}}/{{.schema}}?role={{.role}}"
    queries:
      {"#from": "/etc/signalfx/snowflake-metrics.yaml"}

You can also copy and paste the contents of snowflake-metrics.yaml into agent.yaml under queries if needed or preferred. Make sure you edit snowflake-metrics.yaml to only include the metrics you want to monitor.

Metrics 🔗

The Smart Agent and Splunk Distribution of OpenTelemetry Collector do not do any built-in filtering of metrics coming out of this monitor.