SQL ๐
Note
If youโre using the Splunk Distribution of the OpenTelemetry Collector and want to collect SQL metrics, use the native OTel SQL Query receiver component.
The SQL monitor gathers database usage metrics from SQL queries on your databases. Itโs available for Kubernetes, Windows, and Linux.
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.
See how to Use Smart Agent monitors with the Collector.
See how to set up the Smart Agent receiver.
For a list of common configuration options, refer to Common configuration settings for monitors.
Learn more about the Collector at Get started: Understand and use the Collector.
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:
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 |
|
|
|
No |
|
|
|
No |
|
|
|
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 |
|
|
|
No |
|
Metrics generated from the query. |
|
No |
|
|
The nested metrics
configuration object has the following fields:
Option |
Required |
Type |
Description |
---|---|---|---|
|
Yes |
|
|
|
Yes |
|
The column name that holds the data point value. |
|
No |
|
|
|
No |
|
|
|
No |
|
|
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:
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:
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:
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 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 (''
).
Metrics ๐
This integration doesnโt produce any metrics.
Troubleshooting ๐
If you are a Splunk Observability Cloud customer and are not able to see your data in Splunk Observability Cloud, you can get help in the following ways.
Available to Splunk Observability Cloud customers
Submit a case in the Splunk Support Portal .
Contact Splunk Support .
Available to prospective customers and free trial users
Ask a question and get answers through community support at Splunk Answers .
Join the Splunk #observability user group Slack channel to communicate with customers, partners, and Splunk employees worldwide. To join, see Chat groups in the Get Started with Splunk Community manual.