SPLK-1001 Creating and Using Lookups

Creating and Using Lookups Detailed Explanation

7.1 What is a Lookup?

Definition

• A lookup in Splunk is a feature that allows you to enrich your indexed data by cross-referencing it with external datasets.
• These external datasets can be stored in formats like:
  • CSV Files: Simple tabular data files.
  • KV Stores: Key-value pair stores managed within Splunk.

Purpose of Lookups

• Lookups allow you to:
  1. Add Descriptive Labels: Replace cryptic field values with meaningful labels for easier analysis.
     • Example:
       • Instead of showing user_id=101, you display user_name=Alice.
  2. Correlate Data: Combine event data with external information.
     • Example:
       • Enrich logs with customer details or product descriptions.
  3. Standardize Fields: Normalize data from various sources for consistent reporting.

Common Use Cases

1. User Identification:

   • Map user_id to user_name from a lookup file.

   • Example CSV:

     user_id,user_name
     101,Alice
     102,Bob
     

2. Geolocation Data:

   • Convert ip_address to geographical locations like city or country.

3. Category Mapping:

   • Map error codes or product IDs to human-readable descriptions.

7.2 Steps to Configure a Lookup

To use lookups in Splunk, follow these four key steps:

Step 1: Upload the Lookup File

1. Navigate to the Lookup Settings:
   • Go to Settings > Lookups > Lookup Table Files.
2. Upload the Lookup File:
   • Click Add New.
   • Choose the file to upload (e.g., users.csv).
   • Provide a name for the lookup table (e.g., users.csv).
3. Verify the Upload:
   • Check the uploaded file to ensure it contains the expected data.

Step 2: Define the Lookup

1. Go to Lookup Definitions:
   • Navigate to Settings > Lookups > Lookup Definitions.
2. Create a New Lookup Definition:
   • Click Add New.
   • Select the uploaded file (e.g., users.csv).
   • Provide a meaningful name for the lookup (e.g., user_lookup).
   • Configure matching options:
     • Case Sensitivity: Should field values match case exactly?
     • Advanced Options: Configure field transformations or fallback behaviors.

Step 3: Apply the Lookup in Searches

• Once the lookup is defined, you can reference it in your SPL queries using the lookup command.

Basic Syntax:

<search criteria> | lookup <lookup_table> <lookup_field> OUTPUT <output_field>

Example:

index=main | lookup users.csv user_id OUTPUT user_name

• Explanation:
  • users.csv: Name of the lookup table.
  • user_id: Field in your Splunk data to match against the lookup table.
  • OUTPUT user_name: Field to enrich your Splunk data with.

Example Data:

• Lookup File (users.csv):

  user_id,user_name
  101,Alice
  102,Bob
  

• Splunk Query Results:

  _time       user_id  user_name
  2025-01-01  101      Alice
  2025-01-01  102      Bob
  

Step 4: Configure Automatic Lookups

Instead of manually applying lookups in each search, you can configure automatic lookups. These will enrich data as it’s ingested or queried.

1. Navigate to Automatic Lookups:
   • Go to Settings > Lookups > Automatic Lookups.
2. Add a New Automatic Lookup:
   • Select the lookup definition (e.g., user_lookup).
   • Specify which index or sourcetype to apply the lookup to.
3. Save the Configuration:
   • Once configured, the lookup is applied automatically to matching data.

7.3 Troubleshooting Lookups

Common Issues

1. File Format Errors:

   • Ensure the lookup file is properly formatted as a CSV.
   • Common issues include:
     • Missing headers.
     • Extra blank lines.

2. Mismatched Field Names:

   • The field in your Splunk data must exactly match the field name in the lookup table.
   • Example:
     • If your lookup table uses user_id but your data has userid, the fields won’t match.

Validating Lookups

• Use the inputlookup command to inspect the lookup table directly.

• Example:

  | inputlookup users.csv
  

• Output:

  user_id,user_name
  101,Alice
  102,Bob
  

Debugging with Logs

1. Check Splunk Logs:

   • Errors with lookups are often logged in splunkd.log.

   • Search the logs for relevant errors:

     index=_internal sourcetype=splunkd lookup
     

2. Test with Simpler Data:

   • Create a small lookup file with a few rows to test functionality.

   • Example:

     id,name
     1,TestUser
     

Creating and Using Lookups (Additional Content)

1. Difference Between inputlookup and lookup

While both commands interact with lookup tables, they serve very different purposes:

Command	Purpose	Modifies Event Data?
inputlookup	Reads and displays the entire contents of a lookup file	No
lookup	Matches fields from event data with a lookup and enriches events	Yes

Example of inputlookup

| inputlookup users.csv

• Retrieves all rows from users.csv.

• Used primarily for:

  • Previewing the contents of a lookup table.

  • Performing standalone queries without event context.

Example of lookup

index=main | lookup users.csv user_id OUTPUT user_name

• Enriches the events from the main index.

• Uses user_id to find a match in the lookup table and adds user_name to the events.

2. OUTPUT vs. OUTPUTNEW

These are options used with the lookup command to control field merging behavior.

Option	Behavior
OUTPUT	Overwrites existing fields in the event (if the field already exists)
OUTPUTNEW	Adds fields only if they don’t already exist in the event

Example:

| lookup users.csv user_id OUTPUTNEW user_name

• Adds user_name only if it doesn’t already exist in the event.

• Safer in cases where a field might already be populated and you want to preserve it.

Exam Tip:
Use OUTPUTNEW when you want to avoid accidental overwrites of existing data fields.

3. Case Sensitivity in Lookups

Field Name Case Sensitivity

• SPL is case-sensitive when referring to field names.

• User_ID is not the same as user_id.

Field Value Case Sensitivity

• When defining a lookup (via Lookup Definition), you can enable or disable case sensitivity.

• If case sensitivity is enabled:

  • An event with user_id=101 will match a lookup row with user_id=101

  • But it will not match a row with User_ID=101

Exam Scenario

A lookup fails even though the event and lookup table appear to match.
Correct cause: Case sensitivity mismatch between event value and lookup table value.

4. Lookup Matching Limitations

Splunk lookup operations are strictly exact-match.

Feature	Supported in Lookups?
Exact matches	Yes
Partial matches	No
Wildcards (*)	No
Regular expressions	No

Implication:

• You cannot do:

  lookup users.csv user_id OUTPUT user_name WHERE user_id=*1
  

• Instead, you must ensure full, literal equality for field matching.

5. CSV Lookup vs. KV Store Lookup

These are the two main types of lookup tables in Splunk. Understanding the differences is crucial for SPLK-1001.

Feature	CSV Lookup	KV Store Lookup
Storage Format	Flat .csv file	NoSQL-style key-value store (managed by Splunk)
Editable via UI	Yes	Yes
Supports Write via SPL	No	Yes (outputlookup, inputlookup append)
Best For	Static reference data	Large or dynamic data that changes frequently
Performance	Good for small/medium datasets	Scales better for large, complex datasets

CSV Lookup:

• Use for static mappings like:

  • ID-to-name

  • Code-to-description

  • Geography tables

KV Store Lookup:

• Use for:

  • Storing data that may be updated by SPL

  • Building dynamic applications

  • Scaling to thousands or millions of entries

Example Use Case:

A company wants to enrich logs with real-time asset metadata that updates daily.
Solution: Use KV Store rather than CSV.

Key Exam Summary Table

Concept	You Should Know
inputlookup	Reads lookup data only; does not enrich events
lookup	Joins lookup data into events using exact-match field
OUTPUTNEW vs OUTPUT	OUTPUTNEW preserves existing fields; OUTPUT overwrites them
Case sensitivity	Controlled in Lookup Definition; mismatches will cause failures
Match limitations	Only exact matches are allowed; no wildcards or regex
CSV vs KV Store	CSV = static, read-only; KV = dynamic, writable, scalable