How to use join on fields with wildcards (hyphen "-", ampersand " &" doesn't work)

[mlevsh]

For one of our project , we are running the join on fields that contain hyphen or ampersand and it doesn't work.
Let's say we join two sets of queries on field that set to "Service - Tools" or field="Service & Tools" with "-" or "&" - that field is not found in sub-search even though it is there.

We suspect it is because of hyphen "-" and ampersand "&" being inside of the value.
Will appreciate any advice.

[cmerriman]

so....

snow_index has taskcurrentstatus: "TASK0001OpenService - Tools"
snow_index_2 has taskcurrentstatus: "TASK0001OpenService & Tools"

You can't just join on taskcurrentstatus and expect it to match those two together, because they don't match.

you'd need to do something like:
|replace "* - *" with "* & *" in group before you eval taskcurrentstatus and a similar one in the subsearch with t_group

but really, you shouldn't use join, it's ugly and has limitations.
try something like this:

(index=snow_index sourcetype=task_lookup_dat_csv) OR (index=snow_index_2)
| eval ExistingInSummaryTable=if(index=snow_index,"No","Yes")
| eval taskcurrentstatus=coalesce(number+state+group,t_number+t_state+t_group)
| stats values(t_number) as t_number values(t_state) as t_state values(t_group) as t_group values(ExistingInSummaryTable) as ExistingInSummaryTable by taskcurrentstatus| search ExistingInSummaryTable="No"

you might need to adjust that a bit, break it apart command by command, but you can do that search without a join, guarantee it.

[pc-ran]

Would this also work when trying to join values that have an ampersand? I am trying to join two tables based on four different categories but out of the three, the fourth category (XXXXX&) won't join its match.

[jpolvino]

What does your search look like? This is a simple test that shows the values you posted do match:

| makeresults 
| eval field1a="Service - Tools" | eval field1b="Service - Tools"
| eval field2a="Service & Tools" | eval field2b="Service & Tools"
| eval match1=if(field1a==field1b,"Match","Mismatch")
| eval match2=if(field2a==field2b,"Match","Mismatch")
| table field1a field1b match1 field2a field2b match2

[mlevsh]

@jpolvino , we are using join of two queries on the field taskcurrentstatus, that combined from number, state and group name.
For example:
taskcurrentstatus: "TASK0001OpenService - Tools"
OR
taskcurrentstatus: "TASK0001OpenService & Tools"

index=snow_index sourcetype=task_lookup_dat_csv
| eval taskcurrentstatus=number+state+group
| eval ExistingInSummaryTable="No"
| fields number,state,taskcurrentstatus,group,ExistingInSummaryTable

| join type=left taskcurrentstatus
[
search index=snow_index_2
| eval taskcurrentstatus=t_number+t_state+t_group
| eval ExistingInSummaryTable="Yes"
| fields t_number,t_state,t_group,taskcurrentstatus,ExistingInSummaryTable
]
|table _time t_number t_state t_group number state group taskcurrentstatus ExistingInSummaryTable
| search ExistingInSummaryTable="No"

So, when "-" or "&" is part of group name, taskcurrentstatus is not found
in the inner join .