How to use return command in subsearch to return a multivalued field?

[kabiraj]

I am trying to use return command to output a multivalued field from subsearch to main search. My search looks like below:

mysearch | eval field = [| inputlookup rest_of_search | return $fieldname]

Here, fieldname has multiple values in multiple rows but after running query it outputs only the value at first row to each of the rows because of which i am getting same value in each row. I also tried below but it showed error:

mysearch | eval field = [| inputlookup rest_of_search | return 1000 $fieldname]

where 1000 is the count of matched results.

Any Solution?

[DalJeanis]

@kabiraj -

Are you trying to get a single value out of the lookup that is appropriate to each value on the input record? If so, then use this syntax

| lookup mylookupname lookupfieldname OUTPUT outputfieldnamefromlookup

If not, then I think you may be trying to do something in a way that won't work. But it is kind fo hard to figure out what that might be. Please back up and update the question with an explanation of the overall purpose of your search, what is in the lookup, and what you hope this structure will achieve.

[niketn]

@kabiraj, can you add sample data from your inputlookup which has multivalued field.

Following is a run anywhere search for you to try out

| makeresults
| eval
    [|  makeresults
    |  eval data="100;200"
    |  makemv data delim=","
    |  table data
    |  return data]
| makemv data delim=";"

In your case you can try out the following:

 mysearch 
| eval  
           [| inputlookup rest_of_search 
            | return 1000 fieldname]
| makemv fieldname delim=";"

[kabiraj]

@niketnilay basically the output is a table with single column & multiple rows. in this above example, data is the column & it has like 100 rows with different values in each row

[kabiraj]

here is some sample data for the field:

percent
80
0
0
0
100
0
0
50
7.692308
100
33.333333
17.391304
0
0
14.285714

percent is the column and rest are rows with values

[niketn]

Hi @kabiraj, based on the details seems like you want to use the values returned by the inputlookup to perform filter in your base search. Also what you have mentioned as multivalue is actually several rows of a column with single value.

I am hoping the field name in your lookup file is the same as what you intend to search in your base search (or else you would need to use rename command). Your sample data seems to have duplicate values for percent so if you want to use unique values you should use dedup command:

 myBaseSearch [| inputlookup rest_of_search | dedup fieldname | table fieldname]

Following is a run anywhere search based on Splunk's _internal index and makeresults command instead of lookup file, to explain the above search:

index=_internal sourcetype=splunkd 
    [| makeresults
    |  eval log_level="WARN,ERROR,FATAL"
    |  makemv log_level delim=","
    |  mvexpand log_level
    |  table log_level]

The makeresults command is used to generate a log_level field (column) with three rows i.e. WARN, ERROR AND FATAL. Placing this in base search under square braces actually implies the following search:

index=_internal sourcetype=splunkd log_level="WARN" OR log_level="ERROR" OR log_level="FATAL" 

Please try out and confirm. If you are looking for something else you will have to provide more details.

[kabiraj]

@niketn No, This is not what i am looking for.

mysearch | eval field = [| inputlookup rest_of_search | return $fieldname]

in this spl, ideally the values under "fieldname" should be assigned to field "field", which i am getting fine but the problem is with the values. It copies the value of first row to all the rows & then assigns it to field "field" because of which i am getting the same value in all the rows in field "field" which is incorrect.

then i tried

mysearch | eval field = [| inputlookup rest_of_search | return 1000 $fieldname]

1000 is the count of rows which it should consider uniquely while copying. By default its 1 because of which only first row get copied to all rows.

But this spl gives me error in eval