Splunk Search

Join, appendcols how to collect data from several events and combine them into one row?

New Member

Hi.

I am building up a table with a row for each key. Each row is build up by selecting field values from different events with the same key. What is the best practice of setting this multi-search row?

Each event is an update that only fills the fields used in that event. Therefore we get following rows:

Key Field1 Field2 Field3 Field4 Field5
A valueF1a valueF2e NULL NULL valueF5e
A valueF1
b valueF2d valueF3c valueF4c valueF5d
A valueF1a valueF2c valueF3c valueF4b valueF5c
A valueF1
a valueF2b valueF3a valueF4b valueF5b
A NULL valueF2a NULL valueF4a valueF5_a

There are several keys(A,B,C,D,...) in the index and I would need to show one row for each key by the same "rule" set by selecting values based on a. values from other fields or b. first/last updated value by time. Result would look like this:

Key Field1 Field2 Field3 Field4 Field5
A valueF1b valueF2b valueF3a valueF4c valueF5_e

Field1: value when valueF5=valueF5_d
Field2: value from the first event notNULL
Field3: value from the first event not NULL
Field4: value from the last event not NULL
Field4: value from the last event not NULL

I have tried to set it up with "join" but not been successfull so far.

0 Karma
Highlighted

Re: Join, appendcols how to collect data from several events and combine them into one row?

SplunkTrust
SplunkTrust

Give this a try

your base search | eval Field1=if(Field5="valueF5_d",Field1,null() 
| stats values(Field1) as FIeld1 earliest(Field2) as Field2 earliest(Field3) as Field3 latest(Field4) as Field4 latest(Field5) as Field5 by KEY
0 Karma