Whats the splunk equivalent of SQL IN clause

[jmc82]

What is the Splunk equivalent of an SQL IN clause. I want to run a query where some field has a value which is present in a list of values.

For example, suppose I have a list of car types, such as:

BMW
Volkswagon
Ford

And I want to query something like:

CAR_MAKE IN {BMW, Volkswagon, Ford}

Obviously I can query separately using

CAR_MAKE = "BMW" or CAR_MAKE = "Volkswagon"... etc

However my actual use case is slightly more complicated (I have an inputlookup) but the principle is the same. How can this be done?

[JohnMurphyAus]

Splunk now has an "IN" operator. So you can simply add the following to your search:

CAR_MAKE IN (BMW, Volkswagon, Ford)

If your search values have spaces, it will need to be wrapped in quotations. E.g:

CAR_MAKE IN (BMW, Volkswagon, "Mercedes Benz", Ford)

I know its late, but hopefully it can help people looking for it now.

Also, you may want to correct the spelling of Volkswagen 🙂

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

[lguinn2]

As of Splunk 6.6, you can test a list of values. However, for an extensive list, the lookup solution given is better.

Search command supports IN operator

sourcetype=xyz status IN (100, 102, 103)

Eval and where commands support in function

| where in(status,"222","333","444","555")

[vkalra2924]

If the values are limited then a simple query with OR clause will do its job in this case. i.e.

Car_Make="Honda" OR Car_Make="Toyoto" etc.

Please note that use of "OR" is case sensitive

[bmunson_splunk · Accepted solution]

The first thing is that splunk's query language is not SQL so looking for similar commands will not always be possible. But there is nearly always a way of doing things.

Looking at your data, I would restructure the query as follows.

my_car_search | lookup cardata.csv car_make OUTPUT country | where country="Japan"

If the lookup was made automatic, it would be a simple as

my_car_search country="Japan"

[jmc82]

Got this working via the inline search you mentioned in your first suggestion thanks:

my_car_search | lookup cardata.csv car_make OUTPUT country | where country="Japan"

Trying unsuccessfully to do it via an automatic lookup table but I believe that ultimately that would be the better solution thanks.

[jmc82]

Ok, so an autolookup table sounds like the way to go. Can someone clarify how to configure this for me? My lookup has two columns:

CAR_MAKE, COUNTRY_OF_ORIGIN

and my original data has a CAR_MAKE field. Based on the docs I believe my autolookup table should specify the following:

Lookup input fields:
CAR_MAKE = CAR_MAKE

and

Lookup output fields:
COUNTRY_OF_ORIGIN = COUNTRY_OF_ORIGIN

And this should result in the COUNTRY_OF_ORIGIN field becoming part of my events, is that correct?

Thanks.

[bmunson_splunk]

Correct though technically if you are not renaming them you don't need the second part.

This walks you through setting up lookups
http://docs.splunk.com/Documentation/Splunk/6.1/Knowledge/Usefieldlookupstoaddinformationtoyourevent...

[jmc82]

Ok, sorry, I think I need to clarify my use case a bit:

Suppose we have a log file containing the details of various cars. Something like:

car_make = "Toyota"....
car_make = "Honda"...
car_make = "BMW"...

I have an inputlookup which maps the car make to its country of origin:

Japan Toyota
Japan Honda
Germany BMW

The user has a drop down list where they can select a country. So suppose they select 'Japan'. I then want to filter my events for all Japanese cars. So I take the value of the drop down (Japan in this example) and I search my lookup for all the cars from Japan and I get back 'Toyota' and 'Honda'.

I then need my search string to have something like:

car_make in {'Toyota', 'Japan'}

or

car_make = "Toyota" or car_make = "Honda"...etc

Just to be clear, I DONT have the country of origin in my event data, so I can't simply search my events for country_of_origin='Japan' or anything like that.

Thanks for your help.

[MuS]

Ok, the easiest thing is to read the docs on http://docs.splunk.com/Documentation/Splunk/6.1.4/Knowledge/Usefieldlookupstoaddinformationtoyoureve... and add this country_of_origin field from the lookup file. After that you can search for it.

[jeremiahc4]

I agree with MuS, you can add the country_of_origin to your events at search time using an automatic lookup. It was a bit daunting at first to get a handle on how to set them up, but you'll get the hang of it and it'll be a great tool in your Splunk toolbelt.

[MuS]

OR is how it is done in Splunk. Maybe you should provide more details about your actual use case, search string and lookup file and I'm sure someone can guide you .....

[jmc82]

OR is not feasible for me. The list is potentially a few hundred entries long and its contents can change. Surely there is a simple way of checking for membership like this without explicitly checking every member of the list using on OR clause?

[MuS]

okay, without still not knowing your events nor use cases I'll give it a shot ....

Assumption: you have a lookup file containing members to membership mapping. Now, you want to find a user that is not mapped to any membership in your lookup file, run something like this:

your base search to get user events | search NOT [ | inputlookup membership ] | ....

Maybe this will give you some hint....