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