Splunk Search
Highlighted

Whats the splunk equivalent of SQL IN clause

Explorer

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

CARMAKE = "BMW" or CARMAKE = "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?

Tags (3)
Highlighted

Re: Whats the splunk equivalent of SQL IN clause

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

Re: Whats the splunk equivalent of SQL IN clause

Explorer

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?

0 Karma
Highlighted

Re: Whats the splunk equivalent of SQL IN clause

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

Re: Whats the splunk equivalent of SQL IN clause

Explorer

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:

carmake = "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

carmake = "Toyota" or carmake = "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 countryoforigin='Japan' or anything like that.

Thanks for your help.

0 Karma
Highlighted

Re: Whats the splunk equivalent of SQL IN clause

SplunkTrust
SplunkTrust

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.

Highlighted

Re: Whats the splunk equivalent of SQL IN clause

Builder

I agree with MuS, you can add the countryoforigin 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.

Highlighted

Re: Whats the splunk equivalent of SQL IN clause

Splunk Employee
Splunk Employee

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"

View solution in original post

Highlighted

Re: Whats the splunk equivalent of SQL IN clause

Explorer

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:

CARMAKE, COUNTRYOF_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:
CARMAKE = CARMAKE

and

Lookup output fields:
COUNTRYOFORIGIN = COUNTRYOFORIGIN

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

Thanks.

0 Karma
Highlighted

Re: Whats the splunk equivalent of SQL IN clause

Splunk Employee
Splunk Employee

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

0 Karma