For Xml data join 2 tables with different fields

[Annna]

Table A:

<Type  LineNo="589123363" > <Type> 

<OrderLine Line="6" PrimeNo="3" S="2"> Code="75602005" /> </OrderLine>  

<OrderLine Line="6" PrimeNo="4" S="3"> Code="95602015" /> </OrderLine>  

<OrderLine Line="6" PrimeNo="2" S="4"> Code="65602025" /> </OrderLine>  

<OrderLine Line="6" PrimeNo="1" S="5"> Code="55602305" /> </OrderLine>  

<OrderLine Line="6" PrimeNo="5" S="6"> Code="65602405" /> </OrderLine>  

Table B:

<Type  LineNo="589123363" > <Type> 

<ShipLines>

<ShipLine PrimeNo="2" Q="1"/>

<ShipLine PrimeNo="3" Q="2"/>

<ShipLine PrimeNo="4" Q="4"/>

<ShipLine PrimeNo="1" Q="3"/>

</ShipLines>

Need to join above 2 tables with LineNo, PrimeNo and get the code.

I tired but values is not coming properly in one table values are coming in  horizontal and second table values coming vertical. please help me out.

Output:

LineNo             PrimeNo           Code

589123363     1                      55602305

589123363     2                   65602025

589123363     3                      75602005

589123363     4                      95602015

[ITWhisperer]

With the join command in splunk, you are joining the results of two searches at an event by event level. What events are you getting from your search of the first set of data? How have you extracted PrimeNo and Code? Same for the second set of data.

[Annna]

after joining 2 tables my result is like below and shipline prime is not split properly, using mvexpand also no use. i tried many ways but no use.

Using below query getting output like below and help me with correct one

index=A earliest=-120d@d latest=now
| dedup LineNo
| eval new=mvzip(PrimeNo, Code)
| mvexpand new
| makemv delim="," new
| eval N_PrimeNo=mvindex(new,0)
| eval N_Code=mvindex(new,1)
| table LineNo N_PrimeNo N_Code
| join LineNo
[search index=B
| dedup Order | rex max_match=0 "(?<ShipLine>ShipLine)\s"
| search ShipLine=ShipLine
| rex max_match=0 "ShipLine PrimeNo\=\"(?<PNO1>.*). Q"
| table LineNo PNO1]
| table LineNo N_PrimeNo PNO1 N_Code
| mvexpand PNO1

but i need final output be like:

[ITWhisperer]

Will this work?

index=A earliest=-120d@d latest=now
| dedup LineNo
| eval new=mvzip(PrimeNo, Code)
| mvexpand new
| makemv delim="," new
| eval N_PrimeNo=mvindex(new,0)
| eval N_Code=mvindex(new,1)
| table LineNo N_PrimeNo N_Code
| eval lineprime=LineNo."|".N_PrimeNo
| join lineprime
[search index=B
| dedup Order | rex max_match=0 "(?<ShipLine>ShipLine)\s"
| search ShipLine=ShipLine
| rex max_match=0 "ShipLine PrimeNo\=\"(?<PNO1>.*). Q"
| table LineNo PNO1
| eval lineprime=LineNo."|".PNO1]
| eval lineprime=split(lineprime,"|")
| eval LineNo=mvindex(lineprime,0)
| eval PrimeNo=mvindex(lineprime,1)
| table LineNo PrimeNo N_Code

[Annna]

NO, its not giving any results because joined with "lineprime"

Because lineprime is not populating properly in the second table.

see below:

[ITWhisperer]

Sorry, missed mvexpand for PNO1 - try this

| rex max_match=0 "ShipLine PrimeNo\=\"(?<PNO1>.*). Q"
| mvexpand PNO1
| table LineNo PNO1

[Annna]

after Joining with 2 tables mvexpand is not working. Results not found

| mvexpand PNO1

that mvexpand while using individual table its worked out.

While joining 2 tables only its not split

[ITWhisperer]

You shouldn't need the mvexpand after the join 

index=A earliest=-120d@d latest=now
| dedup LineNo
| eval new=mvzip(PrimeNo, Code)
| mvexpand new
| makemv delim="," new
| eval N_PrimeNo=mvindex(new,0)
| eval N_Code=mvindex(new,1)
| table LineNo N_PrimeNo N_Code
| eval lineprime=LineNo."|".N_PrimeNo
| join lineprime
[search index=B
| dedup Order | rex max_match=0 "(?<ShipLine>ShipLine)\s"
| search ShipLine=ShipLine
| rex max_match=0 "ShipLine PrimeNo\=\"(?<PNO1>.*). Q"
| table LineNo PNO1
| mvexpand PNO1
| eval lineprime=LineNo."|".PNO1]
| eval lineprime=split(lineprime,"|")
| eval LineNo=mvindex(lineprime,0)
| eval PrimeNo=mvindex(lineprime,1)
| table LineNo PrimeNo N_Code

[Annna]

Sorry, Its not working out.

With the  below query i am getting results like that.

index=A earliest=-120d@d latest=now
| dedup LineNo
| eval new=mvzip(PrimeNo, Code)
| mvexpand new
| makemv delim="," new
| eval N_PrimeNo=mvindex(new,0)
| eval N_Code=mvindex(new,1)
| table LineNo N_PrimeNo N_Code
| join LineNo
[search index=B
| dedup Order | rex max_match=0 "(?<ShipLine>ShipLine)\s"
| search ShipLine=ShipLine
| rex max_match=0 "ShipLine PrimeLineNo\=\"(?<PNO1>.*). Q"
| table LineNo PNO1]
| table LineNo N_PrimeLineNo PNO1 N_Code
| mvexpand PNO1

MY expecting output be like after giving where N_PrimeNo = PNO1

LineNo	N_PrimeNo	PNO1	N_Code
288136957	1	1	66871812
588136313	1	1	66871812
588136313	3	3	65602005
588136313	4	4	69101805
488136313	1	1	66871812
488136313	2	2	65252707
488136313	3	3	65602005
488136313	4	4	69101805

[ITWhisperer]

You are not looking at what I  suggested carefully enough - there is a mvexpand inside the join query not outside

[Annna]

sorry i wrongly copied the results, i am keeping the mvexpand command inside only.

Suggested query no results found

Mvexpand inside the join results. Its not showing correctly.

MY expecting output be like after giving where N_PrimeNo = PNO1

LineNo	N_PrimeNo	PNO1	N_Code
288136957	1	1	66871812
588136313	1	1	66871812
588136313	3	3	65602005
588136313	4	4	69101805
488136313	1	1	66871812
488136313	2	2	65252707
488136313	3	3	65602005
488136313	4	4	69101805

[ITWhisperer]

You also need the eval to create lineprime inside the joined search but it doesn't appear to be in your image

| mvexpand PNO1
| eval lineprime=LineNo."|".PNO1 ]

[Annna]

See above screenshot, giving eval logic values are not coming. 

That image was which I tried earlier and now I shared 

[ITWhisperer · Accepted solution]

The image you shared earlier appeared to have no space between ShipLine and PrimeNo - try

| rex max_match=0 "ShipLine PrimeNo\=\"(?<PNO1>[^\"]+)\" Q"

[Annna]

Thank you so much for the help. 

[scelikok]

Hi @Annna,

Assuming you have these tables in separate events; you can try something below;

| stats values(Code) as Code by LineNo PrimeNo