Splunk Search

Exclusive Right Join option in splunk

kiril123
Path Finder

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.

0 Karma

elliotproebstel
Champion

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.

elliotproebstel
Champion

Did this work for you, @kiril123?

0 Karma

kiril123
Path Finder

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.

0 Karma

elliotproebstel
Champion

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.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

Thanks

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...