Splunk Search

For Xml data join 2 tables with different fields

Annna
Explorer

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

 

 

 

Labels (1)
0 Karma
1 Solution

ITWhisperer
Ultra Champion

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

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

 

View solution in original post

ITWhisperer
Ultra Champion

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.

0 Karma

Annna
Explorer

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

Annna_0-1614592267262.png

but i need final output be like:

Annna_1-1614592303535.png

 

0 Karma

ITWhisperer
Ultra Champion

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
0 Karma

Annna
Explorer

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

Because lineprime is not populating properly in the second table.

see below:

Annna_1-1614607804478.png

 

0 Karma

ITWhisperer
Ultra Champion

Sorry, missed mvexpand for PNO1 - try this

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

Annna
Explorer

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

0 Karma

ITWhisperer
Ultra Champion

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
0 Karma

Annna
Explorer

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

Annna_0-1614610891431.png

 

MY expecting output be like after giving where N_PrimeNo = PNO1

LineNo N_PrimeNoPNO1N_Code
2881369571166871812
5881363131166871812
5881363133365602005
5881363134469101805
4881363131166871812
4881363132265252707
4881363133365602005
4881363134469101805
0 Karma

ITWhisperer
Ultra Champion

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

0 Karma

Annna
Explorer

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

Suggested query no results found

Annna_0-1614614982934.png

Mvexpand inside the join results. Its not showing correctly.

Annna_2-1614615446141.png

 

MY expecting output be like after giving where N_PrimeNo = PNO1

LineNo N_PrimeNoPNO1N_Code
2881369571166871812
5881363131166871812
5881363133365602005
5881363134469101805
4881363131166871812
4881363132265252707
4881363133365602005
4881363134469101805

 

0 Karma

ITWhisperer
Ultra Champion

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 ]
0 Karma

Annna
Explorer

Annna_0-1614619030424.png

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

That image was which I tried earlier and now I shared 

0 Karma

ITWhisperer
Ultra Champion

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

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

 

View solution in original post

Annna
Explorer

Thank you so much for the help. 

0 Karma

scelikok
Influencer

Hi @Annna,

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

 

| stats values(Code) as Code by LineNo PrimeNo

 

If this reply helps you an upvote is appreciated.
0 Karma