Plugins
Source plugins
SQL DB Input

SQL DB Input source plugin

The SQL DB Input source plugin lets you ingest log data from a relational database. If you write your logs to a relational database, you can use the SQL DB Input plugin to convert that data into a pipeline-friendly format.

This plugin supports the following SQL formats:

  • Postgres
  • MySQL
  • Oracle
  • Microsoft SQL Server
  • SQLite

Configuration parameters

The SQL DB Input plugin accepts these configuration parameters.

General

NameKeyRequiredDescription
DB TypedriverThe SQL driver for your relational data. Possible values are postgres, mysql, oracle, sqlserver, and sqlite. If unspecified, the default value is postgres.
Database Connection StringdsnThe data source name for your database, in string format.
SQL QueryqueryThe SQL query to perform. This query supports named arguments in @named format.

Advanced

NameKeyRequiredDescription
Argument ColumncolumnsForArgsA list of column names that you'd like to turn into arguments, separated by spaces. The most recently-scanned row in each specified column will become a @named argument that you can use to paginate data. For example, if this key has a value of id log_time, the SQL DB Input plugin creates two arguments that you can use in queries: @last_id and @last_log_time.
Time ColumntimeFromThe column in your database with information about the time when each log was ingested. If unspecified, each ingested log is assigned a time value equivalent to the time that the SQL DB Input plugin runs its query.
Time FormattimeFormatThe time format of your timeFrom value. Possible values are unix_sec, unix_ms, unix_us, unix_ns, or any RFC time format layout. If unspecified, the default value is unix_ns if timeFrom is an integer and 2006-01-02T15:04:05.999999999Z07:00 if timeFrom is a string.
Fetch IntervalfetchIntervalHow often to query your database. This value must specify one or more integer and one or more time unit, like 5m or 1m30s. Valid time units are ns, us, ms, s, m, and h. If unspecified, the default value is 1s.
Storage KeystorageKeyA key to store the SQL DB Input plugin's metadata, which lets the SQL DB Input plugin store arguments and other configuration data even if the plugin restarts. By default, the SQL DB Input plugin writes its own metadata in sqldb_{hash}.gob format, where {hash} is a string generated from your query arguments. You can also specify your own value, which can follow any format and use any file extension.
Storage PathstorageDirThe path to the directory where the SQL DB Input plugin will write data. You can use an absolute or relative path, but Chronosphere recommends using an absolute path. If unspecified, the default value is /data/storage. However, if no /data/storage directory exists, the SQL DB Input plugin writes to your operating system's default directory for temporary data.

Example queries

The following examples describe how to configure the SQL DB Input plugin in different hypothetical scenarios.

Serial ID

Given the following database structure, which includes an auto-increasing id:

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    payload JSON NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

You can use the SQL DB Input plugin to paginate over your data using id. To include such a query in your pipeline, configure the SQL DB Input plugin as follows:

pipeline:
  inputs:
    - name: sqldb
      dsn: "postgres://user:password@localhost:5432/dbname"
      query: |-
        SELECT *
        FROM logs
        WHERE @last_id IS NULL OR id > @last_id
        ORDER BY id
        LIMIT 100
      columnsForArgs: id

The previous query sorts by id, and then uses the id value from the previous query to paginate data.

Time-based pagination

Given the following database structure, which includes a non-sortable id:

CREATE TABLE logs (
    id UUID PRIMARY KEY,
    payload JSON NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

You can use a combination of id and created_at to paginate data. To include such a query in your pipeline, configure the SQL DB Input plugin as shown:

pipeline:
  inputs:
    - name: sqldb
      dsn: "postgres://user:password@localhost:5432/dbname"
      query: |-
        SELECT *
        FROM logs
        WHERE @last_id IS NULL OR @last_created_at IS NULL OR (created_at >= @last_created_at OR (created_at = @last_created_at AND id > @last_id))
        ORDER BY created_at, id
        LIMIT 100
      columnsForArgs: "id created_at"

The previous query sorts by both id and created_at, then uses the id and created_at values from the previous query to paginate data.

Splunk universal forwarderSyslog