Splunk Search

Unexpected inner join results

xiangtaner
Path Finder

Hi,

I have two tables like below:

table 1 (nl_t1.csv):
IP Source1
1 a
1 b

table 2 (nl_t2.csv):
IP Source2
1 c
1 d
1 e

when I conduct inner join using the following code:
| inputlookup nl_t1.csv
| join IP [|inputlookup nl_t2.csv]

The results is:
IP Source1 Source2
1 a c
1 a d
1 a e

It seems that it only used the first row of table 1. What I expected is the results should have 6 (2 X 3) rows like below:
IP Source1 Source2
1 a c
1 a d
1 a e
1 b c
1 b d
1 b e

Could anyone give an explanation or suggest a solution to get the right results?

Thanks,

Wayne

Tags (2)
0 Karma
1 Solution

javiergn
SplunkTrust
SplunkTrust

In case you are wondering why your join doesn't work as expected try this instead:

| inputlookup nl_t1.csv
| join IP max=0 [|inputlookup nl_t2.csv]

This is the way the max parameter works:

max
Syntax: max=<int>
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

View solution in original post

0 Karma

javiergn
SplunkTrust
SplunkTrust

In case you are wondering why your join doesn't work as expected try this instead:

| inputlookup nl_t1.csv
| join IP max=0 [|inputlookup nl_t2.csv]

This is the way the max parameter works:

max
Syntax: max=<int>
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
0 Karma

MuS
SplunkTrust
SplunkTrust

Just a side note: It can most likely be done without join .. See the March Session of @sideview here http://wiki.splunk.com/Virtual_.conf or this answer https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo... to learn more about this topic.

cheers, MuS

xiangtaner
Path Finder

Thanks MuS and javiergn for the inputs, javiergn's way works perfect!

0 Karma

MuS
SplunkTrust
SplunkTrust

Hi xiangtaner,

try using a stats instead:

 | inputlookup nl_t1.csv | inputlookup append=t nl_t2.csv | stats values(*) AS * by IP

Hope this helps ...

cheers, MuS

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...