Lookups – Lookup table files Splunk

Lookups – lookup table files splunk

One of the most important portions in slunk is the lookup file or table that’s majorly used for mapping field values and fields. Moreover, Splunk lookup assists us in totaling a full new field, from an external source based on the value which suits your field in event data. Essentially it enhances our data by adding externals data.

Here is an example, suppose you’ve “emp_name” & “age” in an index but you lack the “country” name of every employee, though you have a “CSV file” where every “country” & “emp_name” are kept, so in this case, you will use the CSV-file as lookup-file to increase “country” in Splunk result and “emp_name” field will be used as mapping field. This is because it’s a common field we’ve in both places.

Creating a lookup file in Splunk

To begin with, you need to start with creating a lookup-file in Splunk. Suppose a CSV file that consists of 2 fields, “status_information” & “status_code” is available. This will give us every HTTP response status code similar to 402 is for “Payment required”, 404 is for “Page Not Found”, and 403 is for “Forbidden” etc.

Log in to Splunk instance using your credentials.

 

Move to “Settings” & click on “Lookups”

 

Then click on Lookup table files & New Lookup Table file.

 

After that, a dialog box for uploading the lookup file will be opened. Fill every displayed mandatory field.

 After that, you need to save it.

 

This way you will upload a lookup file with Splunk.

Important Functions & Commands to interact with lookups in Splunk

1. Inputlookup. It’s used to see contents available in a lookup file or read a lookup file.

 

Note that those in bold are the required arguments only

 

Example

 

 

Result

 

Explanation

As seen in the earlier step where a lookup file name was uploaded “status_code.csv”, with the help of the “input lookup” command. You will see the content of that lookup file as displayed.

2. Lookup. It’s used to add fields from the lookup file into a search result

 

Those in bolds are the only needed arguments

 

Example Outcomes

  Explanations

In query above “_internal” is index & “splunkd_ui_access” is sourcetype. With the “stats” command we’ve used 2 fields “status” and “method”.

Then utilizing the lookup command we get data from a lookup file “status_code.csv” & we used “status_code”. This is similar to the “status” field in event data as a mapping field with “status” then utilizing the “OUTPUT” clause. An extra field is added from a lookup file to the event search result including “status_information”.

Now if one checks a lookup file then they will notice status_code 200 represents “OK”, 404 represents “Not Found”, 401 represents “Unauthorized” etc. This is similar to what is reflected in the above image.

OUTPUTNEW and OUTPUT clauses

The syntax of this lookup command has its clauses as OUTPUTNEW & OUTPUT. OUTPUTNEW and OUTPUT clauses are essentially for mentioning output lookup fields that you need to add using your event search outcomes. If the OUTPUTNEW or OUTPUT clause isn’t specified, then every field in a lookup table that isn’t the match field is utilized as an output field.

Differences between OUTPUT & OUTPUTNEW clauses

With OUTPUT clause you will overwrite the event data using lookup file data. Moreover, with OUTPUTNEW clause, you will put lookup file data when event data is missing. This does not overwrite every event data.

Example

 

Explanations

With the query above “_internal” is an index & “splunkd_ui_access” is a sourcetype. With this example, a field is known as “status_information” is created using the eval command. This contains info of status 204 & 200, the rest are NULL. Using table command offered a tabular view of method, status & status_information fields. Presently, if lookup is used to fetch every “status_information” from lookup then what can happen?

With OUTPUT clause

 

  Explanation

Because the OUTPUT clause is used here that is why indexed information is overwritten using lookup file information. That is why we’re getting similar data as kept in a lookup file.

With OUTPUTNEW clause

   Explanation

In this example, the OUTPUTNEW clause is used and that’s the reason lookup file data are overwritten using indexed data. With “status_code “204 & 200 information was present in our event created earlier using the eval command. That is why using just OUTPUTNEW clause just missing data in the result will be reflected.

3. Outputlookup. With this command, you will save any of your search outcomes as a lookup file.

 Those in bold BOLDS are required arguments

 

Example

 

 

 

Explanations

Sample data from the “CSV” sourcetype and the index “test_index” are taken. A table with fields is created using the table command and also sort command is used for sorting the value in suitable order. Using the outputlookup command, you can save the outcomes as a lookup file.

If one needs to save, cross-check it, or either they can type |inputlookup film_grossing.csv. Moreover, one can move to Settings > Lookups > Lookup table files and we will get that in a list.