Splunk Search

How to get multiple lookups to work in a single search?

tmarlette
Motivator

So I am attempting to perform two lookups in a single query, and i'm receiving an error. if I remove the second lookup (doesn't matter which one) then my search returns results normally.

Q: is there a way to get multiple lookups to occur in a single query?

This is my Search:

index=my_index sourcetype="iis" sc_status=2* |lookup status_codes.csv status AS sc_status | lookup my_lookup2.csv field2 AS host | stats count by status_description | rename status_description AS "Status Description"

The error that is returned is as follows:

<hostname> Streamed search execute failed because: Error in 'lookup' command: The lookup table 'my_lookup2.csv' does not exist.

I know the lookup table exists because I can see it on the machine in both the UI and the backend, and it's shared globally. Also, if I attempt to query 'my_lookup2.csv' in a search using it as the only lookup table, then the search works just fine.

Is this possible, or a limitation of splunk?

Tags (2)
0 Karma
1 Solution

tmarlette
Motivator

I figured this one out. This is the query in working form:

index="my_index" sourcetype="iis" sc_status=2* | localop | lookup status_codes.csv status AS sc_status | lookup myLookup.csv nt_host AS host | search "Field 1"="Value1" | stats count

View solution in original post

jrindfleisch
Observer

There can be a number of reasons why you get this message.
check permissions on both the lookup file and definition knowledge objects.

If you  use the OUTPUT field, any misspellings will cause it to fail.

 

I hope that helped

0 Karma

tmarlette
Motivator

I figured this one out. This is the query in working form:

index="my_index" sourcetype="iis" sc_status=2* | localop | lookup status_codes.csv status AS sc_status | lookup myLookup.csv nt_host AS host | search "Field 1"="Value1" | stats count

ppuru
Path Finder

How execution of this SPL (that contains two lookup commands) from local search head and not from the search peers resolve the issue?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Un-related, your query is not using any fields from 2nd lookup, so do you really need that?
Also, its recommended to perform the lookup further to the right of the search (towards end), preferably after some aggregation. So give this a try

index=my_index sourcetype="iis" sc_status=2* | stats count by host, sc_status | lookup status_codes.csv status AS sc_status | lookup my_lookup2.csv field2 AS host | stats sum(count) as count by status_description | rename status_description AS "Status Description"
0 Karma

tmarlette
Motivator

No go unfortunately, this didn't get me what I was looking for.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...