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

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

dpuhr — Wed, 22 Mar 2023 15:41:08 GMT

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

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

richgalloway — Wed, 22 Mar 2023 18:56:37 GMT

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?

Re: Using a column of field names FROM MVFIELD to dynamically select fields for use in eval expression

dpuhr — Wed, 22 Mar 2023 16:16:19 GMT

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

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

yuanliu — Thu, 23 Mar 2023 06:28:10 GMT

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

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

ITWhisperer — Thu, 23 Mar 2023 07:27:53 GMT

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__*

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

dpuhr — Thu, 23 Mar 2023 09:13:16 GMT

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

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

ITWhisperer — Thu, 23 Mar 2023 09:22:33 GMT

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.

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

dpuhr — Thu, 23 Mar 2023 09:39:34 GMT

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

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

richgalloway — Thu, 23 Mar 2023 13:19:01 GMT

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