Unexpected inner join results

[xiangtaner]

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

[javiergn · Accepted solution]

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

[MuS]

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]

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

[MuS]

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