Splunk Search

Left Join Not Returning All Fields

ryanprayacn
Explorer

So as an example:

Primary Table
Customer 1, 2, 3

Secondary Table
Customer 1,2,3,2
Spend 100, 200, 300, 400

Search: index=primary | join Customer [search index=secondary] | table Customer Spend

Output is
Customer 1, 2, 3
Spend 100, 400, 300

It is NOT returning all values from Customer 2. Need ALL values from secondary table, not just one. Please advise. Thanks!

0 Karma
1 Solution

Sukisen1981
Champion

hi - Firstly if you do not specifically specify the join type its takes default as inner - from splunk join documentation 'Syntax: type=inner | outer | left
Description: Indicates the type of join to perform. The difference between an inner and a left (or outer) join is how the events are treated in the main search that do not match any of the events in the subsearch. In both inner and left joins, events that match are joined. The results of an inner join do not include events from the main search that have no matches in the subsearch. The results of a left (or outer) join includes all of the events in the main search and only those values in the subsearch have matching field values.
Default: inner'
Second- max
Syntax: max=
Description: Specifies the maximum number of subsearch results that each main search result can join with. If set to max=0, there is no limit.
Default: 1
so probably what you need is - index=primary | join type=left Customer max=0 [search index=secondary] | table Customer Spend

You however need the inner / outer join in case you want common fields. The reason your query is working is because you have same values for customer in both searches. Without specifying a 'left' join type say if there was a customer value 4, you would not have got any returns from the sub search even if there was a customer with value 4 in the sub search.
BTW - Consider using stats, count, or appendcols and join as a last resort

View solution in original post

Sukisen1981
Champion

hi - Firstly if you do not specifically specify the join type its takes default as inner - from splunk join documentation 'Syntax: type=inner | outer | left
Description: Indicates the type of join to perform. The difference between an inner and a left (or outer) join is how the events are treated in the main search that do not match any of the events in the subsearch. In both inner and left joins, events that match are joined. The results of an inner join do not include events from the main search that have no matches in the subsearch. The results of a left (or outer) join includes all of the events in the main search and only those values in the subsearch have matching field values.
Default: inner'
Second- max
Syntax: max=
Description: Specifies the maximum number of subsearch results that each main search result can join with. If set to max=0, there is no limit.
Default: 1
so probably what you need is - index=primary | join type=left Customer max=0 [search index=secondary] | table Customer Spend

You however need the inner / outer join in case you want common fields. The reason your query is working is because you have same values for customer in both searches. Without specifying a 'left' join type say if there was a customer value 4, you would not have got any returns from the sub search even if there was a customer with value 4 in the sub search.
BTW - Consider using stats, count, or appendcols and join as a last resort

jkat54
SplunkTrust
SplunkTrust

stats as a last resort?

0 Karma

ryanprayacn
Explorer

Thank you! I had accidentally left out the "type=left" in my example. Not including "max" was my issue.

I agree that "join" is a last resort. How would you do this sample search with something more effective (since join is max 50,000). Thanks!

0 Karma

Sukisen1981
Champion

Consider using stats, count, or appendcols and THEN join ONLY as a last resort 🙂 🙂

jkat54
SplunkTrust
SplunkTrust

The power of words!

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