How to search for a value in multiple fields

[robinettdonWY]

I have 2 sources in separate indexes; the first contains a field "appId"; to get the human readable (appDisplayName) I need to search the 2nd source. Normally I'd do this with a subsearch:

index=index2 sourcetype=st2 
    [search index=index1 sourcetype=st1 | stats values(appId) as appId |format]
|stats values(appDisplayName) as appDisplayName by appId

The problem I'm running into is the ID for the app in the first source is always called "appId" but depending on the type of app (which i don't know from the first source); appId will either correspond to 1 of 2 fields in the second source; it will either be "appId" or "resourceId". I need to find "appId" from the 1st source in either "appId" or "resourceId" in the second source and the corresponding human readable will either be "appDisplayName" or "resourceDisplayName" in the second source.

Any ideas on how to approach this?

Thanks!

[DalJeanis · Accepted solution]

Don't use a subsearch where the stats can handle connecting the two. This is called the "Splunk soup" method.

(index=index2 sourcetype=st2) OR 
(index=index1 sourcetype=st1)
 | fields appId, resourceId appDisplayName resourceDisplayName
 | rename COMMENT as "above selects only the record types and fields you need" 

 | rename COMMENT as "create synthetic fields as per diogofm answer" 
 | eval appId = coalesce(appId, resourceId)
 | eval appDisplayName = colaesce(appDisplayName, resourceDisplayName)

 | rename COMMENT as "stats together for final result" 
 | stats values(appDisplayName) as appDisplayName by appId

[diogofgm]

You can always eval the multiple fields into one using coalesce.

|eval appId = coalesce(appId, resourceId)
|eval appDisplayName = colaesce(appDisplayName, resourceDisplayName)

A nice blog post about coalesce:
https://www.splunk.com/en_us/blog/tips-and-tricks/search-command-coalesce.html

[robinettdonWY]

Thanks! I wish I could accept both answers.