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

[tmarlette]

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?

[jrindfleisch]

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

[tmarlette · Accepted solution]

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]

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

[somesoni2]

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"

[tmarlette]

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