Splunk Search

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

andrewdotnich
Explorer

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…

1 Solution

sideview
SplunkTrust
SplunkTrust

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

View solution in original post

MattZerfas
Communicator

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

rstitt
Explorer

Another idea: Use regex

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

A bit clunky, but better than tons of ORs.

0 Karma

sideview
SplunkTrust
SplunkTrust

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

View solution in original post

gkanapathy
Splunk Employee
Splunk Employee

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.

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

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.

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!