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?
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
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?
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