8.4 Using Lookups in Searches

Calling a lookup from SPL

Topic 7.5 is where lookups become visible in search language. Even if you love automatic lookups, you must understand the lookup command for the User exam: syntax, match fields, and the difference between OUTPUT and OUTPUTNEW.

Core syntax pattern

... | lookup <lookup-table-name> <lookup-field> [AS <event-field>]
        [OUTPUT | OUTPUTNEW <lookup-destfield> [AS <event-destfield>] ... ]

In User scenarios, <lookup-table-name> is usually your lookup definition name (for example http_status). It can also refer to an eligible lookup file name in some cases, but definitions are the clean teaching target.

Simple worked example

Events contain status. Definition http_status uses CSV columns status, status_description, status_type.

sourcetype=access_combined
| lookup http_status status OUTPUT status_description, status_type
| table status, status_description, status_type, uri_path

What happens:

1. Search returns matching web events (with status present).
2. | lookup matches each event's status to the lookup key column status.
3. OUTPUT adds status_description and status_type from the matched row.
4. | table displays original plus enriched fields.

When event field names differ from lookup columns

Use AS to map names.

If events have http_status but the CSV key is status:

... | lookup http_status status AS http_status OUTPUT status_description, status_type

Read it as: "Use the lookup's status column, matching against the event field http_status."

You can also rename outputs:

... | lookup http_status status OUTPUT status_description AS desc, status_type AS class

OUTPUT vs OUTPUTNEW (must-know)

Memorize the exam contrast:

• OUTPUT → can overwrite
• OUTPUTNEW → keep existing values; add only when new/missing as defined by that clause's behavior

What if you omit OUTPUT / OUTPUTNEW?

If you do not specify an OUTPUT or OUTPUTNEW clause, Splunk uses the lookup's non-match fields as output fields (that is, fields in the table that were not used as match fields). That can be convenient, but it is also easier to accidentally create confusing overwrite behavior or reference-cycle problems. For clarity on the exam and in real searches, prefer an explicit OUTPUT or OUTPUTNEW list.

Using enriched fields after the lookup

Once fields exist on the results, treat them like any other field:

Filter on enrichment

sourcetype=access_combined
| lookup http_status status OUTPUT status_description, status_type
| search status_type="Server Error"

Aggregate on enrichment

sourcetype=access_combined
| lookup http_status status OUTPUT status_type
| stats count by status_type
| sort - count

Present enrichment in a table/report

... | lookup http_status status OUTPUT status_description, status_type
| table _time, status, status_description, status_type, clientip

These patterns matter because Domain 7 connects to earlier User skills: fields, stats, table, reports, and dashboards. Lookups often exist specifically so transforming commands and visualizations can group on human-friendly values.

Manual lookup vs automatic lookup in searches

• If an automatic lookup already applies for the sourcetype, enrichment fields may already be present and you might not need | lookup.
• Use manual | lookup to test a new definition, to enrich only one ad hoc search, or when no automatic lookup exists.
• If both could apply, understand overwrite rules and which fields already exist — this is exactly where OUTPUT vs OUTPUTNEW awareness helps.

Mini troubleshooting guide

Keep scope on User Domain 7

For this section, stay with:

• | lookup syntax
• match fields and AS mapping
• OUTPUT / OUTPUTNEW
• using enrichment fields downstream

You do not need to deepen into inputlookup/outputlookup administration workflows, KV Store collection design, or Power User knowledge-object ecosystems to satisfy the User blueprint's lookup objectives.

Practice drill (say it out loud)

"I match status to lookup http_status, OUTPUT the description fields, then stats count by status_type." If you can translate that sentence into SPL and explain why OUTPUT might overwrite while OUTPUTNEW preserves existing fields, you have topic 7.5 under control.

Multiple match fields (awareness)

Some lookups match on more than one column. The lookup command can accept multiple lookup/event field pairs. A User exam item is more likely to use a single key such as status or user, but you should recognize that composite matches exist when a table is keyed by two values (for example vendor code plus product code).

Putting it together in a realistic search

index=web sourcetype=access_combined earliest=-24h
| lookup http_status status OUTPUTNEW status_description, status_type
| search status_type="Client Error" OR status_type="Server Error"
| stats count by status, status_description, status_type
| sort - count

This search:

1. Limits to recent web events
2. Enriches status codes without overwriting any pre-existing description fields (OUTPUTNEW)
3. Filters to error classes using the enriched field
4. Aggregates for a readable error report

That end-to-end pattern is exactly how Domain 7 supports earlier User skills in fields, transforming commands, and reporting.

Final User exam checklist for | lookup

• Know the command name is lookup, not macro/tag/datamodel syntax
• Know match fields and optional AS renaming
• Know OUTPUT vs OUTPUTNEW
• Know enriched fields are usable immediately in | search, | stats, | table, and visualizations
• Know manual | lookup still matters even when automatic lookups exist