How to use a column of field names FROM MVFIELD to dynamically select fields for use in eval expression?

[dpuhr]

Hi,
I am looking for a solution to a problem that has been addressed here:

Using a column of field names to dynamically select fields for use in eval expression 

but with this difference:

Original solution was:

| makeresults 
| eval raw="x1 y1 z1 field1 x1::x2 y2 z2 field3 z2::x3 y3 z3 field2 y3" 
| makemv delim="::" raw 
| mvexpand raw 
| rex field=raw "(?<field1>\S+)\s+(?<field2>\S+)\s+(?<field3>\S+)\s+(?<lookup>\S+)\s+(?<expected_result>\S+)"
| fields - raw _time
| rename COMMENT AS "Everything above fakes sample data; everything below is your solution."
| eval result="N/A"
| foreach field* [eval result=if(lookup="<<FIELD>>", $<<FIELD>>$, result)]

In the  | foreach... command is used field* as an set of input fields. But in my case the set of input fields cannot be described by wildcard, there is lot of field names in my input "list". 

I have decided to create multivalue field with all values in lookup column:

| eventstats values(lookup) as mv_lookup

That created mv field mv_lookup I want to use as input for | foreach... command.

| foreach mode=multivalue mv_lookup [eval result=if(lookup="<<FIELD>>", $<<FIELD>>$, result)]

I guess, if foreach command input is MV field, i have to use <<ITEM>> instead of <<FIELD>> and that is the reason for no match in lookup="<<FIELD>>" 
Does exist any way how to use MV list of values (names of fields) to perform requested lookup?

Thanks in advance.

David

[ITWhisperer]

Not sure how multivalue helps - try something like this (depending on your actual field names and the quality of your actual data, you may need some quotes etc. to deal with non-alphanumerics in field names, and other exception handling)

| makeresults 
| eval raw="w1 x1 y1 z1 field2 x1::w2 x2 y2 z2 field4 z2::w3 x3 y3 z3 field3 y3" 
| makemv delim="::" raw 
| mvexpand raw 
| rex field=raw "(?<field1>\S+)\s+(?<field2>\S+)\s+(?<field3>\S+)\s+(?<field4>\S+)\s+(?<lookup>\S+)\s+(?<expected_result>\S+)"
| fields - raw _time
| eval lookup__{lookup}=lookup
| foreach lookup__*
    [| eval result=if(isnotnull(<<FIELD>>),<<MATCHSEG1>>,result)]
| fields - lookup__*

[dpuhr]

Thaks a lot, it works!

I am just thinking about efficiency in case of 10th "fields" i have to create to perform lookup on them.
Which solution is more efficient, yours, or yuanliu's one?
Thanks

David

[ITWhisperer]

You could test it - use job inspector to see how long each takes - you may have to run each version multiple times to get an average.

Having said that, it probably depends on the ratio of the number of unique values in lookup to the total number of fields (found by the wildcard list on the foreach command). For ratios greater than 1, I suspect @yuanliu 's solution might be more efficient. For ratios less than 1, I suspect my solution might be more efficient.

[yuanliu · Accepted solution]

Even if your input fields doesn't fit into a neatly prefixed wildcard expression, wildcard still works.  Why no this?

| foreach * [eval result=if(lookup="<<FIELD>>", $<<FIELD>>$, result)]

[dpuhr]

Thanks a lot, it works!

I am just thinking about efficiency. I guess, that subsearch [eval...] run first, so foreach cycle does not process all (*) fields? Am I right?

David

[richgalloway]

If your problem is resolved, then please click the "Accept as Solution" button to help future readers.

[richgalloway]

You seem to have the answer already.  Have you tried 

| foreach mode=multivalue mv_lookup [eval result=if(lookup="<<ITEM>>", $<<ITEM>>$, result)]

What was the result?

[dpuhr]

I'm sorry, it does not work.

You can try to run it by yourself:

| makeresults 
| eval raw="x1 y1 z1 field1 x1::x2 y2 z2 field3 z2::x3 y3 z3 field2 y3" 
| makemv delim="::" raw 
| mvexpand raw 
| rex field=raw "(?<field1>\S+)\s+(?<field2>\S+)\s+(?<field3>\S+)\s+(?<lookup>\S+)\s+(?<expected_result>\S+)"
| eventstats values(lookup) as mv_lookup
| fields - raw _time
| eval result="N/A"
| foreach mode=multivalue mv_lookup [eval result=if(lookup="<<ITEM>>", $<<ITEM>>$, result)]

I am afraid, that $<<ITEM>>$ cannot be filled with the value from the field field1, field2 or field3... It works only if <<FIELD>> keyword is used, I guess 😕

Does exist any method how get results in this case?

Thanks

David