Splunk Search

Can an IN clause in a SPL query have 277 values?

mihir_hardas
Explorer

How many values are allowed in an IN clause which is part of where clause? I want to read 277 values to be precise.


index=abc sourcetype="ccinfrap.dc_pqr_jws:app"
"[SubscriptionService] Consumer sent message"
"Not Predicted to Finish"
| rex mode=sed "s/^.*message {/{/"
| rex mode=sed "s/\n}.*/\n}/"
| spath
| fillnull jobStreamName value="BLANK"
| where jobStreamName IN(
"stream1"
,"stream2"
,"stream3"
.

.

,"stream277"
)
Labels (1)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

I wouldn't be so sure about the performance of one vs. another. If you limit events at the initial stage, you get less results to process further down the pipeline. If you just do search * | lookup | where, Splunk has to do a lookup on every single event returned. (I know that Splunk can be clever and can sometimes optimize out some simple SPL constructions but I don't think it's this case).

EDIT: OK. I didn't read the original post thoroughly enough - OP himself did some trickery and got the "| where" part at the end. I still think this condition should be incorporated into the initial search (possibly by searching over the _raw data, withouth matching specific fields). It could save us some time especially since there's a relatively heavy spath further down the road.

View solution in original post

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @mihir_hardas ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the Contributors 😉

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @mihir_hardas ,

having so many values, why don't you think to use a lookup to contain the 277 values?

Ciao.

Giuseppe

0 Karma

mihir_hardas
Explorer

The only reason to go for such a long IN clause is because I feel its faster than using lookup. The lookup is very very slow.

0 Karma

javiergn
Super Champion

It probably can but would it not be cleaner to use a lookup table instead?

So instead of

| where jobStreamName IN(
"stream1"
,"stream2"
,"stream3"
...
,"stream277"
)

 

You would do:

| lookup jobStreamLookup jobStreamName as jobStreamName OUTPUT jobStreamName AS match
| where isNotNull(match)

 

Your lookup file would be as simple as:

 

jobStreamName
-----------------------
stream1
stream2
stream3
...

 

Be careful with the case sensitivity though.

 

Regards,

Javier

0 Karma

PickleRick
SplunkTrust
SplunkTrust

I wouldn't be so sure about the performance of one vs. another. If you limit events at the initial stage, you get less results to process further down the pipeline. If you just do search * | lookup | where, Splunk has to do a lookup on every single event returned. (I know that Splunk can be clever and can sometimes optimize out some simple SPL constructions but I don't think it's this case).

EDIT: OK. I didn't read the original post thoroughly enough - OP himself did some trickery and got the "| where" part at the end. I still think this condition should be incorporated into the initial search (possibly by searching over the _raw data, withouth matching specific fields). It could save us some time especially since there's a relatively heavy spath further down the road.

0 Karma
Get Updates on the Splunk Community!

Splunk is Nurturing Tomorrow’s Cybersecurity Leaders Today

Meet Carol Wright. She leads the Splunk Academic Alliance program at Splunk. The Splunk Academic Alliance ...

Part 2: A Guide to Maximizing Splunk IT Service Intelligence

Welcome to the second segment of our guide. In Part 1, we covered the essentials of getting started with ITSI ...

Part 1: A Guide to Maximizing Splunk IT Service Intelligence

As modern IT environments continue to grow in complexity and speed, the ability to efficiently manage and ...