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!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...