Pattern matching in UMP Group Filters not as per documentation

Document ID : KB000112531
Last Modified Date : 30/08/2018
Show Technical Document Details
Issue:
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
Environment:
UIM 8.51
Oracle DB
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
 
Resolution:
There is no permanent solution at this time.  The issue is currently being researched.  Additional information will be provided as it becomes available.