topic Re: Splunks equivalent to the SQL "IN" () function in Splunk Search

Splunks equivalent to the SQL "IN" () function

marquiselee — Mon, 28 Sep 2020 13:24:06 GMT

I'm trying to create a search form that can take a comma separated list. In sql I would use the 'IN' command.

If the form allowed for a comma separated list like "Honda,Chevorlet,FORD,TOYOTA"
I guess the search would look similar to this.

search index=Cars CAR_MAKE IN ($CAR_MAKE$)|table CAR_MAKE CAR_MODEL

Any Ideas?

Re: Splunks equivalent to the SQL "IN" () function

alacercogitatus — Tue, 26 Feb 2013 20:49:32 GMT

Map might work for you, but the more in the list, the longer it takes.

|eval cars=$car_make$|eval car=split(cars,",")|map [search index=Cars car=$car$]

You can also try using a lookup. In $APP_HOME/lookups make a "cars.csv".

cars.csv car Ford Toyota Honda

Then search

index=Cars | join car [|inputlookup cars.csv |stats count by car|fields - count]

Re: Splunks equivalent to the SQL "IN" () function

martin_mueller — Tue, 26 Feb 2013 21:24:58 GMT

You could use this subquery in the main search:

index=Cars [| gentimes start=-1 increment=1d | eval CAR_MAKE = "foo,bar,baz" | makemv CAR_MAKE delim="," | mvexpand CAR_MAKE | return 100 CAR_MAKE]

The subsearch yields this as a filter for the main search:

(CAR_MAKE="foo") OR (CAR_MAKE="bar") OR (CAR_MAKE="baz")

Just replace the fixed string with the appropriate $value$.

Re: Splunks equivalent to the SQL "IN" () function

marquiselee — Tue, 26 Feb 2013 22:51:10 GMT

This worked perfectly! Thank You Thank You

Re: Splunks equivalent to the SQL "IN" () function

woodcock — Wed, 04 Oct 2017 15:09:43 GMT

We can all celebrate v6.6.1:
http://docs.splunk.com/Documentation/SplunkCloud/6.6.1/ReleaseNotes/NewSplunkCloudFeatures

Because this:
New SQL-like IN SPL operator New SPL operator that acts as a shorthand for multiple disjunctions of one field. See Comparison and Conditional functions and search in the Search Reference manual.

So this:
http://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/ConditionalFunctions#in.28VALUE-LIST.29

And this:
You can use this function with the eval, fieldformat, and where commands, and as part of eval expressions with other commands.
There is also an IN operator that is similar to the in(VALUE-LIST) function that you can use with the search and tstats commands.
The following syntax is supported:
...| where in(field,"value1","value2", ...)
...| where field in("value1","value2", ...)
...| eval new_field=in(field,"value1","value2", ...)