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

[mihir_hardas]

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

[gcusello]

Hi @mihir_hardas ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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

[gcusello]

Hi @mihir_hardas ,

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

Ciao.

Giuseppe

[mihir_hardas]

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.

[javiergn]

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

[PickleRick · Accepted solution]

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.