Configure CSV lookups

CSV lookups match field values from your events to field values in the static table represented by a CSV file. Then they output corresponding field values from that table to your events. They are also referred to as "static lookups".

Each column in a CSV table is interpreted as a potential value of a field.

About the CSV files

There are a few restrictions to the kinds of CSV files that can be used for CSV lookups:

• Most CSV table files have at least two columns. One of those columns should represent a field with a set of values that includes those belonging to a field in your events. The column does not have to have the same name as the event field. Any column can have multiple instances of the same value, as this represents a multivalued field.
• The CSV file cannot contain non-utf-8 characters. Plain ascii text is supported, as is any character set that is also valid utf-8.
• The following are unsupported:
  • CSV files with pre-OS X (OS 9 or earlier) Macintosh-style line endings (carriage return ("\r") only)
  • CSV files with header rows that exceed 4096 characters.

Create a CSV lookup

If you have Splunk Enterprise, here's how you add a lookup.

1. Add the CSV file for the lookup to your Splunk deployment.

The CSV file must be located in one of the following places:

$SPLUNK_HOME/etc/system/lookups

$SPLUNK_HOME/etc/apps/<app_name>/lookups

Create the lookups directory if it does not exist.

2. Add a CSV lookup stanza to transforms.conf.

If you want the lookup to be available globally, add its lookup stanza to the version of transforms.conf in $SPLUNK_HOME/etc/system/local/. If you want the lookup to be specific to a particular app, add its stanza to the version of transforms.conf in $SPLUNK_HOME/etc/apps/<app_name>/local/.

Caution: Do not edit configuration files in $SPLUNK_HOME/etc/system/default.

The CSV lookup stanza names the lookup table and provides the name of the CSV file that the lookup uses. It uses these required attributes.

• [<lookup_name>]: The name of the lookup.
• filename = <string>: The name of the CSV file that the lookup references.

3. (Optional) Use the filter attribute to prefilter large CSV lookup tables.

You may need to prefilter significantly large CSV files. To do this use the filter attribute to restrict searches. See "Prefilter large CSV lookup tables," in this topic.

4. (Optional) Set up field/value matching rules for the CSV lookup.

See "Add field matching rules to your lookup configuration," in this manual.

5. (Optional) If the CSV file contains time fields, make the CSV lookup time-bounded.

See "Configure a time-bounded lookup," in this manual.

6. (Optional) Make the CSV lookup automatic by adding a configuration to props.conf

If you want the automatic lookup to be available globally, add its lookup stanza to the version of props.conf in $SPLUNK_HOME/etc/system/local/. If you want the lookup to be specific to a particular app, add its stanza to the version of props.conf in $SPLUNK_HOME/etc/apps/<app_name>/local/.

Caution: Do not edit configuration files in $SPLUNK_HOME/etc/system/default.

See "Make your lookup automatic," in this manual.

7. Restart Splunk Enterprise to implement your changes.

If you have set up an automatic lookup, after restart you should see the output fields from your lookup table listed in the fields sidebar. From there, you can select the fields to display in each of the matching search results.

Prefilter large CSV lookup tables

When your CSV lookup file is extremely large, performance can suffer when your lookups must search through the entire table to retrieve matching field values. If you know that you only need results from a subset of records in the lookup table, improve search performance by using the filter attribute to filter out all of the records that do not need to be looked at. The filter attribute requires a string containing a search query with Boolean expressions and/or comparison operators (==, !=, >, <, <=, >=, OR , AND, and NOT). This query runs whenever you run a search that invokes this lookup.

For example, if your lookup configuration has filter = (CustID>500) AND (CustName="P*"), it will try to retrieve values only from those records in the table that have a CustID value that greater than 500 and a CustName value that begins with the letter P.

Note: You can also filter records from CSV tables when you use the WHERE clause in conjunction with the inputlookup and inputcsv commands, when you use those commands to search CSV files.

CSV lookup example

This example explains how you can set up a lookup for HTTP status codes in an access_combined log. In this example, you design a lookup that matches the status field in your events with the status column in a lookup table named http_status.csv. Then you have the lookup output the corresponding status_description and status_type fields to your events.

The following is the text for the http_status.csv file.

status,status_description,status_type
100,Continue,Informational
101,Switching Protocols,Informational
200,OK,Successful
201,Created,Successful
202,Accepted,Successful
203,Non-Authoritative Information,Successful
204,No Content,Successful
205,Reset Content,Successful
206,Partial Content,Successful
300,Multiple Choices,Redirection
301,Moved Permanently,Redirection
302,Found,Redirection
303,See Other,Redirection
304,Not Modified,Redirection
305,Use Proxy,Redirection
307,Temporary Redirect,Redirection
400,Bad Request,Client Error
401,Unauthorized,Client Error
402,Payment Required,Client Error
403,Forbidden,Client Error
404,Not Found,Client Error
405,Method Not Allowed,Client Error
406,Not Acceptable,Client Error
407,Proxy Authentication Required,Client Error
408,Request Timeout,Client Error
409,Conflict,Client Error
410,Gone,Client Error
411,Length Required,Client Error
412,Precondition Failed,Client Error
413,Request Entity Too Large,Client Error
414,Request-URI Too Long,Client Error
415,Unsupported Media Type,Client Error
416,Requested Range Not Satisfiable,Client Error
417,Expectation Failed,Client Error
500,Internal Server Error,Server Error
501,Not Implemented,Server Error
502,Bad Gateway,Server Error
503,Service Unavailable,Server Error
504,Gateway Timeout,Server Error
505,HTTP Version Not Supported,Server Error

1. Put the http_status.csv file in $SPLUNK_HOME/etc/apps/search/lookups/. This indicates that the lookup is specific to the Search App.

2. In the transforms.conf file located in $SPLUNK_HOME/etc/apps/search/local, put:

[http_status]
filename = http_status.csv

3. Restart Splunk Enterprise to implement your changes.

Now you can invoke this lookup in search strings with the following commands:

• lookup: Use to add fields to the events in the results of the search.
• inputlookup,: Use to search the contents of a lookup table.
• outputlookup: Use to write fields in search results to a CSV file that you specify.

See the topics on these commands in the Search Reference for more information about how to do this.

For example, you could run this search to add status_description and status_type fields to events that contain status values that match status values in the CSV table.

... | lookup http_status status OUTPUT status_description, status_type

Use search results to populate a CSV lookup table

If you have Splunk Enterprise, you can edit a local or app-specific copy of savedsearches.conf to use the results of a report to populate a lookup table.

In a report stanza, where the search returns a results table:

1. Add the following line to enable the lookup population action.

action.populate_lookup = 1

This tells Splunk software to save your results table into a CSV file.

2. Add the following line to specify where to copy your lookup table.

action.populate_lookup.dest = <string>

The action.populate_lookup.dest value is a lookup name from transforms.conf or a path to a CSV file where the search results are to be copied. If it is a path to a CSV file, the path should be relative to $SPLUNK_HOME.

For example, if you want to save the results to a global lookup table, you might include:

action.populate_lookup.dest = etc/system/lookups/myTable.csv

The destination directory, $SPLUNK_HOME/etc/system/lookups or $SPLUNK_HOME/etc/<app_name>/lookups, should already exist.

3. Add the following line if you want this search to run when Splunk Enterprise starts up.

run_on_startup = true

If it does not run on startup, it will run at the next scheduled time. We recommend that you set run_on_startup = true for scheduled searches that populate lookup tables.

Because the results of the reporter copied to a CSV file, you can set up this lookup the same way you set up a CSV lookup.