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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...