Splunk Search

How to exclude the results from main search that matches with sub search

nits
Explorer

Here is my problem statement: 

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.

Labels (5)
0 Karma

FritzWittwer
Path Finder

I'd try something like

<< First Query >>
| append [<< Second Query >>]
| append [<< Third Query >>]
| stats count first(Time) as Time First(Item) as Item << repeat for all fields >> by 'User Id'
| search count = 1
| fields - count


This works under the following assumptions:

  • the same 'User id' exists only once in the results from first  query
  • there exist a event with the same 'User id' in the first query for every event from the second and third  query
0 Karma
Get Updates on the Splunk Community!

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...