Can I exclude results from a subsearch from my main search?

[asdfxqwert]

Hi,
I have two Splunk searches:

search1
search2

search2 returns a list of values for field IP. I am trying to exclude these results from search1.
Can you please explain if this is possible

Thanks

[nits]

@splunkoverflow @pcombs001 @kamlesh_vaghela  Can anyone answer to this. I have problem on similar pattern.

Here is my problem summary with sample queries and outputs:

My 1st query will return:

1st Query:

index=test "TestRequest" | dedup _time | rex field=_raw "Price\":(?<price>.*?)," | rex field=_raw REQUEST-ID=(?<REQID>.*?)\s | rex field=_raw "Amount\":(?<amount>.*?)}," | rex field=_raw "ItemId\":\"(?<itemId>.*?)\"}" | eval discount=round(exact(price-amount),2) , percent=(discount/price)*100
, time=strftime(_time, "%m-%d-%y %H:%M:%S") | stats list(time) as Time list(itemId) as "Item" list(REQID) as X-REQUEST-ID list(price) as "Original Price" list(amount) as "Test Price" list(discount) as "Dollar Discount" list(percent) as "Percent Override" by _time
| join X-REQUEST-ID
[search index=test "UserId=" | rex field=_raw UserId=(?<userId>.*?)# | dedup userId | rex field=_raw X-REQUEST-ID=(?<REQID>.*?)\s | stats list(userId) as "User ID" list(REQID) as X-REQUEST-ID by _time]

Sample Output:

Time User Id Item X-REQUEST-ID Original Price Test Price Dollar Discount Percent Override
1           1             1               1                          1                         1                      1                                1
2           2             2               2                          2                         2                      2                                2
3           3             3               3                          3                         3                      3                                3
4           4             4               4                          4                         4                      4                                4
5           5             5               5                          5                         5                      5                                5

2nd Query:

search index=test "Remove Completed for" | rex field=_raw UserId=(?<userId>.*?)# | rex field=_raw X-REQUEST-ID=(?<REQID>.*?)\s | stats list(userId) as "User ID" list(REQID) as X-REQUEST-ID by _time

Sample Output:

User Id
4

3rd Query:

search index=test "Clear Completed for" | rex field=_raw UserId=(?<userId>.*?)# | rex field=_raw X-REQUEST-ID=(?<REQID>.*?)\s | stats list(userId) as "User ID" list(REQID) as X-REQUEST-ID by _time

Sample Output:

User Id
5

I want the final output as below: 

Time User Id Item X-REQUEST-ID Original Price Test Price Dollar Discount Percent Override
1           1             1               1                          1                         1                      1                                1
2           2             2               2                          2                         2                      2                                2
3           3             3               3                          3                         3                      3                                3

The above output is excluding the results of 2nd Query and 3rd Query from main search query result (1st Query) based on the field value of "User Id". So if  "User Id" found in 1st Query also found in either 2nd Query and 3rd Query then exclude that "User Id" row from main result 1st Query.

[kamlesh_vaghela]

Hi
Can you share your sample searches??

[pcombs001]

Try this..

SEARCH1 NOT [SEARCH2]

I have done something similar where my second search does an | inputlookup and returns a table of values.

index=cisco_ios_us oid=14179.2.1.4.1.1 NOT
[| inputlookup user_lookup
| search user_name="blah"
| table user_id]

Hope this helps.

[splunkoverflow]

I didn't know that NOT is case-sensitive (must be all caps).

[asdfxqwert]

To add to the question, I am trying to find the equivalent of this in Splunk

SELECT column_name(s)
FROM table_name
WHERE column_name NOT IN (SELECT STATEMENT);