
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 oftransforms.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 ofprops.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.
PREVIOUS Introduction to lookup configuration |
NEXT Configure external lookups |
This documentation applies to the following versions of Splunk® Enterprise: 6.3.0, 6.3.1, 6.3.2, 6.3.3, 6.3.4, 6.3.5, 6.3.6, 6.3.7, 6.3.8, 6.3.9, 6.3.10, 6.3.11, 6.3.12, 6.3.13, 6.3.14, 6.4.0, 6.4.1, 6.4.2, 6.4.3, 6.4.4, 6.4.5, 6.4.6, 6.4.7, 6.4.8, 6.4.9, 6.4.10, 6.4.11
Feedback submitted, thanks!