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
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
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
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
Thanks MuS and javiergn for the inputs, javiergn's way works perfect!
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