All Apps and Add-ons

Is there an operator similar to the SQL 'in' operator?

dstuder
Communicator

I've been looking through the search documentation to see if Splunk has an operator similar to the SQL 'in' operator. I'm not seeing anything so my hunch is it does not exist, but I thought I would just ask. I know I can just add on a bunch of 'or' clauses but an 'in' operator would just be a bit more concise. Any thoughts?

1 Solution

lguinn2
Legend

Wish Granted!!! In Splunk 6.6 -

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

View solution in original post

mad4wknds
Path Finder

Note: This works but it takes way longer of this to return result than if you ran it with field=value pair

0 Karma

lguinn2
Legend

Wish Granted!!! In Splunk 6.6 -

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

amehta_splunk
Splunk Employee
Splunk Employee

The feature was introduced in Splunk 6.6 (see Release Notes) in May 2017.
Feature: 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.

0 Karma

dstuder
Communicator

Handel's Messiah started playing in my mind as soon as I read this. 🙂

0 Karma

dstuder
Communicator

Doesn't look like you can do a NOT IN yet. Urg!!

0 Karma

dstuder
Communicator

Oh ... wait. I spoke too soon. It looks like it has to be this way.
... AND NOT field IN (val1, val2, val3)
as opposed to
... AND field NOT IN (val1, val2, val3)

0 Karma

somesoni2
Revered Legend

Superb!!! it was in my wishlist too.

0 Karma

inventsekar
SplunkTrust
SplunkTrust

SQL "in" example -
select a from A where b in (select b from B)
can be done thru subsearches -
sourcetype=a [sourcetype=B | fields b] | fields a

a good discussion on the same topic -
https://answers.splunk.com/answers/173465/whats-the-splunk-equivalent-of-sql-in-clause.html

another example -
https://answers.splunk.com/answers/26989/does-splunk-have-an-equivalent-to-sqls-in-construct.html

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

Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...