How to filter Search 1 result from Search 2 returned value?

[QQAL2021]

We have many completely diff events. Sometimes, we got a result based on Search 1. But we want to exclude some records from results of Search 2.

E.G., the below are events

event1: {name: mike, action: ok}

event2: {name: cool, action: ok}

event3: {name: mike, state: invalid}

My first search1 shall get event1, event2

ok | table name, action

My second search2 shall get event3

invalid | fields name

However, Search 1 shall exclude the result of Search 2, that is, only event 2 shall be gotten

I am trying to achieve by

ok |  search NOT name IN ([invalid | fields name]) | table name, action

But obviously it is unsuccessful

Thanks for any advice

[yuanliu]

It will be easier if you can define the actual  requirement without considering two SPL searches.  Do you mean to simply exclude events containing a name which has state=invalid? Because you illustrated events as JSON objects, I assume that name, action, and state are already extracted.

If you want to use subsearch, you can do 

action=ok NOT [
  search state=invalid
  | field name ]

Alternatively, 

action=ok OR state=invalid
| eventstats values(state) as state by name
| where state!="invalid"

If you have a list of fields of interest  and do not care to carry all events along, you can use stats instead of eventstats to gain performance, e.g.,

action=ok OR state=invalid
| fields state foi1 foi2 ...
| stats values(*) as * by name
| where state!="invalid"

[ITWhisperer · Accepted solution]

Try something like this

ok NOT [ search invalid | fields name ] | table name, action

[QQAL2021]

works perfectly thank you

Just curious, is there a way to make subsearch returns a list? I mean how to make the below syntax correctly?

ok field NOT IN [ search invalid | return field ] | table name, action

[ITWhisperer]

Try something like this

ok NOT field IN [ search invalid | table field | rename field as query | format "(" "" "" "" "," ")" ] | table name, action

Important thing to note is renaming the field to query as this  gets special treatment by the format command and is not passed through, so you just get a list of values