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
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Level Up Your .conf25: Splunk Arcade Comes to Boston

With .conf25 right around the corner in Boston, there’s a lot to look forward to — inspiring keynotes, ...

Manual Instrumentation with Splunk Observability Cloud: How to Instrument Frontend ...

Although it might seem daunting, as we’ve seen in this series, manual instrumentation can be straightforward ...

Take Action Automatically on Splunk Alerts with Red Hat Ansible Automation Platform

Ready to make your IT operations smarter and more efficient? Discover how to automate Splunk alerts with Red ...