Trying to match 2 fields in 2 different sources by wildcard

[fotc1969]

I have 2 sources with different pieces information and i'm trying to return a coalesced search based on a partial match between them

source_1
lookupfield = "dept_LV"
location = "Las Vegas"
costcenter = "33422345"

source_2
actual_field = "dept_LV_subdivision_2"
building_number="22"
caption = "Accounting"
actual_field = "dept_LV_subdivision_3"
building_number="28"
caption = "Engineering"

source = "source_1" OR source = "source_2" | eval matchID=coalesce(lookupfield, actual_field) | search matchID = "dept_LV" | table costcenter, location, building_number, caption

from this i would expect to get the aggregated info from both sources for all actual_fields that match dept_LV. the problem is i dont think coalesced searches allow partial matches. how would i achieve this otherwise?

[elliotproebstel]

I think you're almost there. Have you tried this:

source = "source_1" OR source = "source_2" 
| eval matchID=coalesce(lookupfield, actual_field) 
| search matchID = "dept_LV*" 
| table costcenter, location, building_number, caption

To make it even more efficient, I'd start out by including that wildcarded term in the original search:

source = "source_1" OR source = "source_2" dept_LV*
| eval matchID=coalesce(lookupfield, actual_field) 
| search matchID = "dept_LV*" 
| table costcenter, location, building_number, caption

[fotc1969]

This is almost perfectly exactly what i'm looking for, the only thing, and this is probably my fault for not elaborating, but "dept_LV*" wont always be "dept_LV" it could be "site_LA" "org_NY". i need the "search" function to use the variable rather than a manual string... for instance:

source = "source_1" OR source = "source_2"
| eval matchID=coalesce(lookupfield, actual_field)
| search matchID = lookupfield
| table costcenter, location, building_number, caption

I even tried:

source = "source_1" OR source = "source_2"
| eval matchID=coalesce(lookupfield, actual_field)
| where like (matchID, lookupfield)
| table costcenter, location, building_number, caption

but it's just not triggering on that search. Another way of asking, is is there a way for a search clause to match 2 fields with a wildcard?

[richgalloway]

The search is returning exactly what you told it to search for - "dept_LV", not "dept_LV*". Try this

source = "source_1" OR source = "source_2" | eval matchID=coalesce(lookupfield, actual_field) | where match(matchID, "dept_LV.*" | table costcenter, location, building_number, caption