Splunk® DB Connect

Deploy and Use Splunk DB Connect

Download manual as PDF

NOTE - Splunk DB Connect version 1.x reached its End of Life on July 28, 2016. Please see the migration information.
This documentation does not apply to the most recent version of DBX. Click here for the latest version.
Download topic as PDF

Set up a lookup table

Splunk DB Connect lets you define a lookup table that uses an external database as its source. For more information on lookups, see "About lookups and field actions".

To set up a database lookup table:

1. In Splunk Web, go to Settings > Lookups > Database Lookups. Click Add new.

2. Enter a Lookup Name, then choose your Database from the menu.

3. Enter the Database Table name.

4. At this point you have two options:

  • Specify Lookup Fields directly: Click the Fill all columns button to bring in all columns from the table. Or specify the individual fields/columns to be used in the lookup. (You can fill all the columns, then use the Delete button next to each field to trim the list.)
  • Use an SQL query to pull data in: Check Configure advanced Database lookup settings, then define a SQL query, using $input_field$ as a placeholder for each input field value.

5. Click Save.

This creates a scripted lookup definition, which you can use inside Splunk as if it were a regular lookup by using the | lookup command.

Note: By default, only one match is returned from the database for each lookup input row. If you want to return more than one row, you must edit max_matches in dblookup.conf, as shown in the following section: "Create a lookup by editing dblookup.conf."

You can also configure an automatic lookup. For information on automatic lookups, see this topic in the Splunk Enterprise platform documentation.

Create a lookup by editing dblookup.conf

You can also create a lookup by editing the dblookup.conf file. This is useful if you have a table with many columns that would be cumbersome to select using Manager. This requires that you also create the lookup definition manually in transforms.conf with external_cmd = dblookup.py <name from dblookup.conf>

By default, only one match is returned from the database for each lookup input row. If you want to return more than one row, you must change max_matches in dblookup.conf.

Lookups and Splunk DB Connect in a distributed environment

Some constraints exist when using Splunk DB Connect to perform lookups in a distributed Splunk environment.

  • If you are running DB Connect in a distributed environment, you must perform lookups on the search head where Splunk DB Connect is installed. To perform a lookup locally, add local=1 after the lookup command.

Example:

index=test | lookup local=1 mysql_table ip_address as clientip OUTPUT host | table clientip, host

  • Automatic lookups are not supported.

Note: To perform database lookups in a distributed search environment, you must install the DB Connect app on a search head. For instructions on installing apps in a search head pooling environment, see "Create a search head pool". For instructions on configuring search head pooling for Splunk DB Connect, see "Set up search head pooling".

Lookups and Datatypes

Splunk typically only sends CSV data to a lookup, so dblookup receives everything as a string. DB Connect can do datatype conversion under one of these two conditions:

  • the database/JDBC driver you're using supports parameter metadata. This means that it can analyze the SQL you're about to execute and can tell which datatype is expected for each placeholder beforehand. Some JDBC drivers don't support this. Even if the JDBC driver supports it, in most cases it requires another round trip to the database for analyzing the SQL and therefore costs performance.
  • the datatype is specified in the SQL template for the lookup. DB connect will generically convert the values it receives from Splunk to the datatype specified in the parameter placeholder. The syntax for specifying those datatypes is as follows: $<fieldname>[:<DATATYPE>]$

The datatype portion is optional. If it's not supplied, then DB Connect will try to use parameter metadata from the JDBC API. If it's not possible it will fallback to simply supply String values.

For example:
SELECT FOO FROM BAR WHERE _time = $_time:TIMESTAMP$ and src_ip = $src_ip:VARCHAR$

This will force DB Connect to supply an actual timestamp value and to not rely on datatype conversion of the database or JDBC driver. A list of datatypes that can be used can be found here: http://docs.oracle.com/javase/6/docs/api/java/sql/Types.html (not all of them are fully supported). For the TIMESTAMP datatype, the value is expected to be in epoch format (for fields other than _time, it might be necessary to strptime them in the search).

PREVIOUS
Configure database input queries
  NEXT
Security and access controls

This documentation applies to the following versions of Splunk® DB Connect: 1.0.6, 1.0.7, 1.0.8, 1.0.9, 1.0.10, 1.0.11, 1.1, 1.1.1, 1.1.2, 1.1.3, 1.1.4, 1.1.5, 1.1.6, 1.1.7, 1.2.0, 1.2.1, 1.2.2


Comments

Hi there. The behavior you’re seeing is an unfortunate side effect of the way this documentation was forked when we started writing documentation for DB Connect v2.

The closest topic to this one for DB Connect v2 is here: http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Createandmanagedatabaselookups

Mtevenan splunk, Splunker
February 16, 2016

The "latest" link is borked.

Woodcock
February 15, 2016

Was this documentation topic helpful?

Enter your email address, and someone from the documentation team will respond to you:

Please provide your comments here. Ask a question or make a suggestion.

You must be logged into splunk.com in order to post comments. Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters