Set up a lookup table
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
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
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
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
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:
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.
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).
Configure database input queries
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