topic Re: Whats the splunk equivalent of SQL IN clause in Splunk Search

Whats the splunk equivalent of SQL IN clause

jmc82 — Mon, 28 Sep 2020 17:53:02 GMT

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?

Re: Whats the splunk equivalent of SQL IN clause

MuS — Wed, 15 Oct 2014 09:43:46 GMT

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

Re: Whats the splunk equivalent of SQL IN clause

jmc82 — Wed, 15 Oct 2014 10:39:27 GMT

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?

Re: Whats the splunk equivalent of SQL IN clause

MuS — Wed, 15 Oct 2014 10:45:50 GMT

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

Re: Whats the splunk equivalent of SQL IN clause

jmc82 — Mon, 28 Sep 2020 17:53:04 GMT

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'}

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.

Re: Whats the splunk equivalent of SQL IN clause

MuS — Wed, 15 Oct 2014 11:04:05 GMT

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

Re: Whats the splunk equivalent of SQL IN clause

jeremiahc4 — Mon, 28 Sep 2020 17:53:09 GMT

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.

Re: Whats the splunk equivalent of SQL IN clause

bmunson_splunk — Wed, 15 Oct 2014 13:40:36 GMT

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"

Re: Whats the splunk equivalent of SQL IN clause

jmc82 — Mon, 28 Sep 2020 17:53:14 GMT

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.

Re: Whats the splunk equivalent of SQL IN clause

jmc82 — Mon, 28 Sep 2020 17:53:18 GMT

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.

Re: Whats the splunk equivalent of SQL IN clause

bmunson_splunk — Wed, 15 Oct 2014 14:41:57 GMT

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/Usefieldlookupstoaddinformationtoyourevents

Re: Whats the splunk equivalent of SQL IN clause

vkalra2924 — Tue, 29 Sep 2020 10:22:29 GMT

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

Re: Whats the splunk equivalent of SQL IN clause

lguinn2 — Mon, 19 Jun 2017 20:29:47 GMT

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

Re: Whats the splunk equivalent of SQL IN clause

woodcock — Wed, 04 Oct 2017 15:09:57 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", ...)

Re: Whats the splunk equivalent of SQL IN clause

JohnMurphyAus — Thu, 14 Jul 2022 07:05:59 GMT

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 🙂