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
Revered Legend

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
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Community Content Calendar, September edition

Welcome to another insightful post from our Community Content Calendar! We're thrilled to continue bringing ...

Splunkbase Unveils New App Listing Management Public Preview

Splunkbase Unveils New App Listing Management Public PreviewWe're thrilled to announce the public preview of ...

Leveraging Automated Threat Analysis Across the Splunk Ecosystem

Are you leveraging automation to its fullest potential in your threat detection strategy?Our upcoming Security ...