Search fails to correctly return all matching events when performing outer joins. The search below illustrates the problem (only one row is returned):
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?
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
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 [...]