topic Re: For Xml data join 2 tables with different fields in Splunk Search

For Xml data join 2 tables with different fields

Annna — Fri, 26 Feb 2021 18:15:57 GMT

Table A:

Table B:

</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

Re: For Xml data join 2 tables with different fields

scelikok — Sun, 28 Feb 2021 11:58:38 GMT

Hi @Annna,

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

| stats values(Code) as Code by LineNo PrimeNo

Re: For Xml data join 2 tables with different fields

ITWhisperer — Sun, 28 Feb 2021 11:32:14 GMT

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.

Re: For Xml data join 2 tables with different fields

Annna — Mon, 01 Mar 2021 12:40:48 GMT

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

but i need final output be like:

Re: For Xml data join 2 tables with different fields

ITWhisperer — Mon, 01 Mar 2021 13:47:16 GMT

Will this work?

Re: For Xml data join 2 tables with different fields

Annna — Mon, 01 Mar 2021 14:10:12 GMT

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

Because lineprime is not populating properly in the second table.

see below:

Re: For Xml data join 2 tables with different fields

ITWhisperer — Mon, 01 Mar 2021 14:13:09 GMT

Sorry, missed mvexpand for PNO1 - try this

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

Re: For Xml data join 2 tables with different fields

Annna — Mon, 01 Mar 2021 14:31:44 GMT

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

Re: For Xml data join 2 tables with different fields

ITWhisperer — Mon, 01 Mar 2021 14:34:17 GMT

You shouldn't need the mvexpand after the join

Re: For Xml data join 2 tables with different fields

Annna — Mon, 01 Mar 2021 15:10:51 GMT

Sorry, Its not working out.

With the below query i am getting results like that.

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

Re: For Xml data join 2 tables with different fields

ITWhisperer — Mon, 01 Mar 2021 15:33:13 GMT

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

Re: For Xml data join 2 tables with different fields

Annna — Mon, 01 Mar 2021 16:17:41 GMT

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

Re: For Xml data join 2 tables with different fields

ITWhisperer — Mon, 01 Mar 2021 16:36:50 GMT

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 ]

Re: For Xml data join 2 tables with different fields

Annna — Mon, 01 Mar 2021 17:45:17 GMT

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

That image was which I tried earlier and now I shared

Re: For Xml data join 2 tables with different fields

ITWhisperer — Mon, 01 Mar 2021 19:31:56 GMT

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

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

Re: For Xml data join 2 tables with different fields

Annna — Tue, 02 Mar 2021 18:09:12 GMT

Thank you so much for the help.