left join multivalue

[sfatnass]

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

[javiergn]

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-jo...

Thanks,
J

[sfatnass]

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?

[javiergn]

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

[sfatnass]

the request using stats don't work

[javiergn]

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