topic Re: Does Splunk have an equivalent to SQL's IN () construct? in Splunk Search

Does Splunk have an equivalent to SQL's IN () construct?

andrewdotnich — Tue, 28 Jun 2011 00:22:55 GMT

I have an analyst who wants to find logs where the value of a field is in a list of values. In SQL we can do this by saying:

SELECT * FROM table WHERE field IN (tuple, of, values);

Is there an easy, throw-away way to do this in Splunk?

EDIT: This list is too long to specify with a string of OR clauses; I'm looking for something a little neater than that…

Re: Does Splunk have an equivalent to SQL's IN () construct?

sideview — Tue, 28 Jun 2011 00:39:45 GMT

1) You could create a lookup (think a big csv file) where each row is one of the values in your tuple.
The lookup would have to have two columns even though in such a simple case the second one feels redundant.

in this case lets say wegive it columns called 'value' and 'weirdness' as your columns.

<your search> | lookup mylookupname fieldName | where weirdness=1

http://www.splunk.com/base/Documentation/4.2.2/User/Fieldlookupstutorial

http://www.splunk.com/base/Documentation/latest/SearchReference/Lookup

2) If the events that you're trying to match on are a tiny subset of the main events, there's another similar way that can be much more efficient. That is to use the inputlookup command within a subsearch. Essentially you're doing that gigantic OR search, butin an automated way

<your search> [ inputlookup mylookupname | fields myfieldname ]

http://www.splunk.com/base/Documentation/4.2.2/User/HowSubsearchesWork

Re: Does Splunk have an equivalent to SQL's IN () construct?

gkanapathy — Tue, 28 Jun 2011 03:21:18 GMT

Edited some. A subsearch within the base search is exactly the equivalent of a subselect used in an IN clause in SQL.

Also, the size of the list (the IN clause, or the size of mylookupname file) can be 10,000 items in size, which is larger than the allowed size of an IN clause in many SQL databases.

Re: Does Splunk have an equivalent to SQL's IN () construct?

gkanapathy — Tue, 28 Jun 2011 03:22:21 GMT

And note that inputlookup is one way to populate the subsearch clause. You can use any valid Splunk search in the subsearch, just as you can use any SQL statement in a subselect.

Re: Does Splunk have an equivalent to SQL's IN () construct?

rstitt — Sat, 11 Feb 2017 05:10:28 GMT

Another idea: Use regex

| regex field="^(a|b|c|d|e|f|g)$"

A bit clunky, but better than tons of ORs.

Re: Does Splunk have an equivalent to SQL's IN () construct?

MattZerfas — Tue, 05 Sep 2017 19:49:32 GMT

In Splunk 6.6 there is a new feature now that you CAN use IN(). Below is a link to the doc on it.

IN() operator in search