conditional lookup with catch-all value

[thezen]

I am looking to retrieve the following a field from a lookup table depending on the lookup result of two fields as follow,

Current lookup table sample:

FIELD1  FIELD2  MATCHTAG    UNMATCHEDTAG
A       123     string1 string2
B       456     string3 string4

All of the events will always have a matching value for field1, but not on field2. Those event which has a matching for both field1 and field2 will have to append the corresponding MATCHTAG field value (string1), otherwise (if it doesn't have matching value for FIELD2) it will have to append "string2" instead to the result set (which will also contain the matching events).

Event Sample,

A 123 
A 789
B 789

Ideal Result,

A 123 string1
A 789 string2
B 789 string4

I know we can do a lookup and return the non-matching result set, but not so sure on how I can append the UNMATCHEDTAG to the result set together.

Appreciate any suggestions on this,

[Vijeta]

You can try this , I don't know the field names so you will have t change in query accordingly

index=<yourindex> |join fields1[|inputlookup <yourlookup>.csv |rename FIELD2 as lookup_field2| fields Flookup_field2 MATCHED UNMATCHED]| eval string=if(field2= lookup_field2, MATCHED,UNMATCHED)| fields field1 field2 string

[thezen · Accepted solution]

Managed to do it by using enabling wildcard on the field and then use mvindex to do catch-all.

[richgalloway]

@thezen, If your problem is resolved, please accept an answer to help future readers.

[harsmarvania57]

Hi @thezen,

Try below query

<yourBaseSearch>
| lookup yourlookup.csv raw_field1 AS FIELD1, raw_field2 AS FIELD2 OUTPUT MATCHTAG AS MATCHTAG, UNMATCHEDTAG AS UNMATCHEDTAG 
| eval merged_tag = coalesce(MATCHTAG, UNMATCHEDTAG)

[thezen]

thanks but it doesn't seem to work in my case, I need all of the original events including the one which is not mached to be part of the result.