Splunk Search

Trying to combine data within the same table under certain conditions

Jimenez
Explorer

 

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:

ABCDEF
0.1b10.1d10.1f1
0.11b20.2d20.35f2
0.2b30.3d30.9f3
0.22b4  1.0f4

0.4

b5    
0.5b6    
0.55b7    
0.9b8    

 

and I need to generate something like:

ABCDEF
0.1b10.1d10.1f1
0.11b2    
0.2b30.2d2  
0.22b4    
0.3 

0.3

d3  
0.35   0.35f2

0.4

b5    
0.5b6    
0.55b7    
0.9b8  0.9f3
1.0   1.0f4

 

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.

 

 

 

 

 

 

Labels (4)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

Jimenez
Explorer

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.

 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Explorer

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 :grinning_face_with_sweat:

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. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...