Splunk Search

How to use a lookup table in a Splunk query?

Path Finder

I have a lookup excel sheet with the application name, hostname, and IP address. I want to use it in a Splunk query and how shall I do it?

0 Karma

You should save your excel spreadsheet as a csv (comma-separated values) file, making sure to follow these guidelines:

  1. The table in the CSV file should have at least two columns. One column represents a field with a set of values that includes values 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, which is a multivalued field.
  2. The characters in the CSV file must be plain ASCII text and valid UTF-8 characters. Non-UTF-8 characters are not supported.
  3. CSV files cannot have "\r" line endings (OSX 9 or earlier)
  4. CSV files cannot have header rows that exceed 4096 characters.

Source: http://docs.splunk.com/Documentation/Splunk/7.1.0/Knowledge/Usefieldlookupstoaddinformationtoyoureve...

Next, you'll need to upload the csv file to Splunk. You can do this by following these steps:

  1. Select Settings > Lookups to go to the Lookups manager page.
  2. In the Actions column, click Add new next to Lookup table files.
  3. Select a Destination app from the list. Your lookup table file is saved in the directory where the application resides. For example: $SPLUNK_HOME/etc/users///lookups/.
  4. Click Choose File to look for the CSV file to upload.
  5. Enter the destination filename. This is the name the lookup table file will have on the Splunk server. If you are uploading a gzipped CSV file, enter a filename ending in ".gz". If you are uploading a plaintext CSV file, use a filename ending in ".csv".
  6. Click Save.

Source: http://docs.splunk.com/Documentation/Splunk/7.1.0/Knowledge/Usefieldlookupstoaddinformationtoyoureve...

After the file is in Splunk, you should create a lookup definition. The details for that are here:

Once the lookup is properly defined, you can use these commands for interacting with it:
lookup - to consult the contents of the lookup file and use fields from the lookup to enrich your event data
inputlookup - to display the contents of the lookup file
outputlookup - to append to the lookup file or replace its contents entirely

Splunk Employee
Splunk Employee

I suggest you go through the Search Tutorial from the beginning. It includes a step for enriching data with a CSV lookup file.

0 Karma


You wanna read this chapter of the docs.

Hope that helps - if it does I'd be happy if you would upvote/accept this answer, so others could profit from it. 🙂

0 Karma