Create and manage database lookups

A database lookup object enables you to enrich and extend the usefulness of your Splunk Enterprise data through interactions with your external database.

For example, a database lookup is a lookup that takes a customer ID value in an event, matches that value with the corresponding customer name in your external database, and then adds the customer name to the event as the value of a new customer_name field. Therefore, if you have an event where customer_id="24601", the lookup adds customer_name="Valjean, Jean" to the event.

You can run lookups against your database by specifying the dbxlookup command in your search query.

The database data DB Connect transfers when it performs lookups does not count toward your daily Splunk Enterprise indexing quota.

Select between database lookups and Splunk lookups

Splunk provides lookups by default which enable high-speed lookup functionality by holding the desired data in memory or the KV store. CSV lookups are cached in memory for high speed access. This is ideal behavior for data sets that are infrequently changed, smaller than available RAM, or both. KV Store lookups are not fully cached in memory, but they do consume resources. In either case, these lookups can be created and updated from an external database using the dbxquery command in a scheduled search. For instance, the following search could be used to maintain a CSV lookup of IDs and names:

   | dbxquery query="SELECT actor_id,first_name,last_name FROM \"sakila\".\"dbo\".\"actor\"" connection="ms-sql" 
   | inputlookup append=true actor-lookup.csv 
   | dedup actor_id 
   | outputlookup actor-lookup.csv

A database lookup is different from a Splunk lookup because each execution will contact the database for the current state of the rows in question. This is ideal behavior for data sets that are rapidly changing, very large, or both, because the user receives the freshest data possible.

Create a database lookup

From within Splunk DB Connect, navigate to the Data Lab > Lookups tab and click New Lookup.You have to Create a new identity and create a new connection, before you can create a new database lookup.

Complete the following steps to create a database lookup.

• Set Reference Search. Perform a Splunk Enterprise search to select the fields on which to base the lookup.
• Set Lookup SQL. Specify a reference search to fetch data from your database.
• Field Mapping. Map your selected database table column, plus any new column you want to add, to Splunk Enterprise fields.
• Set Properties. Configure the basic settings and parameters of the lookup.

Set Reference Search

In this step, you perform a search or use the saved search of you Splunk indexed data.

1. Perform a search of your Splunk Enterprise data. You can either enter a search using the Search Processing Language (SPL), or you can run a report (saved search).
2. To select a saved search, click Saved Search and then choose the one you want to use from the drop-down list. Splunk Enterprise performs the search and displays the results in a table. Each column corresponds to a Splunk Enterprise field.
   If you want to use the Saved Search, set the permission of the saved search to This app only (splunk_app_db_connect) or All apps.
3. Click Next.

Set Lookup SQL

In this step, you specify a search to fetch data from your database.

1. Choose a connection that you want to use for this lookup from the drop-down list under the Connection field. The drop-down list lists all the connections you have configured in DB Connect, you can also enter the connection name to search the connection you want to use.
   Once you have select the connection, Splunk DB Connect will try to validate the connection, and will display an error message if it is not able to do so. You cannot continue the new lookup setup process unless a valid connection is specified.
2. From the corresponding dialog menus, choose the Catalog, Schema, and Table that contain the columns you want to base for this lookup.
3. After you choose the table, the corresponding SQL query will be displayed in SQL Editor, you can preview the result of the query. If you need further edit or write your own SQL query, you can write it directly in SQL Editor and click Execute SQL to preview the result. You can make complex SQL statements easier to read by clicking Format SQL. This is a one-way operation.
   The reference search fields of Splunk data will be listed at the right side of the page. You can use it as a reference to specify your SQL query.
4. Click Next.

Field Mapping

In this step, you map the selected Splunk fields with the database table column(s), then you can add the table columns as new Splunk data fields to enrich you Splunk data.

1. Search fields mapping. Click Add Search Field and choose the field name you want to match to the database table column. Then select the column name in the Table Column field. You can add multiple pairs of fields and columns if you need to.
2. Add table column(s) as new Splunk fields. Click Add Column and choose the column name on the drop-down list, the column you select will be added as new fields in Splunk data.
3. Set alias name to the table columns. (Optional) To make the name of the database column easy to understand, You can rename the database column by entering a new name in Aliases field. The name must be unique, and must not overlap with the field name. When you choose a database column in the first part of this step, do not then choose the same database column in the second part of this step.
4. The corresponding search will be displayed in Preview Results field. You can run the search to preview the data by clicking Open in Search.

Set properties

Configure the following fields:

• Name: The output name cannot contain any spaces. Do no use special characters.
• Description: The description of the lookup
• Application: The name of the Splunk Enterprise app in which DB Connect saves this lookup object. By default, the DB Connect selects Splunk DB Connect. This menu enables other apps to use DB Connect inputs, outputs, and lookups within their own context.

You can use the generated dbxlookup command in the summary page to enrich your Splunk data. See more on Use dbxlookup to perform lookups in DB Connect.

Edit database lookups

To see a list of the defined database lookups, first click the Data Lab>Lookup tab. To edit a database lookup, click its name. You can make changes to a database lookup using the following buttons on the lookup page:

• Enable/Disable: Enable/Disable a lookup by clicking Disable here.
• Edit: Edit a lookup by clicking its name or the Edit button.
• Clone: Creates a copy of the lookup. You must give the copy a new name.
• Delete: Delete the lookup.

You can also edit any of the attributes of a database lookup listed in Create a database lookup, except its name. To change the name of a lookup, clone it, give the clone the name you want, and then delete the original lookup.

Use dbxlookup to perform lookups in DB Connect

Description

dbxlookup is a search command for performing lookups by using remote database tables as lookup tables. Use dbxlookup to enrich your indexed events with the information you have stored in external databases. If you are not using Verbose search mode, you must explicitly reference input fields in the search.

Syntax

There are two ways to use dbxlookup command.

   dbxlookup chunksize=<integer> lookup=<lookup_name>

The argument <lookup_table_name> refers to the lookup you defined in DB Connect UI.
From DB Connect 3.1.0, dbxlookup command allows users to declare Splunk fields/table column mapping directly in the options. The syntax is similar as lookup. Users do not have to create a lookup in UI before using it in dbxlookup command.

  dbxlookup connection=<connection name> query=<SQL query> chunksize=<integer> <database_key_column> AS <splunk_key_field> OUTPUT <column_destname> AS <field_destname>

Argument

If you want to use the lookup defined in DB Connect, use the following

lookup

Syntax: lookup=<lookup_name>

Description:: Required. It refers to the lookup name you defined in UI.

chunksize

Syntax: <integer>

Description: Optional. Specifies the number of events will be used to look up the database of each query.

Default: 1000.

If you want to define the connection, SQL query and mappings directly in dbxlookup command, use the following

connection

Syntax: connection=<connection name>

Description:: Required. Specifies the connection name you want to use for the lookup.

query

Syntax: query=<SQL query>

Description:: Required. Specifies the SQL query to search the data in your database.

chunksize

Syntax: <integer>

Description: Optional. Specifies the number of events will be used to look up the database of each query.

Default: 1000.

<database_key_column>

Syntax: <string>

Description:: Refers to a table column in the database table to match the key value against the field in Splunk. You can specify multiple <database_key_column> values separated.

<splunk_key_field>

Syntax: <string>

Description:: Refers to a Splunk field which to acquire the value to match in the database table. You can specify multiple <splunk_key_field> values.

<column_destname>

Syntax: <string>

Description:: Refers to a table column in the database table to be copied into the Splunk field. You can specify multiple <column_destname> values separated by commas.

<field_destname>

Syntax: <string>

Description:: The alias name of <column_destname> in Splunk. You can specify multiple <field_destname> values separated by commas.

Default:: The value of the <field_destname> argument.

When using the dbxlookup command, if an OUTPUT clause is not specified, all the table columns that are not the key field are used as output fields. If the OUTPUT clause is specified, the output lookup fields overwrite existing fields.

Examples

Example 1: This example uses the lookup "GTS_Product_Lookup" to enrich the results of a Splunk Enterprise search query.

sourcetype=GTS_Sessions | dbxlookup lookup="GTS_Product_Lookup" | stats sum(product_price) as "spend" by uid

Example 2: If you are not using Verbose search mode, you must explicitly reference input fields in the search:

sourcetype=GTS_Sessions | fields pid, uid | dbxlookup lookup="GTS_Product_Lookup" | stats sum(product_price) as "spend" by uid

Example 3: This example defines the connection, SQL query, mappings and alias name directly in dbxlookup command.

sourcetype=GTS_Sessions | dbxlookup connection="sh-oracle" query="SELECT * FROM \"TEST\".\"DBX"" ID AS customer_id OUTPUT data AS customer_data, name AS customer_name

Example 4: If there is no alias name, the table column name will be set as the corresponding Splunk field name.

sourcetype=GTS_Sessions | dbxlookup connection="sh-oracle" query="SELECT * FROM \"TEST\".\"DBX"" ID OUTPUT data, name AS "Last Name"