LOOKUPS – LOOKUP TABLE FILES ( PART – 1 )

LOOKUPS – LOOKUP TABLE FILES ( PART – 1 )

A lookup table or file is one of the most important portions in Splunk, which is mainly use for mapping of fields and field-values. Splunk Lookup helps us in adding a complete new field,  from an external source based on the value that matches your field in the event data. Basically it enriches our data by adding some externals data.

Let’s take an example, Suppose you have “emp_name” and “age” in your index but you don’t have “country” name of all employees, but you have a csv file where all the “country” and “emp_name” are stored, so in that case you can use that csv file as lookup file to add “country” in your Splunk result where we will use “emp_name” field as a mapping field because that is the common field we have in both the places.

Creating a lookup file in Splunk:

At first we will start with creating a lookup file in Splunk. Suppose we have a csv file which consist two field, “status_code” and “status_information” it’s basically giving us all HTTP response status codes like 402 is for “Payment Required”, 403 is for “Forbidden” and 404 is for “Page Not Found” etc.

Log in to your Splunk instance with your credentials.

1

Then go to the Settings and click on Lookups

2

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

3

4

Then it will open the dialog box to upload the lookup file. Fill the all mandatory fields as shown.

Destination app : <app name>
Upload a lookup file : <select the file from your system which you want to upload>
Destination filename : <name of the lookup file which will be saved as by that name in Splunk>

And Save it.

5

In this way you can upload any lookup file in Splunk.

You can also know about :  How to Create Calculated Fields in Splunk

Important Commands and functions to interact with lookups in Splunk:

  1. InputlookupTo read a lookup file or to see the contents of a lookup file.

Syntax: | inputlookup [append=<bool>] [start=<int>] [max=<int>] [<filename> | <tablename>] [WHERE <search-query>]

NOTE: BOLDS are only required arguments

<filename> or <tablename> - Name of the lookup file which you want to read. This is the only required argument in this command.

Example:

| inputlookup status_code.csv

6

Result:

7

Explanation:

As you know in the previous step we uploaded a lookup file name “status_code.csv”, by using the “inputlookup” command we are viewing the content of that lookup file as simply as you see.

  1. Lookup: Use to add fields from the lookup file file into your search result.

Syntax: ……| lookup [local=<bool>] [update=<bool>] <lookup-table-name> ( <lookup-field> [AS <event-field>] )… [ OUTPUT | OUTPUTNEW (<lookup-destfield> [AS <event-destfield>] )… ]

NOTE: BOLDS are only required arguments

<lookup-table-name> - Name of the lookup file which you want to read. This is the only required argument in this command.
<lookup-field> - Field name of lookup file which will be used to map with event data.
<event-field> - Field name of event data which will be used to map with lookup file.
<lookup-destfield> - Field names of the lookup file which will add with event search result. One can specify multiple <lookup-destfield>.
<event-destfield> - A field in the events. One can specify multiple <event-destfield>.

Example:

index=_internal sourcetype=splunkd_ui_access
| stats count by status method
| lookup status_code.csv status_code as status OUTPUT status_information

Result:

8

9

Explanation:

In the above query “_internal” is the index and “splunkd_ui_access” is the sourcetype. With the “stats” command we have used two fields “method” and “status” .

Then using lookup command we fetched data from lookup file “status_code.csv” and we used “status_code” (which is same as “status” field in our event data) as a mapping field with “status” then using “OUTPUT” clause we added an extra field from lookup file to our event search result i.e “status_information”.

You can also know about :  How To Find The Missing Values Of A Field By Comparing A Lookup File

Now if you check the lookup file then we will notice that status_code 200 is for “OK”, 401 is for “Unauthorized” and 404 is for “Not Found” etc. which is the same as reflected in the above figure.

OUTPUT and OUTPUTNEW clauses:

If you look carefully at the syntax of the lookup command then you will find clauses as OUTPUT and OUTPUTNEW, now what are these for?

These are basically for mentioning the output lookup fields which you want to add with your event search result. If an OUTPUT or OUTPUTNEW clause is not specified, then all of the fields in the lookup table that are not the match field are used as output fields.

Now what are the differences between these two clauses?

OUTPUT OUTPUTNEW
Overwrite the event data with lookup file data  Put the lookup file data if event data is missing. Doesn’t overwrite any event data

Let’s take an example then.

Example:

index=_internal sourcetype=splunkd_ui_access
|eval status_information=case(status=200,"FINE",status=204,"NOPE",0=0,NULL)
|table status method status_information
|dedup status

Explanation:

In the above query “_internal” is the index and “splunkd_ui_access” is the sourcetype. Now here we have created a field called “status_information” using the eval command, which only contains information of status 200 and 204, rest are NULL. Then using table command we provided a tabular view of status, method and status_information fields. Now after that if we use lookup to fetch all the “status_information” from the lookup then what will happen?

With OUTPUT clause:

index=_internal sourcetype=splunkd_ui_access 
|eval status_information=case(status=200,"FINE",status=204,"NOPE",0=0,NULL)
|table status method status_information 
|dedup status 
| lookup status_code.csv status_code as status OUTPUT status_information

11

12

Explanation:

Because we have used the OUTPUT clause here that’s why indexed data are overwritten by lookup file data. That’s why we are getting the same data as stored in the lookup file.

With OUTPUTNEW clause:

index=_internal sourcetype=splunkd_ui_access 
| eval status_information=case(status=200,"FINE",status=204,"NOPE",0=0,NULL)
| table status method status_information 
| dedup status 
| lookup status_code.csv status_code as status OUTPUTNEW status_information

13

14

Explanation:

Now you can see the differences, here we have used OUTPUTNEW clause that’s why lookup file data are overwritten by indexed data. For “status_code200 and 204 information was already there in our event (we earlier created using eval command), that’s why by using OUTPUTNEW only missing data will be reflected in the result.

  1. Outputlookup: With this command we can save any of our search results as a lookup file.
You can also know about :  How to Create Field Aliases in Splunk

Syntax: …..| outputlookup [append=<bool>] [create_empty=<bool>] [override_if_empty=<bool>] [max=<int>] [key_field=<field>] [createinapp=<bool>] [output_format=<string>] <filename> | <tablename>

NOTE: BOLDS are only required arguments

<filename> - The name of the lookup file by which name you want to save in splunk.
<tablename> -  The name of the lookup table by which name you want to save in splunk.

Example:

index="test_index" sourcetype="csv"
| table Rank,Peak,Title,"Worldwide gross",Year
| sort - "Worldwide gross"
| outputlookup film_grossing.csv

15

16

Explanation:

We took a sample data from the index “test_index” and “csv” sourcetype and using table command we created a table with some fields,  and we have also used sort command for sorting the value in proper order. Now I want to save that result as a lookup file for that only we can use the outputlookup command.

If you want to cross check it, saved or not either you can type |inputlookup film_grossing.csv or you can go to the Settings > Lookups > Lookup table files, then we will find that in the list.

17

Hope you have understood this topic on Lookups – Lookup Table Files ( Part – 1 )

Lookups – Lookup Definition and Automatic Lookup ( Part – 2 )

Happy Splunking!!

3 comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.