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!

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

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...