Splunk Search

How to exclude the results from subsearch

nits
Explorer

I have one  query which looks like:

Query1:
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]

I have another 2 queries which looks like:

Query2:
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

Query3:
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


I want to exclude the results from Query1 that are matching the results from Query2 and Query3 based on column "User Id" which is present in all three queries. How can i do that?

Labels (4)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It is not clear to me which events you want to exclude. Would this work?

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=" NOT "Remove Completed for" NOT "Clear Completed for" 
| 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
]
0 Karma

nits
Explorer

@ITWhisperer 
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

Time UserId 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.

0 Karma

nits
Explorer

@ITWhisperer  Basically want equivalent of 
SELECT column_name(s)
FROM table_name
WHERE column_name NOT IN (SELECT STATEMENT);

shaurya10000
Engager

Did you find an answer to this ?

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...