Splunk Search

joining tables miss some values

smolcj
Builder

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

Tags (1)
0 Karma

qk9962549
New Member

join max=0

0 Karma

jrodriguezap
Communicator

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!

0 Karma

troywollenslege
Path Finder

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

0 Karma

smolcj
Builder

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  ]

0 Karma

cramasta
Builder

what happens if you use fillnull?

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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")]
0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!