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

[HaPPo]

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 valueF1_a valueF2_e NULL NULL valueF5_e
A valueF1_b valueF2_d valueF3_c valueF4_c valueF5_d
A valueF1_a valueF2_c valueF3_c valueF4_b valueF5_c
A valueF1_a valueF2_b valueF3_a valueF4_b valueF5_b
A NULL valueF2_a NULL valueF4_a 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 valueF1_b valueF2_b valueF3_a valueF4_c 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.

[somesoni2]

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