Splunk Search

lookup several fields in one lookup command

christoffertoft
Communicator

Currently I use lookups on a new row each for several fields i want to run through the lookup, like so:

|lookup my_lookup_command word as word OUTPUT lookup_value as interesting_field1
|lookup my_lookup_command word as otherword OUTPUT lookup_value as interesting_field2

Is there any way to use the my_lookup_command to output the results of the lookup of word and otherword on one lookup call?

0 Karma
1 Solution

MonkeyK
Builder

I think that you could use mvappend to do this, no?

base search | eval searchTerm=mvappend(word, otherword) 
| lookup my_lookup_command word as searchTerm OUTPUT lookup_value as interesting_field

View solution in original post

MonkeyK
Builder

I think that you could use mvappend to do this, no?

base search | eval searchTerm=mvappend(word, otherword) 
| lookup my_lookup_command word as searchTerm OUTPUT lookup_value as interesting_field

christoffertoft
Communicator

This actually worked out - How do you split the mv field?

0 Karma

MonkeyK
Builder

christoffertoft, your original terms are still in the record, so you just need to expand the results. As deepashri_123 notes, this can be done with mvexpand:

base search | eval searchTerm=mvappend(word, otherword) 
| lookup my_lookup_command word as searchTerm OUTPUT lookup_value as interesting_field
| mvexpand interesting_field
0 Karma

christoffertoft
Communicator

The question is at this point whether my SPL "beautification" helps in the end if i have to convert from mv fields to single value on each hit. Hm, I might have to accept my fate and write several lookups

0 Karma

MonkeyK
Builder

Yeah, it is a funny thought that making the query more complex leaves it easier to read.
On the other hand, there is a cost associated with multiple lookups. I'd try racing the solutions against each other to find out if one works better than the other (very probably related to result set and lookup table size)

0 Karma

deepashri_123
Motivator

Hi christoffertoft,

You can add mvexpand after MonkeyK's query:

base search | eval searchTerm=mvappend(word, otherword)
| lookup my_lookup_command word as searchTerm OUTPUT lookup_value as interesting_field | mvexpand searchTerm | rex field=fields "(?\w+),(?\w+)"

Let me know if this helps!!!

somesoni2
SplunkTrust
SplunkTrust

No you can't match up two different fields in single lookup call, matching same lookup field, to generate two different output. If you're just trying to simplify your search by reducing number of lookup statements, you can try this foreach workaround (it still calls lookup command for each foreach field but in SPL looks leaner)

your search 
| foreach word otherword [| lookup my_lookup_command word as "<<FIELD>>" OUTPUT lookup_value as "interesting_<<FIELD>>"]

elliotproebstel
Champion

I was trying to figure this out before you posted, but I couldn't get it to work because I didn't know to add the leading pipe inside the subsearch. I know you don't need it when you are using eval inside the subsearch. Do you need it for all other commands, or is there some good references for when to use it? Thanks!

(Also, I think you have an extra "L" at in <<FIELDL>> at the end.)

0 Karma

christoffertoft
Communicator

HI, this looks like a neat way to do it, however how would you decide the output fields names?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

The output field name can be LookupFieldName_FullFieldNameFromSearch if in foreach you're passing the full field name, e.g. word, otherword will fetch output as interesting_word, interesting_otherword.
The output field name can be LookupFieldName_FullFieldNameFromSearch OR LookupFieldName_WildCardPortionofSearchField if in foreach you're passing the full field name. If your search field names are oneword and otherword and you're using | foreach *word [.... OUTPUT lookup_value as "interesting_<<MATCHSTR>>"] ,then it'll fetch output as interesting_one, interesting_other.

0 Karma
Get Updates on the Splunk Community!

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 ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...