Splunk Search

Search using outer join fails to return all matching events

Jaci
Splunk Employee
Splunk Employee

Search fails to correctly return all matching events when performing outer joins. The search below illustrates the problem (only one row is returned):

  • | head 1 | eval somefield="matchthis" | stats count by somefield | join type=outer somefield [ search * | head 10 | eval somefield="matchthis" | eval joined="success" | fields + somefield,joined ]

The first search creates a field call "somefield" that ought to be matched within the sub-search. The sub-search returns 10 matching events and therefore the results ought to include all 10 events from the sub-search. The search does return an event that matches (indicated by the fact that the field "joined" is returned with a value of "success") however, only one event is returned:

| somefield | count | joined | 1 | matchthis | 1 | success |

Is this correct?

Tags (2)
1 Solution

sideview
SplunkTrust
SplunkTrust

Yes this is confusing but consistent given that the join command's type="outer" is technically a 'left outer' join.

If you flip the two searches, such that the one with 10 rows is in the main search, and the one with 1 row is in the subsearch, you'll get the result you expect.
(Note: as per Gerald's comment below you will also need to set max=0 or you'll still get one row since all the 'matchthis' values are the same)

See the discussion between 'left outer' and 'right outer' joins in SQL here: http://en.wikipedia.org/wiki/Join_%28SQL%29#Left_outer_join

as well as this related answers question here: http://answers.splunk.com/questions/3291/can-the-join-command-only-give-me-inner-joins-and-left-oute...

and here's the reference page for join if you want to read more about the 'max' argument. http://www.splunk.com/base/Documentation/latest/SearchReference/Join

View solution in original post

sideview
SplunkTrust
SplunkTrust

Yes this is confusing but consistent given that the join command's type="outer" is technically a 'left outer' join.

If you flip the two searches, such that the one with 10 rows is in the main search, and the one with 1 row is in the subsearch, you'll get the result you expect.
(Note: as per Gerald's comment below you will also need to set max=0 or you'll still get one row since all the 'matchthis' values are the same)

See the discussion between 'left outer' and 'right outer' joins in SQL here: http://en.wikipedia.org/wiki/Join_%28SQL%29#Left_outer_join

as well as this related answers question here: http://answers.splunk.com/questions/3291/can-the-join-command-only-give-me-inner-joins-and-left-oute...

and here's the reference page for join if you want to read more about the 'max' argument. http://www.splunk.com/base/Documentation/latest/SearchReference/Join

sideview
SplunkTrust
SplunkTrust

Oh of course. Cause the "matchthis" values are all the same. Good point.

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

To get 10 results if the main (i.e., left) search has 10 results, you also need to up the max parameter in the join, or you will still only get a single result, i.e., join type=left max=0 somefield [...]

Get Updates on the Splunk Community!

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 ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...