Splunk Search

Example of left outer join in Splunk without using join?

anwarmian
Communicator

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

Tags (2)
0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

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.

0 Karma

weidertc
Communicator

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.

0 Karma

to4kawa
Ultra Champion

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

0 Karma

anwarmian
Communicator

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...