Why is coalesce working only for one of the two fields I am combining, depending on the sequence the fields are being combined?

[christopheryu]

I have two existing fields - "narrative" and "alarm_type" that I am trying to combine into a new single field "alert_msg", which is a header on my lookup table. However, my search is only returning one of the two existing fields depending on the order they are placed on my coalesce command. Searches shown below:

index=foo sourcetype=bar
| eval alert_msg=coalesce(narrative, alarm_type)
| search [| inputlookup triggers | fields alert_msg]
| table alert_msg

returns existing "narrative" field only:

alert_msg
Major alarm set, FPC 3 Major Errors

Switching the fields' sequence on the coalesce command:

index=foo sourcetype=bar
| eval alert_msg=coalesce(alarm_type, narrative)
| search [| inputlookup triggers | fields alert_msg]
| table alert_msg

returns existing "alarm_type" field only:

alert_msg
%DAEMON-3-JTASK_SCHED_SLIP

This is the result I am expecting:

alert_msg
%DAEMON-3-JTASK_SCHED_SLIP
Major alarm set, FPC 3 Major Errors

[micahkemp]

Looking at your use case, I wonder if you'd be better served doing something like:

<base search> 
    [| inputlookup triggers 
    | eval search_fields="narrative=".alert_msg." OR alarm_type=".alert_msg 
    | table search_fields 
    | mvcombine search_fields 
    | eval search=mvjoin(search_fields, " OR ") 
    | table search]

The last | table search isn't necessary, but by leaving it in the example it makes it easier for you to run as a standalone search to see what the subsearch returns.

When a subsearch returns a field named search, that field's value is dropped directly into your search where the [] was. So this will result in a search like:

<base search> narrative=A OR alarm_type=A OR narrative=B OR alarm_type=B

[micahkemp · Accepted solution]

It sounds like coalesce is doing exactly what it's supposed to do: return the first non-NULL value you give it.

Perhaps you are looking for mvappend, which will put all of the values passed to it into the result:

| eval allvalues=mvappend(value1, value2)

[christopheryu]

Thank you for the response. Still not capturing all the results I want. I am vetting results by doing this search:

| search [| inputlookup triggers | fields alert_msg]
| rename alert_msg as query

Using the above search, 6 events are being returned. Using your suggested search,

| eval alert_msg=mvappend(narrative, alarm_type)
| search [| inputlookup triggers | fields alert_msg]

Only 5 are being returned, missing one event "Major alarm set, CB 1 ESW PFE Port Fail" that has a narrative field matching value on the lookup table. If i change my search to this:

| eval alert_msg=mvappend(narrative, alarm_type)
| search [| inputlookup triggers | fields alert_msg] OR narrative="Major alarm set, CB 1 ESW PFE Port Fail"
| table alert_msg

All 6 events are being returned, with "Major alarm set, CB 1 ESW PFE Port Fail" being one of the new alert_msg field value. Also, I need to keep the alert_msg field for use on further data processing.