SQL π
Description π
The Splunk Distribution of OpenTelemetry Collector provides this integration as the SQL monitor type for the Smart Agent Receiver.
Use this monitor to gather database usage metrics from SQL queries on your databases.
This integration is available for Kubernetes, Windows, and Linux.
Benefits π
After you configure the integration, you can access these features:
View metrics. You can create your own custom dashboards, and most monitors provide built-in dashboards as well. For information about dashboards, see View dashboards in Observability Cloud.
View a data-driven visualization of the physical servers, virtual machines, AWS instances, and other resources in your environment that are visible to Infrastructure Monitoring. For information about navigators, see Splunk Infrastructure Monitoring navigators.
Access the Metric Finder and search for metrics sent by the monitor. For information, see Use the Metric Finder.
Installation π
Follow these steps to deploy this integration:
Deploy the Splunk Distribution of OpenTelemetry Collector to your host or container platform:
Configure the monitor, as described in the Configuration section.
Restart the Splunk Distribution of OpenTelemetry Collector.
Configuration π
To use this integration of a Smart Agent monitor with the Collector:
Include the Smart Agent receiver in your configuration file.
Add the monitor type to the Collector configuration, both in the receiver and pipelines sections.
Read more on how to Use Smart Agent monitors with the Collector.
See how to set up the Smart Agent receiver.
Learn about config options in Collector default configuration.
Note
Provide an SQL monitor entry in your Collector or Smart Agent (deprecated) configuration. Use the appropriate form for your agent type.
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
To complete the monitor activation, you must also include the smartagent/sql
receiver item in a metrics
pipeline. To do this, add the receiver item to the service
> pipelines
> metrics
> receivers
section of your configuration file. For example:
service:
pipelines:
metrics:
receivers: [smartagent/sql]
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 Install and configure the SignalFx Smart Agent for an autogenerated example of a YAML configuration file, with default values where applicable.
Configuration settings π
The following tables show the configuration options for this monitor:
Option |
Required |
Type |
Description |
---|---|---|---|
|
Yes |
|
A list of queries that generate data points. |
|
No |
|
Host or address of the SQL instance. |
|
No |
|
Port of the SQL instance. The default value is |
|
No |
|
Replaceable parameters, in the form of key-value pairs. The system inserts the values into |
|
No |
|
The database driver to use. Valid values are |
|
No |
|
Connection string and replaceable parameters used to connect to the database. To learn more, see the list of connection string parameters for the Go |
|
No |
|
(default: |
The nested queries
configuration object has the following fields:
Option |
Required |
Type |
Description |
---|---|---|---|
|
Yes |
|
An SQL query text that selects one or more rows from a database. |
|
No |
|
Optional parameters that replace placeholders in the query string. |
|
No |
|
Metrics generated from the query. |
|
No |
|
A set of 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 |
---|---|---|---|
|
Yes |
|
The name of the metric as it appears in Splunk Observability Cloud. |
|
Yes |
|
The column name that holds the data point value. |
|
No |
|
The names of the columns that make up the dimensions of the data point. |
|
No |
|
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. The default value is |
|
No |
|
Mapping between dimensions and the columns to be used to attach corresponding properties. |
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.
Note
Please be sure to use the correct connection string syntax based on the driver youβre using. For example, if you use the mysql
driver, you must use the connection string syntax for the mysql
driver.
This is the list of the drivers currently supported:
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"
Parameterized connection string π
The monitor 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.
Collect Snowflake performance and usage metrics π
To configure the agents to collect Snowflake performance and usage metrics, do the following:
Copy the
pkg/sql/snowflake-metrics.yaml
file from thesql
monitor repo into the same location as youragent.yaml
file. For example,/etc/splunk
. Find the latest version ofsnowflake-metrics.yaml
in our GitHub repo.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:
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"]
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 stringdimensions
: Must be a map of string keys and values, andvalue
: 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 (''
).
For example, the MySQL SHOW REPLICA STATUS
query doesnβt let you pre-process columns using SQL,
but you can convert the Replica_IO_Running
column (a string Yes/No
value) to a gauge data point with values of value of 0 or 1 by using the following configuration:
- type: sql
# This is an example discovery rule. Your environment might be different.
discoveryRule: container_labels["mysql.replica"] == "true" && port == 3306
dbDriver: mysql
params:
user: root
password: password
connectionString: '{{.user}}:{{.password}}@tcp({{.host}})/<database>'
# You can also use '.user:[email protected](.host)/' if you don't want to specify a database.
queries:
- query: 'SHOW REPLICA STATUS'
datapointExpressions:
- 'GAUGE("mysql.replica_sql_running", {main_uuid: Main_UUID, channel: Channel_name}, Replica_SQL_Running == "Yes" ? 1 : 0)'
Use this configuration to generate a single gauge data point for each row in the replica status
output, with two dimension, main_uuid
and channel
, and with a value of 0
or 1
, depending on if the SQL thread for the replica is running.
Metrics filtering π
The Splunk Distribution of OpenTelemetry Collector and the Smart Agent donβt do any built-in filtering of metrics coming out of this monitor.
Get help π
If you are not able to see your data in Splunk Observability Cloud, try these tips:
Submit a case in the Splunk Support Portal
Available to Splunk Observability Cloud customers
-
Available to Splunk Observability Cloud customers
Ask a question and get answers through community support at Splunk Answers
Available to Splunk Observability Cloud customers and free trial users
Join the Splunk #observability user group Slack channel to communicate with customers, partners, and Splunk employees worldwide
Available to Splunk Observability Cloud customers and free trial users
To learn how to join, see Get Started with Splunk Community - Chat groups
To learn about even more support options, see Splunk Customer Success.