Splunk Search
Highlighted

Search using outer join fails to return all matching events

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)
Highlighted

Re: Search using outer join fails to return all matching events

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

Highlighted

Re: Search using outer join fails to return all matching events

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

Highlighted

Re: Search using outer join fails to return all matching events

SplunkTrust
SplunkTrust

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

0 Karma