Splunks equivalent to the SQL "IN" () function

[marquiselee]

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?

[woodcock]

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.28VAL...

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", ...)

[martin_mueller · Accepted solution]

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$.

[marquiselee]

This worked perfectly! Thank You Thank You

[alacercogitatus]

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]