Splunk Search

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

QQAL2021
Engager

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

Labels (1)
Tags (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

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

View solution in original post

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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"

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

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

QQAL2021
Engager

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

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...