topic Re: joining tables miss some values in Splunk Search

joining tables miss some values

smolcj — Wed, 27 Feb 2013 05:17:23 GMT

hi,
i have 2 tables to join and when i am using outer join, i am able t join 2 tables but not able to join all the values from the second table if some of he values are missing

table 1

< no value

    1   we

    2   this

    3   that

    4   from

table 2

value some1 some2 some3

we     ddf   df   dfdf

this   sd     sf   sdfsd

that   sds    sds   sdfds

from                here 



when i am trying to join these two tables wth

 `......|join type=outer value [search source=file.txt|transaction threadname|table value1 some1 some2 some3]

here all the values are mapping finely but not the last row as there is empty fileds in that...
how to resolve this?
please help
thankyou

Re: joining tables miss some values

martin_mueller — Wed, 27 Feb 2013 14:56:32 GMT

Works for me using this as an example:

| gentimes start=-1 increment=5m | head 1 | eval no = "1,2,3,4" | makemv no delim="," | mvexpand no | eval value = case(no==1,"v1",no==2,"v2",no==3,"v3",no==4,"v4") | table no value | join type=outer value [gentimes start=-1 increment=5m | head 1 | eval value = "v1,v2,v3,v4" | makemv value delim="," | mvexpand value | table value | eval some3 = replace(value,"v","s3_") | eval some2 = case(value=="v1","s2_1",value=="v2","s2_2",value=="v3","s2_3") | eval some1 = case(value=="v1","s1_1",value=="v2","s1_2",value=="v3","s1_3")]

Re: joining tables miss some values

cramasta — Wed, 27 Feb 2013 15:09:08 GMT

what happens if you use fillnull?

`......|join type=outer value [search source=file.txt|transaction threadname| fillnull | table value1 some1 some2 some3]

Re: joining tables miss some values

smolcj — Mon, 28 Sep 2020 13:24:35 GMT

cramasta,, fillnull is not providing any change to the result..
exact search is

 index=main source=SUCCESS | stats values(a) by b| mvexpand values(a)| makemv delim="," values(a) | mvexpand values(a)  |rename values(a) as a|join type=outer a[search  index=main source=SUCCESS  |transaction startswith=WRT_1023 endswith=WRT_8233 |fillnull|table a c d  ]

Re: joining tables miss some values

troywollenslege — Thu, 06 Mar 2014 15:56:18 GMT

One thing to be careful is that you remember that join is case sensitive (for some reason)..

It doesn't look like this is the case in your example data, but something to keep in mind. Check out this posting.

http://answers.splunk.com/answers/47699/case-insensitive-join

Re: joining tables miss some values

jrodriguezap — Wed, 27 Aug 2014 22:08:29 GMT

Hi there
Someone could complete what smolcj mentioned? I have the same problem with null fields.
In your example, the result of the table is as follows:

no value some1 some2 some3
1  we     ddf   df   dfdf
2  this   sd    sf   sdfsd
3  that   sds   sds  sdfds
4  from                
4                      here

Help me please!

Re: joining tables miss some values

qk9962549 — Tue, 08 May 2018 09:21:13 GMT

join max=0