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
Try something like this
ok NOT [ search invalid | fields name ] | table name, action
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"
Try something like this
ok NOT [ search invalid | fields name ] | table name, action
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
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