Pattern matching in UMP Group Filters not as per documentation

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

https://docops.ca.com/ca-unified-infrastructure-management/8-4/en/using-ump/the-unified-service-manager-portlet/create-and-manage-groups-in-usm

describes the use of escape characters when defining the filter for a USM group. These do not work when the back end DB is Oracle

Cause

For the following example filter 
User Tag 2 contains [_]cpm[_]

the corresponding sql used is 
 

SELECT DISTINCT 
'1' AS element_type, 
cm_computer_system.cs_id AS member_id, 
cm_computer_system.cs_key AS instance_id 
, cm_computer_system_attr.cs_attr_key AS attr_key 
, cm_computer_system_attr.cs_attr_value AS attr_value 
FROM cm_computer_system_attr 
JOIN cm_computer_system ON cm_computer_system_attr.cs_id = cm_computer_system.cs_id 
JOIN cm_computer_system_origin ON cm_computer_system_origin.cs_id = cm_computer_system.cs_id 
WHERE cm_computer_system.cs_id IN 
( SELECT DISTINCT cm_computer_system_attr.cs_id 
FROM cm_computer_system 
JOIN cm_computer_system_attr ON cm_computer_system_attr.cs_id = cm_computer_system.cs_id 
LEFT JOIN cm_device d ON (cm_computer_system.cs_id = d.cs_id AND d.probe_name = 'controller') 
LEFT JOIN cm_nimbus_robot ON d.dev_id = cm_nimbus_robot.dev_id 
WHERE ( 
( cm_nimbus_robot.user_tag_2 LIKE '%[_]cpm[_]%' ) 
) 
) 
ORDER BY member_id

Which works fine in SQL but in Oracle returns no rows.
Oracle does not have a default escape character. Instead this needs to be defined in the query. for example:
 

SELECT DISTINCT 
'1' AS element_type, 
cm_computer_system.cs_id AS member_id, 
cm_computer_system.cs_key AS instance_id 
, cm_computer_system_attr.cs_attr_key AS attr_key 
, cm_computer_system_attr.cs_attr_value AS attr_value 
FROM cm_computer_system_attr 
JOIN cm_computer_system ON cm_computer_system_attr.cs_id = cm_computer_system.cs_id 
JOIN cm_computer_system_origin ON cm_computer_system_origin.cs_id = cm_computer_system.cs_id 
WHERE cm_computer_system.cs_id IN 
( SELECT DISTINCT cm_computer_system_attr.cs_id 
FROM cm_computer_system 
JOIN cm_computer_system_attr ON cm_computer_system_attr.cs_id = cm_computer_system.cs_id 
LEFT JOIN cm_device d ON (cm_computer_system.cs_id = d.cs_id AND d.probe_name = 'controller') 
LEFT JOIN cm_nimbus_robot ON d.dev_id = cm_nimbus_robot.dev_id 
WHERE ( 
( cm_nimbus_robot.user_tag_2 LIKE '%[_cpm[_%' ESCAPE '[') 
) 
) 
ORDER BY member_id

There is no way to define this in USM and thus the filter fails
 

Environment

UIM 8.51
Oracle DB

Resolution

ump_usm_8.5.1_HF5.zip