Use field lookups
This documentation does not apply to the most recent version of Splunk. Click here for the latest version.
Contents
Use field lookups
The last topic walked you through using a subsearch. If you're not familiar with it, go back and review how to Use a subsearch.
This topic walks you through using field lookups.
| What are field lookups?
Field lookups enable you to reference fields in an external CSV file that match fields in your event data. Using this match, you can enrich your event data by adding more meaningful information and searchable fields to them. For an example that shows you how to use field lookups to add HTTP status code descriptions to your Web access event data, see this User manual topic. |
In the previous example, you created a report table that listed how many items the top purchasing customer bought and which items they were. The items were listed by a product ID number that, on it's own, is pretty meaningless because you don't know what it refers to. Before you show this report to your boss and coworkers, you want to add the actual product name. This field doesn't exist in your data, so you need to use an external file.
To proceed, download and uncompress this CSV file: product_lookup.csv.zip
Find the Lookups manager
1. In the Splunk navigation menus, on the upper right corner, click on Manager.
This takes you to Splunk's Manager view.
2. Under Apps and knowledge, click Lookups.
This takes you to the Manager > Lookups view.
Upload the lookup file
In the Manager > Lookups view:
1. Under Actions for Lookup table files, click Add New.
This takes you to the Manager > Lookups > Lookup table files view where you upload CSV files to use in your definitions for field lookups.
2. Leave the Destination app as search.
This tells Splunk to save your lookup table file in the Search app.
3. Under Upload a lookup file, browse for the CSV file (product_lookup.csv) to upload.
4. Under Destination filename, name the file product_lookup.csv.
This will be the name you use to refer to the file in a lookup definition.
5. Click Save.
This uploads your lookup file to Splunk to the Search app, but now you need to define the type of lookup you want to set up.
6. Return to Manager > Lookups by clicking the breadcrumb:
Define the field lookup
In the Manager > Lookups view:
1. Under Actions for Lookup definitions, click Add New.
This takes you to the Manager > Lookups > Lookup table files view where you define your field lookup.
2. Leave the Destination app as search.
3. Name your lookup product_lookup.
4. Under Type, select File-based.
5. Under Lookup file, select product_lookup (the name of your lookup table).
6. Leave Configure time-based lookup and Advanced options unchecked.
7. Click Save.
Now Splunk knows that product_lookup is a file-based lookup.
Make the lookup automatic
In the Manager > Lookups view:
1. Under Actions for Automatic lookups, click Add New.
This takes you to the Manager > Lookups > Automatic lookups >> Add New view where you configure the lookup to run automatically.
2. Leave the Destination app as search.
3. Name your automatic lookup product_lookup.
4. Under Lookup table, select product_lookup.
5. Under Apply to and named, select sourcetype and type in access_combined_wcookie.
6. Under Lookup input fields type in:
The input field is the field in your event data that you are using to match the field in the lookup table.
7. Under Lookup output fields, type in the following. Use the Add another field link to add more fields after the first one:
The output fields are the field(s) in the lookup table that you want to add to your event data based on the input field matching. Here, you are adding the fields: price, which contains the price for each product_id, and product_name, which contains the descriptive name for each product_id.
8. Leave Overwrite field values unchecked.
If you check this box, Splunk will overwrite any fields that exist in your event data with values from the corresponding field that you map to it from the lookup table. Since you are adding two new fields, you don't need to worry about this option.
9. Click Save.
Return to the Search dashboard (click << Back to Search) and run the search for Web access activity over the time range, Yesterday:
sourcetype=access_*When you scroll through the Fields menu or Fields picker, you should see the new fields that you added.
Search examples
Now you can run the previous subsearch example to see what the VIP customer bought. This time, replace the product_id field with the more readable product_name:
sourcetype=access_* action=purchase [search sourcetype=access_* action=purchase | top limit=1 clientip | table clientip] | stats count, values(product_name) AS product_name by clientip | sort - count | rename count AS "How much did he buy?", product_name AS "What did he buy?", clientip AS "VIP Customer"The result is exactly the same as in the previous subsearch example, except that the VIP customer's purchases are more meaningful.
Save the search as "VIP customer".
When you're ready, proceed to the next topic where you will run more searches.
This documentation applies to the following versions of Splunk: 4.2 , 4.2.1 , 4.2.2 , 4.2.3 , 4.2.4 , 4.2.5 View the Article History for its revisions.
Comments
Try and unzip the file first, then point to the .CSV file directly, as opposed to the .zip file.
The ZIP file containing the CSV is corrupted (I am running Windows XP)
Why not provide product_lookup.csv.gz directly? I see a 29% to 33% file size reduction, depending upon if the file name is embedded in the gzip or not. Less bandwidth, and easier end-user experience.