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

[spinnerdog]

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

[richgalloway]

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 ]

[spinnerdog]

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

[richgalloway]

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.

[spinnerdog]

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 "!=".

[richgalloway]

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)