topic Re: Is there a way to populate an "IN (...)" with items from subsearch? in Splunk Search

Is there a way to populate an "IN (...)" with items from subsearch?

spinnerdog — Wed, 03 Aug 2022 17:49:41 GMT

Is there a way to populate the items in an "IN" statement with the results of a sub query? I've tried several variations.

index=x accountid IN ( [ search index=special_accounts | rename accountid as query ] )

Re: Is there a way to populate an "IN (...)" with items from subsearch?

richgalloway — Wed, 03 Aug 2022 18:32:22 GMT

Since the IN operator is mapped to a series of ORs under the covers, use the OR output produced by the subsearch.

index=x accountid [ search index=special_accounts | rename accountid as query | fields query | format ]

Re: Is there a way to populate an "IN (...)" with items from subsearch?

spinnerdog — Wed, 03 Aug 2022 18:07:11 GMT

Okay, thanks. Was trying to work around using the format command, but maybe there is a way to use it differently. How can you make the format command produce output like.

[search index=special_accounts | table accountid | format mvsep=AND ]

where the desired output would use != instead of =

(accountid!=xxx AND accountid!=yyy AND ...)

Thanks

Re: Is there a way to populate an "IN (...)" with items from subsearch?

richgalloway — Wed, 03 Aug 2022 18:31:51 GMT

Why avoid format? It produces the same thing that IN does.

You can change OR to AND in format, but there's no way I can find to change = to !=. However, "(foo!=bar AND foo!=baz)" is not the same as "foo IN (bar, baz)", which is what I thought the OP wanted.

Re: Is there a way to populate an "IN (...)" with items from subsearch?

spinnerdog — Wed, 03 Aug 2022 18:36:36 GMT

correct, its just the inverse of what the format command produces. I have a list of valid values and I want the events with invalid values. Was hoping to use something line NOT IN (...). But that's not an option and I also can't find a way to change = to "!=".

Re: Is there a way to populate an "IN (...)" with items from subsearch?

richgalloway — Wed, 03 Aug 2022 20:18:10 GMT

Oh! That's easy! Just negate the subsearch.

index=x accountid NOT [ search index=special_accounts | rename accountid as query | fields query | format ]

It'll give you NOT (foo=bar OR foo=baz)