topic Re: left join multivalue in Splunk Search

left join multivalue

sfatnass — Fri, 29 Jul 2016 12:15:23 GMT

hi,

i try to use left join to match between two index.

index="myfirst_Index"   

|  rex max_match=0 field=multivalued_field "(.*?)(GET|POST)(?(.*?))$"  

|join type=left URL1  [|search index=mysecond_index  |eval URL1=URL |fields URL1 element1]
| table multivalued_field URL1 element1

when multivalued_field contain only one value the join work fine and i obtain what i need on element1, but when i have multi value no match found.

how can i do to correct my request ?

thx

Re: left join multivalue

javiergn — Fri, 29 Jul 2016 12:20:02 GMT

Hi, could you modify your question and include the query between code labels?
Otherwise when you post that it'll trim special HTML characters.

Re: left join multivalue

javiergn — Fri, 29 Jul 2016 12:22:06 GMT

You could try to expand your multivalued field before the join:

index="myfirst_Index"
| rex max_match=0 field=multivalued_field "(.?)(GET|POST)(?(.?))$"
| mvexpand URL1
| join type=left URL1 [|search index=mysecond_index |eval URL1=URL |fields URL1 element1]
| table multivalued_field URL1 element1

Careful when using join though (performance, limits in outputs, etc).
See the following answers:

https://answers.splunk.com/answers/221304/alternatives-to-join-with-two-matching-event-field.html
https://answers.splunk.com/answers/387510/what-are-alternatives-to-using-the-join-command-fo.html
https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-join-append-or-use-of-subsearches.html

Thanks,
J

Re: left join multivalue

sfatnass — Fri, 29 Jul 2016 12:41:30 GMT

your solution not help me really i have to conserve URL1 like multivalue, because i need to get element1 like multivalued field.
otherwise if i use lookup and not index for mysecond_index how can i do?

Re: left join multivalue

javiergn — Fri, 29 Jul 2016 13:03:26 GMT

Then use mvexpand first, then join, then back to multivalue by using stats values or something similar.

Or use one of the join alternatives I linked in my previoius answers. It'll be a bit more complicated to implement but it will be faster

Re: left join multivalue

sfatnass — Fri, 29 Jul 2016 13:10:14 GMT

the request using stats don't work