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!!!
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.
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.
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.
dashboard?
query optimization is specific. it's better you provide your sample query.
Thanks so much Martin. I'll try it out. Since I heard that "join" is expensive would "append" make the search faster?