topic Re: return a list from subsearch and use it in main search. return not properly formatted in Splunk Search

return a list from subsearch and use it in main search. return not properly formatted

zebraslunker — Tue, 06 Feb 2024 17:33:58 GMT

i have a splunk query below that returns me

( ( ( list_value2="dev1" OR list_value2="dev2" OR list_value2="dev5" OR list_value2="dev6" ) ) )

i want to use this 4 values as a list to query using IN operation from another main search as show in the second code snippet.

```

index=main label=y userid=tom
| fields associateddev
| eval list_value = replace(associateddev,"{'","")
| eval list_value = replace(list_value,"'}","")
| eval list_value = split(list_value,"', '")
| mvexpand list_value
| stats values(list_value) as list_value2
| format

```

i want to use the results from this as part of a subsearch to query another source as shown below. ideally, the subsearch will return me a list that i can just call using | where hname IN list_value2. But list_value2 is returning me this ( ( ( list_value2="dev1" OR list_value2="dev2" OR list_value2="dev5" OR list_value2="dev6" ) ) ) weird string.
```

index="main" label=x
| where hname IN [search index=main label=y userid=tom
| fields associateddev
| eval list_value = replace(associateddev,"{'","")
| eval list_value = replace(list_value,"'}","")
| eval list_value = split(list_value,"', '")
| mvexpand list_value
| stats values(list_value) as list_value2]
| table _time, hname, list_value2

```

i have tried
| stats values(list_value) as search
| format mvsep="," "" "" "" "" "" ""]

but i still get the error: Error in 'search' command: Unable to parse the search: Right hand side of IN must be a collection of literals. '(dev1 dev2 dev5 dev6)' is not a literal.

Re: return a list from subsearch and use it in main search. return not properly formatted

richgalloway — Tue, 06 Feb 2024 17:39:02 GMT

The IN operator maps to a series of OR operators (check the Job Inspector) so forcing a set of OR operators into IN-compatible form is a wasted effort.

Re: return a list from subsearch and use it in main search. return not properly formatted

zebraslunker — Tue, 06 Feb 2024 18:00:28 GMT

Noted on that, but, this throws me an Error in 'where' command: The expression is malformed. Expected ).

index="main" label=x source="C:\\Users\\me\\Documents\\test22.csv"
| eval hm = replace(hostname,",","")
| where hm IN ([search index=main label=y userid=tom
| fields associateddev
| eval list_value = replace(associateddev,"{'","")
| eval list_value = replace(list_value,"'}","")
| eval list_value = split(list_value,"', '")
| mvexpand list_value
| stats values(list_value) as search])

but this works assuming i dont do any operations to hostname column. is it possible to insert some eval on hostname before doing the IN operation?

index="main" label=x source="C:\\Users\\me\\Documents\\test22.csv" hostname IN ([search index=main label=y userid=tom
| fields associateddev
| eval list_value = replace(associateddev,"{'","")
| eval list_value = replace(list_value,"'}","")
| eval list_value = split(list_value,"', '")
| mvexpand list_value
| stats values(list_value) as search])

Re: return a list from subsearch and use it in main search. return not properly formatted

richgalloway — Tue, 06 Feb 2024 18:13:04 GMT

The where command does not support the IN operator. It does support the in function, which has a different syntax.

The point of my original reply to say that extra code to force a set of values into a comma-separated list for the benefit of the IN operator is wasted effort. The interpreter is just going to convert that comma-separated list into a series of OR operators so you might well just take the raw result from the subsearch (without usingIN).