Splunk Search

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


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

Super Champion


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.


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


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


@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"
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 .

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...