Splunk Search

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

mlevsh
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

cmerriman
Super Champion

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

jpolvino
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

mlevsh
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=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 Community Platform Survey

Hey Splunk Community, Starting today, the community platform may prompt you to participate in a survey. The ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

Avoid Certificate Expiry Issues in Splunk Enterprise with Certificate Assist

This blog post is part 2 of 4 of a series on Splunk Assist. Click the links below to see the other ...