Search using outer join fails to return all matching events

[Jaci]

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?

[sideview · Accepted solution]

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]

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

[gkanapathy]

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