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.
snowindex has taskcurrentstatus: "TASK0001OpenService - Tools"
snowindex_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.
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.
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
@jpolvino , we are using join of two queries on the field taskcurrentstatus, that combined from number, state and group name.
taskcurrentstatus: "TASK0001OpenService - Tools"
taskcurrentstatus: "TASK0001OpenService & Tools"
| eval taskcurrentstatus=number+state+group
| eval ExistingInSummaryTable="No"
| fields number,state,taskcurrentstatus,group,ExistingInSummaryTable
| join type=left taskcurrentstatus
| eval taskcurrentstatus=tnumber+tstate+tgroup
| eval ExistingInSummaryTable="Yes"
| fields tnumber,tstate,tgroup,taskcurrentstatus,ExistingInSummaryTable
|table time tnumber tstate tgroup number state group taskcurrentstatus ExistingInSummaryTable
| search ExistingInSummaryTable="No"
So, when "-" or "&" is part of group name, taskcurrentstatus is not found
in the inner join .