Is cartesian product not possible in splunk using join?

[asingla]

I am using a join search command. What I noticed is that join only takes one row from the sub search result for the joining field though my subsearch has more than one row on the joining field.
In short it's not doing the Cartesian product as done by joins in SQL.

e.g. search result for the outer search is

host

host1

host2

and subsearch return the following

host instance status

host1, 1, ON

host1, 2, OFF

host2, 1, OFF

the join on host returns

host1,1,ON (or host1,2,OFF)

host2,1,OFF

Is there anyway to get the Cartesian product in splunk?

[gkanapathy · Accepted solution]

You need to set the max parameter on the join command to something higher than the default of 1: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Join

Also, I would suggest, depending what you're doing, that you'd be much better off using either a subsearch or a lookup if possible, as they are much more efficiently implemented in Splunk than the join command.