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!

Observe and Secure All Apps with Splunk

  Join Us for Our Next Tech Talk: Observe and Secure All Apps with SplunkAs organizations continue to innovate ...

Splunk Decoded: Business Transactions vs Business IQ

It’s the morning of Black Friday, and your e-commerce site is handling 10x normal traffic. Orders are flowing, ...

Fastest way to demo Observability

I’ve been having a lot of fun learning about Kubernetes and Observability. I set myself an interesting ...