topic Re: Trying to combine data within the same table under certain conditions in Splunk Search

Trying to combine data within the same table under certain conditions

Jimenez — Wed, 29 Nov 2023 09:27:24 GMT

Hi all,

First of all thank you for your time. I am quite new to splunk and I am struggling with this issue for some time but it seems quite more challenging than I initially expected.

I have this following sample data in tabular form:

A	B	C	D	E	F
0.1	b1	0.1	d1	0.1	f1
0.11	b2	0.2	d2	0.35	f2
0.2	b3	0.3	d3	0.9	f3
0.22	b4			1.0	f4
0.4	b5
0.5	b6
0.55	b7
0.9	b8

and I need to generate something like:

A	B	C	D	E	F
0.1	b1	0.1	d1	0.1	f1
0.11	b2
0.2	b3	0.2	d2
0.22	b4
0.3		0.3	d3
0.35				0.35	f2
0.4	b5
0.5	b6
0.55	b7
0.9	b8			0.9	f3
1.0				1.0	f4

So, first I need to merge column A with C and E sorted and then I need to make columns C and E match with column A including data in columns D and F respectively.

I guess there is an easy way to achieve this. I have tried with joins but I cannot make it work.

Any help would be much appreciated.

Re: Trying to combine data within the same table under certain conditions

ITWhisperer — Wed, 29 Nov 2023 11:43:19 GMT

It looks like your sample data has been created with a couple of appendcols. If this is the case, perhaps it would be easier to go back a step and try and create your desired output from the original searches?

Re: Trying to combine data within the same table under certain conditions

Jimenez — Wed, 29 Nov 2023 11:47:05 GMT

Hi @ITWhisperer ,

Actually I created the data by hand to kind of simplify the actual situation. Data is unfortunately sensitive and I cannot show too much. I did not use any appendcol for the actual data but eval with if conditions. Thanks.

Re: Trying to combine data within the same table under certain conditions

ITWhisperer — Wed, 29 Nov 2023 12:29:42 GMT

Try something like this

Re: Trying to combine data within the same table under certain conditions

Jimenez — Thu, 30 Nov 2023 07:15:54 GMT

Thanks a lot @ITWhisperer . Works like a charm.

Sorry I am pretty newbie in splunk and did not even know the existence of command makeresults 😅

One short question, could this be do more flexible? I mean, if columns are named like

A1 B1 A2 B2 A3 B3 instead of A B C D E F

is there a way to make this in a "loop"? I tried foreach, but appendpipe cannot be used within a foreach statement.

Thanks a lot for your time.

Re: Trying to combine data within the same table under certain conditions

ITWhisperer — Thu, 30 Nov 2023 09:16:02 GMT

Yes, you can use different column names

No, I don't think this method can be done in a loop, however, there may be other ways to solve this which might be applied in a loop, but it may depend on the column names and the relationship between the pairs of column names, e.g. A and A1, B and B1, etc.