Splunk Search
Highlighted

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

Builder

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.

0 Karma
Highlighted

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

Builder

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
0 Karma
Highlighted

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

Builder

@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=snowindex sourcetype=tasklookupdatcsv
| eval taskcurrentstatus=number+state+group
| eval ExistingInSummaryTable="No"
| fields number,state,taskcurrentstatus,group,ExistingInSummaryTable

| join type=left taskcurrentstatus
[
search index=snowindex2
| eval taskcurrentstatus=tnumber+tstate+tgroup
| eval ExistingInSummaryTable="Yes"
| fields t
number,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 .

0 Karma
Highlighted

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

Super Champion

so....

snowindex 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.

Highlighted

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

Engager

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.

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.