topic Exclusive Right Join option in splunk in Splunk Search

Exclusive Right Join option in splunk

kiril123 — Thu, 02 Nov 2017 15:51:33 GMT

I am trying to list the events from the subsearch which are not found in the main search.

For example the subsearch returns the table with the following records:

B
C
D

And the main search returns the following records:

A
B
C

So the final result will be "D"

There is an "Exclusive Right Join" concept in SQL.

Can i achieve something similar in Splunk? I have looked at the "join" command documentation but it does not support this specific type.

Re: Exclusive Right Join option in splunk

kamlesh_vaghela — Thu, 02 Nov 2017 16:03:57 GMT

Hi
Can you share your Sample main search and sub-search ( remove/ fake confidential value in search ) ?? So we can help you.

Thanks

Re: Exclusive Right Join option in splunk

elliotproebstel — Thu, 02 Nov 2017 17:23:52 GMT

There are several ways of going about what you're requesting. I'll suggest one, and if it doesn't work for you, maybe you can provide more details about your specific use case, and I'll try again.

Let's say you have a main search and a subsearch that both return events containing the field unique_id, and the value of the unique_id field matches your map above (results from the subsearch include unique_id=B, unique_id=C, and unique_id=D; results from the main search include unique_id=A, unique_id=B, unique_id=C). So your goal would be to retain the event with unique_id=D. Then I'd structure the approach like this:
base search | eval found_in_base_search=1 | append [ subsearch | eval found_in_sub_search=1 ] | eventstats values(found_in_base_search) AS found_in_base_search BY unique_id | where isnull(found_in_base_search) AND isnotnull(found_in_sub_search)

The eventstats portion of the search will apply the field found_in_base_search across all events sharing the same unique_id, so then we just filter down to events that contain the field found_in_sub_search but do not contain the field found_in_base_search.

Re: Exclusive Right Join option in splunk

elliotproebstel — Tue, 07 Nov 2017 20:39:35 GMT

Did this work for you, @kiril123?

Re: Exclusive Right Join option in splunk

kiril123 — Wed, 08 Nov 2017 20:57:45 GMT

No, this doesn't work for me. To test this for both main search and sub-search I selected the same interval: "earliest=-15m@m latest=-5m@m". So in theory no events should be returned as time periods fully overlap. However this is not the case.

Re: Exclusive Right Join option in splunk

elliotproebstel — Thu, 09 Nov 2017 03:54:10 GMT

Hmm...those events that were returned - did they have values for found_in_base_search and/or found_in_sub_search? If I know where it's going wrong, I can help troubleshoot.