topic Re: Example of left outer join in Splunk without using join? in Splunk Search

Example of left outer join in Splunk without using join?

anwarmian — Wed, 30 Jul 2014 00:29:39 GMT

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!!!

Re: Example of left outer join in Splunk without using join?

martin_mueller — Wed, 30 Jul 2014 00:50:53 GMT

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.

Re: Example of left outer join in Splunk without using join?

anwarmian — Wed, 30 Jul 2014 04:00:52 GMT

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

Re: Example of left outer join in Splunk without using join?

martin_mueller — Wed, 30 Jul 2014 07:09:25 GMT

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.

Re: Example of left outer join in Splunk without using join?

weidertc — Thu, 09 Jan 2020 20:44:27 GMT

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.

Re: Example of left outer join in Splunk without using join?

to4kawa — Thu, 09 Jan 2020 21:30:00 GMT

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