I85PREP
⌘ K
Splunk Core Certified User study materials
Splunk Core Certified User study resources

The Lookup That Went Stale on One Search Head

Splunk Core Certified User · Put your knowledge to work in context.

All case studies

The Lookup That Went Stale on One Search Head

Ravi owns product_catalog.csv in the Sales app. It maps product_id to product_name and list_price, and his search is index=sales sourcetype=orders | lookup product_catalog product_id OUTPUT product_name list_price. After a catalog refresh, about 12% of rows show a blank product_name. A nightly script also writes a refreshed CSV to the same path on each search head, but analysts still see last month's prices in the morning. There are two search heads behind a load balancer, and the same analyst gets different results on consecutive refreshes.

1. Give three distinct causes for the blank `product_name` and describe the fastest single test that separates them.

Read the worked answer

Blank output from a lookup means 'no matching row' or 'no such output field' - three distinct causes:

  1. Value mismatch on the key - case difference (SKU-1042 vs sku-1042), leading/trailing whitespace, or type formatting (integer 1042 vs string 1042). Lookup matching is case-sensitive by default (case_sensitive_match = true on the lookup definition).
  2. Missing rows - new products simply are not in the catalog yet, so nothing matches.
  3. Definition/header problem - the CSV column is named Product Name or NAME while the lookup definition outputs product_name, so the match succeeds but the output field is empty.

Fastest separating test: take one failing product_id and run

| inputlookup product_catalog where product_id="<value>"
  • Row returned, but the search still shows blank → definition/header or case/space mismatch (test | inputlookup product_catalog | eval k=lower(trim(product_id))).
  • No row returned → the row is genuinely missing, or the file is not the one Splunk is reading.

Run it on both search heads; if the answers differ, the CSV versions differ and the cause is replication, not matching.

2. Why do the refreshed prices not appear, given that the script writes the file every night? Explain what Splunk does and does not do with a CSV lookup at search time.

Read the worked answer

A CSV lookup is read at search time, by the search head handling that search. Splunk does not push the file to the indexers, does not transform it into indexed data, and does not guarantee that every search head has the same copy.

That produces exactly Ravi's symptom:

  • The script writes the file to a path on each search head, but if the lookup definition resolves to a different app directory (for example $SPLUNK_HOME/etc/apps/sales/lookups/ versus the file the script writes into search/lookups/), the update is invisible. The definition points at one file; the script updates another.
  • If a configuration bundle is deployed or replicated and overwrites lookups/, the script's edit can be reverted.
  • With two search heads behind a load balancer, whichever head serves the request determines which version Ravi sees - hence 'different results on consecutive refreshes.'

Diagnose by running | inputlookup product_catalog | head 5 against each search head directly and comparing a price that changed. The fix is process, not query: have one authoritative copy, update it through a controlled mechanism (deployer/app bundle or a shared lookup-editing endpoint), verify both heads, and never hand-edit on one member.

3. Ravi wants the lookup to survive messy keys. What can he change, and what is the risk?

Read the worked answer

Two layers:

  • In the lookup definition (transforms.conf), set case_sensitive_match = false. This makes matching case-insensitive without changing the search.
  • In the search, normalize both sides before matching: | eval product_id = lower(trim(product_id)) before the lookup, and ensure the catalog's key column is written normalized too. Case-insensitivity settings do not trim whitespace, so SKU-1042 with a trailing space will still miss - normalization in eval or at catalog-generation time is required for that.

Risks to state explicitly:

  • Turning off case sensitivity can create false matches when two legitimately different IDs differ only by case; if both exist in the catalog, the first match wins, which is worse than a blank.
  • Normalizing at search time on every event adds per-event eval cost on large result sets; normalizing the catalog once is cheaper.
  • lower() on a large multivalue or malformed field can produce surprising keys.

Safer design: fix the writer so the key is always normalized (trimmed, one case), keep case-sensitive matching, and add a scheduled check that reports | inputlookup product_catalog | where isnull(product_name) style gaps and orphan IDs so bad keys are found before analysts do.