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
The image you shared earlier appeared to have no space between ShipLine and PrimeNo - try
| rex max_match=0 "ShipLine PrimeNo\=\"(?<PNO1>[^\"]+)\" Q"
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.
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:
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
NO, its not giving any results because joined with "lineprime"
Because lineprime is not populating properly in the second table.
see below:
Sorry, missed mvexpand for PNO1 - try this
| rex max_match=0 "ShipLine PrimeNo\=\"(?<PNO1>.*). Q"
| mvexpand PNO1
| table LineNo PNO1
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
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
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 |
You are not looking at what I suggested carefully enough - there is a mvexpand inside the join query not outside
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 |
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 ]
See above screenshot, giving eval logic values are not coming.
That image was which I tried earlier and now I shared
The image you shared earlier appeared to have no space between ShipLine and PrimeNo - try
| rex max_match=0 "ShipLine PrimeNo\=\"(?<PNO1>[^\"]+)\" Q"
Thank you so much for the help.
Hi @Annna,
Assuming you have these tables in separate events; you can try something below;
| stats values(Code) as Code by LineNo PrimeNo