topic Unexpected inner join results in Splunk Search

Unexpected inner join results

xiangtaner — Tue, 29 Sep 2020 09:27:52 GMT

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

Re: Unexpected inner join results

MuS — Thu, 21 Apr 2016 01:08:52 GMT

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

Re: Unexpected inner join results

javiergn — Thu, 21 Apr 2016 08:52:09 GMT

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

Re: Unexpected inner join results

xiangtaner — Thu, 21 Apr 2016 13:56:21 GMT

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

Re: Unexpected inner join results

MuS — Fri, 22 Apr 2016 11:47:18 GMT

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-join-append-or-use-of-subsearches.html to learn more about this topic.

cheers, MuS