Archive

lookup several fields in one lookup command

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

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

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

Communicator

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

0 Karma

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

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

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

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

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>>"]

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

Communicator

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

0 Karma

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