Example of left outer join in Splunk without using join?

[anwarmian]

Please help me with a good example of Left Outer Join in Splunk without using "Join." I've seen examples of Inner Join without using "Join", but I haven't found one for Left Outer Join.

Thanks in advance!!!

[martin_mueller]

Say you have sourcetypes one with field bar and two with field baz, and a common field foo. You'd search that using sourcetype=one OR sourcetype=two, or generate dummy data using this:

| stats count as sourcetype | eval sourcetype="one" | eval foo = "a b c"| makemv foo | mvexpand foo | eval bar = random()%42 | append [stats count as sourcetype | eval sourcetype="two" | eval foo = "b c d" | makemv foo | mvexpand foo | eval baz = random()%42]

That generates three events a,b,c from sourcetype one and three events b,c,d from sourcetype two. Left outer joining those should yield three events a,b,c, with no value for baz in case of a because sourctype two didn't have that.

Append this to compute that join:

| stats values(sourcetype) as sourcetype values(bar) as bar values(baz) as baz by foo | search sourcetype="one"

You should get a result like this:

foo  sourcetype  bar  baz
a    one           6
b    one           7   41
     two
c    one           8   18
     two

Leave off the final search to get a full outer join.

[martin_mueller]

They do different things, so they aren't really comparable.

There's no need to use append in this case. My dummy data generator uses it, but it's not meant to churn out many events quickly.
Don't:

sourcetype=one | some stuff | append [search sourcetype=two | some stuff] | stats ... by foo

Do:

sourcetype=one OR sourcetype=two | some stuff | stats ... by foo

That'll be miles faster than either join or append based approaches.

[weidertc]

If "one" is already ran as a base search, would it still be faster to re-run one in one OR two, or would it then make sense to use append if you decide to re-use the one base search? this is my situation.

[to4kawa]

dashboard?
query optimization is specific. it's better you provide your sample query.

[anwarmian]

Thanks so much Martin. I'll try it out. Since I heard that "join" is expensive would "append" make the search faster?