Splunk Search

Splunks equivalent to the SQL "IN" () function

marquiselee
Path Finder

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?

Tags (2)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

marquiselee
Path Finder

This worked perfectly! Thank You Thank You

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

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]

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...