How do you connect data from two different sources inside Splunk without writing a million separate searches? Advanced lookup operations solve that exact problem by letting you pull in extra information from a separate table, like a spreadsheet or a CSV file, and merge it with your search results on the fly. For the SPLK-1002 exam, understanding lookups is essential because they are the most common way to enrich raw log data with business context, such as matching an IP address to a user name or a product code to a full product description.
Jump to a section
A simple way to picture Advanced Lookup Operations
A library holds thousands of books, each with a unique barcode. When a patron returns a book, the librarian scans the barcode on the back cover. That single scan does not tell the librarian the book's title, author, genre, or which shelf it belongs on. To get all that detailed information, the librarian must look up the barcode number in the library's central cardholder file on a computer. That file is a massive table where each barcode is a row, and the columns hold the book's title, author, publication year, and shelf location. The scan of the barcode acts as the key to retrieve the rest of the data from the table.
Now, imagine the librarian wants to know not just one book's details but which books were borrowed by a specific group of patrons on the same day. The librarian could manually search the checkout records for every patron ID, then cross-reference each ID against the patron database. That is slow and error-prone. Instead, the librarian runs a special lookup command that joins the checkout records with the patron database using the patron ID as the matching key. This command instantly pulls in the names, phone numbers, and membership levels for every patron who borrowed a book that day.
The lookup operation transforms a simple list of IDs into a rich, complete report. It saves the librarian from flipping through paper files or running dozens of separate searches. This is exactly what advanced lookup operations do in Splunk: they take a raw event containing a code or identifier and enrich it with additional context from a separate table, allowing analysts to see the full story without leaving their main search window.
At its simplest, a lookup in Splunk is a way to add columns of data from an external source (like a CSV file or a database) to your search results. Think of it as a table join in a spreadsheet. When you have a log event that contains, say, a Customer ID, but you also want the Customer's full Name, Email, and Account Tier, you do not need to manually search for each ID. You create a lookup table file (a CSV) that maps every Customer ID to those details, and then use the lookup command in your search to pull them in automatically.
For the SPLK-1002 exam, there are three main types of lookups you need to know: file-based lookups, automatic lookups, and external lookups. File-based lookups use a static file (usually a CSV or KMZ) stored on the Splunk server. You define the file, specify the fields to match (the input fields) and the fields to bring in (the output fields). For example, a CSV named "user_contacts.csv" with columns for employee_id, full_name, and phone_number. When a log event contains employee_id=12345, the lookup command matches that ID and adds the full_name and phone_number to the event.
The syntax for a basic file-based lookup is:
... | lookup user_contacts employee_id OUTPUT full_name phone_number
This command says: for each event in the search pipeline, take the value in the employee_id field, look it up in the user_contacts lookup table, and if there is a match, add the full_name and phone_number fields to the event. If no match is found, the event keeps its original fields without the added ones.
Automatic lookups are a special kind of file-based lookup that runs automatically on every search without you needing to type the lookup command. You configure them in Splunk settings. They are useful for enriching data that always needs extra context. For example, if every event already contains a user_id, you can set up an automatic lookup that always adds the user's full name and department. Automatic lookups save time but can slow down searches if the lookup file is large.
External lookups are more advanced. They let Splunk query an external system, like a relational database or an API, in real time when you run a search. Instead of a static CSV file, you use a Python script or a database connection to retrieve data. For the SPLK-1002 exam, external lookups are less common, but you should know they exist and that they require additional setup (the external script and a configuration on the search head).
Why does Splunk need lookups at all? Machine data (logs) often uses codes and abbreviations to save space. A web server log might record status_code=404 without explaining that means "Page Not Found". A firewall log might record src_ip=192.168.1.10 but not the employee's name. Lookups allow you to translate these codes into human-readable information without changing the original data. This makes reports and dashboards much more useful.
When you create a lookup table file, you must define it in Splunk by going to Settings > Lookups > Lookup table files. You upload the CSV file, specifying its name and the field that contains the matching key (the "required field"). Then you create a lookup definition that links that file to a specific command name (like user_contacts). After that, you can use the lookup command in searches. For automatic lookups, you also need to configure which sourcetype or host the lookup applies to.
One important detail for the exam: the lookup table file must have a header row. The first row should contain the column names that match the field names in your events. If your CSV has a column named "employee_id" but your events use "EmpID", the lookup will fail to match. The input field name in the lookup table must exactly match the field name in your events (or you can specify a different input field name in the lookup definition).
Lookups can also perform multiple output field mappings. You can specify OUTPUT followed by one or more field names to bring in. Or you can use OUTPUTNEW to only add a value if the field does not already exist in the event. The lookup command can also be used with INPUT to change which field from the event is used for matching. For example:
... | lookup user_contacts employee_id AS EmpID OUTPUT full_name
This tells Splunk to use the employee_id field from the lookup table but match it against the EmpID field from the event. This is useful when field names differ.
For the SPLK-1002 exam, you are expected to know how to:
Create and upload a lookup table file (CSV).
Define a lookup and use it in a search with | lookup.
Understand the difference between OUTPUT and OUTPUTNEW.
Recognise when an automatic lookup is configured (it happens without the lookup command).
Format the lookup table correctly (header row, matching field names).
One exam trap: the lookup table file must be in the correct directory (typically $SPLUNK_HOME/etc/apps/search/lookups/), but you do not need to memorise the path. Instead, remember you upload it through the Splunk Web interface at Settings > Lookups > Lookup table files. Another trap: if a field name in the lookup table has spaces or special characters, you must enclose it in double quotes in the search command.
Finally, lookups are case-sensitive by default. If your CSV has "Alice" and your event has "alice", the lookup will not match unless you configure case-insensitive matching, which is not tested on the exam. So always ensure the casing matches.
Create the Lookup Table File
First, prepare a CSV file with a header row. For example, a file called 'employees.csv' with columns 'emp_id', 'full_name', and 'department'. Each row contains one employee's data. Ensure the column names are exactly the field names you will use in Splunk, or plan to use the `AS` keyword to alias them.
Upload the File to Splunk
In Splunk Web, go to Settings > Lookups > Lookup table files. Click 'Add new'. Give the file a name (like 'employee_data'), choose the app context (usually 'search'), and upload your CSV file. This makes the file available to the Splunk instance.
Create a Lookup Definition
Still in Settings > Lookups, click 'Lookup definitions' and then 'Add new'. Give the definition a name (e.g., 'employee_lookup'). Select the lookup table file you just uploaded. Optionally, you can specify the input field name from the CSV that will be used for matching. This definition is what you will call in your search.
Write a Search Using the Lookup Command
Run a search that contains a field matching your lookup key. For instance, if your web logs have a field 'user_emp_id', you can write: `index=web_logs | lookup employee_lookup emp_id AS user_emp_id OUTPUT full_name department`. This enriches each log event with the employee's name and department.
Verify the Enriched Fields
Check the search results to see that the new fields (full_name, department) appear in the events. If they show as empty or missing, double-check the field name mapping and that the CSV contains the expected keys with matching case. Also confirm the lookup definition is active (not disabled).
Optionally Convert to an Automatic Lookup
If you want the lookup to run automatically on every search for a specific sourcetype, go to Settings > Lookups > Automatic lookups. Create a new entry, specify the sourcetype (e.g., 'access_combined_wcookie'), choose the lookup definition, and map the input and output fields. Now whenever you search that sourcetype, the enrichment happens without typing the `lookup` command.
Consider a real IT help desk scenario. The help desk team at a medium-sized company uses Splunk to monitor firewall logs. Every time a user connects to a corporate VPN, the firewall generates a log entry that includes the user's internal IP address (for example, 10.0.1.45). The help desk receives a ticket from the finance department: "User with IP 10.0.1.45 cannot access the accounting server." The technician opens Splunk and runs a search for the IP address. The raw events show only the IP address, the connection time, and the port used. But the help desk needs to know: who is that user? What department do they work in? What is their desk phone number?
Without a lookup, the technician would have to open a separate employee database, copy the IP address into a different tool, find the employee name, then return to Splunk. That is time-consuming. Instead, the IT team has already created a lookup table file named "employee_ips.csv" with the following columns:
ip_address (the internal IP assigned to the employee's computer)
employee_name
department
desk_phone
The technician runs this search:
index=firewall src_ip=10.0.1.45 | lookup employee_ips.ip_address OUTPUT employee_name department desk_phone
Instantly, the search results now show not just the raw IP address but also "John Smith, Accounting, ext 4457". The technician can call John directly to troubleshoot. This simple lookup saves minutes per ticket and reduces the chance of misidentifying the user.
Now consider a more advanced use case: the security team wants to audit all IP addresses that accessed a sensitive server in the last 24 hours and produce a report showing each user's manager and their security clearance level. They have a lookup table called "user_clearance.csv" that contains:
ip_address
employee_name
manager_email
clearance_level (Confidential, Internal, Restricted)
The search:
index=firewall dest_ip=10.10.10.50 earliest=-24h | lookup user_clearance ip_address OUTPUT employee_name manager_email clearance_level
The output shows each connection along with the employee's manager and clearance level. This helps the security team quickly identify if an employee without the proper clearance tried to access the server. Without the lookup, they would have to cross-reference IP addresses manually against a separate HR database.
Another everyday scenario: a product manager for an e-commerce site wants to see which product IDs are generating the most errors on the checkout page. The error logs contain only a product code like "SKU-9876". The product manager has a CSV with product details (SKU, product name, category, price). They run:
index=checkout_errors | lookup products.csv sku OUTPUT product_name category price | stats count by product_name category
This instantly turns a list of cryptic SKU codes into a meaningful bar chart of products with the most errors. The product manager can then prioritise fixing the most popular products that are failing.
In all these cases, the lookup command replaces the manual, error-prone process of copying data from one database to another. It centralises enrichment within Splunk, making analysis faster and more accurate. For the exam, you need to be comfortable with the syntax and know that the lookup table file must exist on the Splunk instance and be properly configured under Settings > Lookups.
The most common mistake in real-world use is forgetting to upload the lookup file or having a mismatch in field names. Always double-check that the column names in the CSV exactly match the field names in your events (or use the AS keyword to alias them). Also, if the CSV file is updated frequently (like daily IP assignments), you must either re-upload the file or use a scripted input to refresh it. For the exam, you only need to know static file-based lookups.
The SPLK-1002 exam tests your ability to use the lookup command and manage lookup table files. You will not be asked to configure automatic lookups in the UI, but you will need to understand the difference between automatic and explicit lookups. The most heavily tested topics are:
The syntax of the lookup command: | lookup <lookup-name> <input-field> OUTPUT <output-fields> or | lookup <lookup-name> <input-field> AS <event-field> OUTPUT <output-fields>
The use of OUTPUT versus OUTPUTNEW: OUTPUT overwrites the event field if it already exists; OUTPUTNEW only adds the value if the event field does not exist.
The rule that the lookup table file must have a header row that matches the field names in the search.
The requirement that the lookup must be defined in Settings > Lookups before it can be used in a search.
You will likely see multiple-choice questions that ask: "Which command enriches events with data from a CSV file?" The answer is | lookup. Another common question type presents a scenario: "You have a CSV file called 'users.csv' with columns 'user_id' and 'full_name'. Your events have a field 'EmpID'. Which command correctly adds the full name?" The correct answer would use AS to rename the input field, like: | lookup users.csv user_id AS EmpID OUTPUT full_name.
Traps to watch out for:
The question might include a lookup command with the fields in the wrong order: | lookup OUTPUT full_name users.csv user_id is incorrect syntax.
The question might use OUTPUTNEW when the scenario explicitly says the field might already exist and you want to keep the original value. In that case, OUTPUTNEW is correct.
The question might include a lookup that is not defined in the configuration. The correct answer will say "First upload the CSV file and create a lookup definition."
The question might have a CSV with no header row. The correct answer will point out that the CSV must have a header row.
The question might mix up lookup with inputlookup. inputlookup is a different command that returns the entire lookup table as events, not enriching existing events. Know the difference: | inputlookup users.csv returns all rows from the CSV as search results.
Exam-tested concepts to memorise:
| lookup <lookup-definition-name> <lookup-field> OUTPUT <new-field>
| lookup <lookup-definition-name> <lookup-field> AS <event-field> OUTPUT <new-field>
| lookup <lookup-definition-name> <lookup-field> OUTPUTNEW <new-field> (only adds if field does not exist)
Automatic lookups do not require the lookup command — they happen automatically based on sourcetype or host.
The lookup table file is stored in the lookups directory of an app, but you upload it via Settings > Lookups > Lookup table files.
The lookup definition is a separate configuration that links the file to a command name.
Case sensitivity: lookup matches are case-sensitive by default.
When using AS, the keyword comes after the lookup field name: <lookup-field> AS <event-field>.
You may also see a question about "Which field is used as the key for the lookup?" The key is the input field that you specify after the lookup name. It must match the column name in the CSV (or be aliased).
Finally, be prepared to identify the correct order of operations. The lookup command is a transforming command, meaning it should come after the search pipeline has filtered events. The typical order is:
index=something | search <filter> | lookup <...> | table <...>
Do not put the lookup command before the main search filter — it will try to enrich every event, which is inefficient. The exam may present a search where the lookup is placed incorrectly, and you must recognise that it should come after the filtering.
The `lookup` command enriches search results by adding fields from an external table using a matching key.
A lookup table file must have a header row that matches the field names in your events or be aliased with `AS`.
Use `OUTPUT` to add fields to events, overwriting existing values; use `OUTPUTNEW` to only add fields if they do not already exist.
An automatic lookup runs on every search for a specified sourcetype or host without needing the `lookup` command in the search.
The `lookup` command is case-sensitive by default, so matching keys must have identical casing.
You must define the lookup table file and then create a lookup definition in Splunk Web before you can use it in a search.
The `inputlookup` command returns the entire contents of a lookup table as events, while `lookup` enriches existing events.
A failed lookup match does not remove events from the results; it leaves new fields as null.
These come up on the exam all the time. Here's how to tell them apart.
lookup command
Enriches existing events in a search pipeline.
Requires a matching key field in the event.
Adds new fields to events but does not remove any original ones.
inputlookup command
Returns the entire lookup table as search results.
Does not require any existing events; it is its own data source.
Useful for examining the contents of a lookup table or using it as a filter.
OUTPUT option
Adds fields to events, overwriting any existing fields with the same name.
Used when you want to ensure the latest data from the lookup is always shown.
If the event already has the field, the lookup value replaces it.
OUTPUTNEW option
Adds fields to events only if the field does not already exist in the event.
Used when you want to preserve the original value in the event.
If the event already has the field, the lookup value is ignored.
File-based lookup (CSV)
Uses a static file (CSV) stored on the Splunk server.
Must be manually updated or replaced when data changes.
Fast and simple; ideal for small to medium datasets.
External lookup (database/script)
Queries an external system (database, API) in real time during the search.
Data is always current from the source system.
Slower due to network calls; requires a script or connection setup.
Mistake
A lookup table file must have the exact same name as the lookup command you type in the search.
Correct
The lookup command uses the name of the lookup definition, not the filename. You define the definition in Settings, and it points to the CSV file. The command name can be different from the filename.
Beginners see tutorials using `| lookup myfile.csv` and think the filename is the command name, but Splunk separates the definition from the file.
Mistake
You can change the data in a lookup table by running a search that uses the `outputlookup` command without first creating the CSV file.
Correct
`outputlookup` creates or overwrites a lookup table file. But the file must already exist or be created as a new lookup table file through the UI. If you use `outputlookup` on a file that is not defined, the search will fail.
People assume `outputlookup` works like a save-to-file command in any tool, but Splunk requires the lookup definition to exist beforehand.
Mistake
The `lookup` command can only work with CSV files.
Correct
The `lookup` command can work with CSV, TSV, and KMZ (geospatial) files, as well as external lookups that query databases or APIs.
Beginners only see CSV examples in documentation and assume that is the only format, but Splunk supports other delimited files and geospatial data.
Mistake
If a lookup table does not contain a value for a particular key, the event is removed from the search results.
Correct
When a lookup does not find a match, the event remains in the results, but the output fields are left empty (null). The event is not dropped.
This misconception comes from confusing `lookup` with `join` or from other databases where a failed join might discard rows. Splunk by default keeps all events.
Mistake
You can use the `lookup` command to update the original lookup table file while the search is running.
Correct
The `lookup` command is read-only. It reads the CSV file and enriches events but never modifies the file. To update a lookup file, you use `outputlookup` separately.
Some beginners assume the lookup is a two-way street, but it is a one-way enrichment from the static file into the search.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
`lookup` enriches existing search results by adding fields from a lookup table. `inputlookup` returns all rows from the lookup table as search results, which you can then filter or manipulate like any other events.
Yes, if you modify the CSV file on disk, you must re-upload it through the Splunk Web interface or place the new file in the correct lookups directory and refresh the lookup. Splunk does not automatically detect changes to the file.
Yes, but large lookups can slow down searches significantly. For the exam, you only need to work with small CSV files (hundreds of rows). In real life, consider using external lookups or optimising the table with indexed fields.
If multiple rows have the same key, Splunk returns only the first match it finds. Duplicate keys can cause unpredictable results, so ensure your lookup table has unique keys.
Splunk does not directly read Excel files. You must first export the Excel sheet to a CSV file and then upload it. KMZ files are supported for geospatial lookups, but for the exam, stick with CSV.
Yes, `| outputlookup my_new_lookup.csv` will take the current search results and write them to a CSV file that can then be used as a lookup table. You must have defined the lookup in Settings first.
You've finished Advanced Lookup Operations. Continue through the SPLK-1002 study guide to build a complete picture of the exam.
Done with this chapter?