Trying to combine data within the same table under certain conditions

[Jimenez]

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.

[ITWhisperer]

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?

[Jimenez]

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.

[ITWhisperer · Accepted solution]

Try something like this

| makeresults format=csv data="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"
| table A B C D E F
``` The lines above create dummy event data in line with your example (it is often helpful if you had provided this yourself) ```
| appendpipe
    [| rename A as a
    | rename B as b
    | eval C=null()
    | eval D=null()
    | eval E=null()
    | eval F=null()]
| fields - A B
| appendpipe
    [| where isnotnull(C)
    | eval A=null()
    | eval B=null()
    | eval a=C
    | rename C as c
    | rename D as d
    | eval E=null()
    | eval F=null()]
| fields - C D
| appendpipe
    [| where isnotnull(E)
    | eval A=null()
    | eval B=null()
    | eval C=null()
    | eval D=null()
    | eval a=E
    | rename E as e
    | rename F as f]
| fields - E F
| where isnotnull(a)
| stats values(*) as * by a
| rename a as A
| rename b as B
| rename c as C
| rename d as D
| rename e as E
| rename f as F

[Jimenez]

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. 

[ITWhisperer]

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.