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

1. From within Splunk DB Connect, navigate to the Data Lab > Lookups tab.
2. Click New Lookup.

Note: You have to Create a new identity and create a new connection, before you can create a new database lookup.

Define your lookup in dbx_lookups.conf:

1. Name lookup: Name and describe your new lookup.
2. Choose and preview table: Specify a query to fetch data from your database.
3. Choose the Splunk Enterprise fields on which to base the lookup: Perform a Splunk Enterprise search to select the fields on which to base the lookup.
4. Map Splunk fields and output to new fields: Map your selected Splunk Enterprise fields, plus any new fields you want to add, to columns in your database table.
5. Preview your lookup: Preview the output in the table.
6. Finalize your lookup: Specify how often DB Connect reloads the lookup data into the cache, and how often the lookup results enrich events.
7. Click Save.

Name lookup

Configure the following fields:

• Name: The output name cannot contain any spaces. Do no use special characters.
• Description
• App: 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.
• Connection: Choose the database connection you want to use with this lookup. DB Connect validates the connection, and displays an error message if it cannot connect. You cannot continue the new lookup setup process without specifying at least one valid connection in the Connection menu.

Choose and preview table

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

If you have already specified a query, DB Connect displays the query by default. it will appear here. If not, or if you want to use a different query, complete the following steps:

1. Choose either Automatic Mode or Editor Mode from the menu.
2. For Automatic Mode:
   1. Choose the catalog, schema, and table to query from the corresponding menus.
   2. Choose the maximum rows to query from the Max rows menu. The default is 100.

For Editor Mode:

• Enter a SQL query into the field. You can format your SQL by clicking Format SQL.
3. Click the green Execute button. DB Connect queries your database and displays the results.
4. Click Continue.

Choose the Splunk Enterprise fields on which to base the lookup

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). To select a saved search, click Saved Search, then click the field that appears. Enter the name of the saved search to use or select it from the menu. Splunk Enterprise performs the search and displays the results in a table. Each column corresponds to a Splunk Enterprise field.
2. From the results that appear, click all of the columns that correspond to the Splunk Enterprise fields on which you want to base your lookup. The blue Fields Selected counter keeps track of how many fields you have chosen.
3. Click Continue.

Map Splunk fields and output to new fields

1. Map your Splunk fields to your database columns: from each menu under the Columns heading, choose the table column that you want to map to the corresponding Splunk Enterprise field under the Fields heading.
2. Map the database columns you want to include in your Splunk Enterprise searches to new Splunk Enterprise fields. This is your opportunity to add a field that does not already exist in your Splunk Enterprise index. Click Add Column, choose the database table column to map, then type in an output field name to which to map the column in Splunk Enterprise. The output field name must be unique, and must not overlap with an input 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.
3. Click Continue.

Preview your lookup

DB Connect displays the output as enriched data in a table. Your live database data populates the table.

Click on a previous step to change any fields or mappings, then click Continue.

Finalize your lookup

Review how to use the newly created lookup, and then click Save:

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

   dbxlookup lookup=<string>

Related

lookup

Required Arguments

lookup

Syntax: lookup=<string>

Description: Name of a configured database lookup object.

Example

The following example uses the lookup "dbx_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

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