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

[andrewdotnich]

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…

[MattZerfas]

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]

Another idea: Use regex

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

A bit clunky, but better than tons of ORs.

[sideview · Accepted solution]

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

[gkanapathy]

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.

[gkanapathy]

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.