Configuration

Example

To activate this integration, add the following to your Collector configuration:

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

Next, add the monitor to the service.pipelines.metrics.receivers section of your configuration file:

service:
  pipelines:
    metrics:
      receivers: [smartagent/sql]

Configuration settings

The following tables show the configuration options for this monitor:

The nested queries configuration object has the following fields:

The nested metrics configuration object has the following fields:

Supported drivers

You must specify the dbDriver option that contains the name of the database driver to use. These names are the same as the name of the Golang SQL driver used in the agent. The monitor formats the connectionString according to the driver you specify.

This is the list of the drivers currently supported:

• hana.

• sqlserver.

• mysql.

• postgres.

• pq.

• snowflake.

Parameterized connection string

The integration treats the value of connectionString as a Golang template with a context consisting of the variables host and port and all the parameters from the params option. To add a variable to the template, use the Golang {{.varname}} template syntax.

See the following example:

smartagent/sql:
  type: sql
  host: localhost
  port: 1433
  dbDriver: sqlserver
  connectionString: 'Server=127.0.0.1;Database=WideWorldImporters;User Id=sa;Password=123456;'
  queries:
    - query: 'SELECT COUNT(*) as count FROM Sales.Orders'
      metrics:
        - metricName: "orders"
          valueColumn: "count"

Collect Snowflake performance and usage metrics

To configure the agents to collect Snowflake performance and usage metrics, do the following:

1. Copy the pkg/sql/snowflake-metrics.yaml file from the sql monitor repo into the same location as your agent.yaml file. For example, /etc/splunk. Find the latest version of snowflake-metrics.yaml in our GitHub repo.

2. Configure the SQL monitor as follows:

   receivers:
     smartagent/sql:
       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 the contents of snowflake-metrics.yaml into agent.yaml under queries. Edit snowflake-metrics.yaml to only include the metrics you want to monitor.

Using the monitor

Consider the following customers database table:

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

receivers:
  smartagent/sql:
    type: sql
    host: localhost
    port: 5432
    dbDriver: postgres
    params:
      user: "${env:SQL_USERNAME}"
      password: "${env:SQL_PASSWORD}"
    # 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"]

When you use this configuration, you get series of MTS, all with the metric name customers. Each MTS has a county and status dimension. The dimension 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.

Using metric expressions

If you need to do more complex logic than mapping columns to metric values and dimensions, use the datapointExpressions option that’s available for individual metric configurations. Create more sophisticated logic to derive data points from individual rows by using the expr expression language. 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.

Both the GAUGE and CUMULATIVE functions have the following signature:

(metricName, dimensions, value)

• metricName: Must be a string

• dimensions: Must be a map of string keys and values, and

• value: Must be a numeric value.

Each of the columns in the row maps to a variable in the context of the expression with the same name. For example, if you have a column called name in your SQL query result, you can use a variable called name in the expression. In your expression, surround string values with single quotes ('').

Metrics

This integration doesn’t produce any metrics.

Troubleshooting