Use field lookups
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 to add new fields to your events.
| 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 Knowledge Manager 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 information doesn't exist in your data, but you can add it from an external file using field lookups.
To proceed, download and uncompress this CSV file: product_lookup.csv.zip
Important: To complete the rest of the tutorial, you have to follow the procedures in this topic. If you don't follow this topic, the searches in the following topics will not produce the correct results.
Find the Lookups manager
1. In the Splunk navigation menus, on the upper right corner, click on Manager.
This takes you to Splunk Manager, which enables you to access and configure your Splunk server's apps, knowledge objects, and other settings such as system, data, deployment, and authentication settings. If you don't see some of these options, it just means that you do not have the permissions to view or edit them.
For now, we're only interested in the Knowledge configurations.
2. Under Knowledge, click Lookups.
This takes you to the Manager > Lookups view.
This view enables you to edit existing lookups by clicking on the links in the table for Lookup table files, Lookup definitions, and Automatic lookups. If you want to add new lookups, just click Add new under actions for that lookup item.
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.
Note: Splunk does not recognize or cannot upload the file, check that it was uncompressed before you attempt to upload it again.
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 definitions 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 sidebar or Fields dialog, you should see the new fields that you added.
Search with the new lookup fields
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 this search as "VIP Customer".
Search acceleration
When you saved the "VIP Customer" search, the save dialog included a new option: Acceleration.
If your search has a large number of events and is slow to complete, you may be able to accelerate it so it completes faster when you run the search again in the future. This option is only available when your search qualifies for acceleration. This search does because it is a reporting search.
The sample data used in this tutorial is pretty limited in volume and the searches throughout are run against data for one day (Yesterday). Checking this box won't have a noticeable affect on the speed of this search and all upcoming searches you will save in this Tutorial.
Read more search acceleration and the searches that qualify in the "Save searches and share search results" topic in the Knowledge Manager manual.
Next steps
When you're ready, proceed to the next topic where you will run more searches.
This documentation applies to the following versions of Splunk: 5.0 , 5.0.1 , 5.0.2 View the Article History for its revisions.
Comments
In the section "Define the field lookup" has a fault, "Manager > Lookups > Lookup table files" should be "Manager > Lookups > Lookup definitions"
Thanks for pointing that out, we've fixed it.