Mulit lookup with same event-field, lookup-destfield and event-destfield

[Lukas85]

Hi All

I'm new on splunk and have following problem.

We need data from a table depending on the value of a variable. For this lookup is the right function i think. The special on that csv/file is the value can be in different columns. Return data will be always the same. 

The csv table has following structur: 

column1	column2	column3	column4	column5
441	F205E	77889		22558
441	F204E	77998	44556	33669
442	G2071	88992	66557
442			11223	11559

it's possible there is no value inside a field in the table.  In case we won't found a match on column2 we search in column 4 and last in column6. 

I tried it as follow:

| lookup Inventar_SBB column2   as Test_field output    "column1" as "value1" , "column3" as "value2", "column4" as "value3", "column5" as "value4" 
| lookup Inventar_SBB "column4" as Test_field outputnew "column1" as "value1" , "column3" as "value2", "column4" as "value3", "column5" as "value4" 
| lookup Inventar_SBB "column5" as Test_field outputnew "column1" as "value1" , "column3" as "value2", "column4" as "value3", "column5" as "value4"

It looks like lookup function returns NULL in case there is no match.  With outputnew it won't overwrite the NULL value and with output it always overwrite my values with NULL  

I tried with different variable names for "value" in each lookup and compare it to NULL but this was also not working. 

Any idea what could be the workaround for this problem?

Thanks in advance for any help.

[efika]

Hi @Lukas85 ,

Read the full set of column and use the function coalesce ( "This function takes an arbitrary number of arguments and returns the first value that is not NULL.")

| makeresults 1 
| eval column2=NULL,column3=NULL, column4=11223,column5=11559
| eval result= coalesce(column2,column3,column4,column5)