Identifying and Deactivating Entitlements Linked to a Specific Security System

[Roua]

Hello,

I need to identify and deactivate entitlements that are linked to a specific security system. Our setup includes two security systems:

Oracle Unified Directory (OUD)
Oracle Unified Directory2

and as known we can't delete entitlements in saviynt, we faced issues with the Oracle Unified Directory (OUD) so i deactivated all the entitlements related using 

UPDATE entitlement_values
SET ENTITLEMENT_VALUE = CONCAT(ENTITLEMENT_VALUE, '_DUPLICATE')
WHERE STATUS = 'Inactive'
  AND ENTITLEMENT_VALUE LIKE '%ou=groups,o=EXAMPLE%';

to identify them, it worked and later on i created anew connection to maintain a clean mapping,
Oracle Unified Directory2. now that this one worked. i see that the connection Oracle Unified Directory (OUD) mapped again some entitlements that are not marked as "duplicate".
i want to make sure all the entitlement under Oracle Unified Directory(OUD) are marked as duplicate but now i can't use the following to identify them 

AND ENTITLEMENT_VALUE LIKE '%ou=groups,o=EXAMPLE%';

and i am not sure how to link the table of endpoints with the entitlement_values table since i didn't find a commun column. i am scared to try anything and deactivate all entitlements AD and the already working Oracle Unified Directory2

can you please give me ideas on how to specify this query ? 

[rushikeshvartak · Accepted solution]

Use below from enhanced query ( please update required endpoint/ Ent name)

SELECT ev.entitlement_valuekey                    AS entitlement_values__primarykey
       ,
       Concat(ev.entitlement_value, '_DUPLICATE') AS
       entitlement_values__Entitlement_Value
FROM   entitlement_values ev,
       entitlement_types et,
       endpoints e
WHERE  ev.status = 2
       AND ev.entitlement_value LIKE '%ou=groups,o=EXAMPLE%'
       AND ev.entitlementtypekey = et.entitlementtypekey
       AND et.endpointkey = e.endpointkey
       AND e.endpointname = 'Oracle Unified Directory2' 

[Roua]

Hello thank you for your answer it worked by testing it in the data analyzer but when i try it in the JOB ENHANCED QUERY it wouldn't work i tried yours and also i adjusted it to match all my points like the following:

SELECT ev.entitlement_valuekey AS entitlement_values__primarykey,
       Concat(ev.entitlement_value, '_DUPLICATE') AS entitlement_values_Entitlement_Value
FROM   entitlement_values ev
JOIN   entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
JOIN   endpoints e ON et.endpointkey = e.endpointkey
WHERE  ev.status = 1
AND    ev.entitlement_value LIKE '%o=xxx'
AND    ev.entitlement_value NOT LIKE '%_DUPLICATE%'
AND    e.endpointname = 'Oracle Unified Directory (OUD)'

UNION

SELECT ev.entitlement_valuekey AS entitlement_values__primarykey,
       ev.entitlement_value AS entitlement_values_Entitlement_Value
FROM   entitlement_values ev
JOIN   entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
JOIN   endpoints e ON et.endpointkey = e.endpointkey
WHERE  ev.status = 2
AND    ev.entitlement_value LIKE '%o=xxx'
AND    ev.entitlement_value NOT LIKE '%_DUPLICATE%'
AND    e.endpointname = 'Oracle Unified Directory (OUD)'

UNION

SELECT ev.entitlement_valuekey AS entitlement_values__primarykey,
       ev.entitlement_value AS entitlement_values_Entitlement_Value
FROM   entitlement_values ev
JOIN   entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
JOIN   endpoints e ON et.endpointkey = e.endpointkey
WHERE  ev.status = 1
AND    ev.entitlement_value NOT LIKE '%o=xxx'
AND    ev.entitlement_value NOT LIKE '%_DUPLICATE%'
AND    e.endpointname = 'Oracle Unified Directory (OUD)';

as i said it worked in the data analyzer but for the JOB QUERY it didn't with the following error: 

I don't get how it is not in format if it is correct and valid in the data analyzer 

[rushikeshvartak]

Please run individual query (UNION will not work)

SELECT ev.entitlement_valuekey AS entitlement_values__primarykey,
Concat(ev.entitlement_value, '_DUPLICATE') AS entitlement_values__Entitlement_Value
FROM entitlement_values ev
JOIN entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
JOIN endpoints e ON et.endpointkey = e.endpointkey
WHERE ev.status = 1
AND ev.entitlement_value LIKE '%o=xxx'
AND ev.entitlement_value NOT LIKE '%_DUPLICATE%'
AND e.endpointname = 'Oracle Unified Directory (OUD)'

SELECT ev.entitlement_valuekey AS entitlement_values__primarykey,
ev.entitlement_value AS entitlement_values__Entitlement_Value
FROM entitlement_values ev
JOIN entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
JOIN endpoints e ON et.endpointkey = e.endpointkey
WHERE ev.status = 2
AND ev.entitlement_value LIKE '%o=xxx'
AND ev.entitlement_value NOT LIKE '%_DUPLICATE%'
AND e.endpointname = 'Oracle Unified Directory (OUD)'

SELECT ev.entitlement_valuekey AS entitlement_values__primarykey,
ev.entitlement_value AS entitlement_values__Entitlement_Value
FROM entitlement_values ev
JOIN entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
JOIN endpoints e ON et.endpointkey = e.endpointkey
WHERE ev.status = 1
AND ev.entitlement_value NOT LIKE '%o=xxx'
AND ev.entitlement_value NOT LIKE '%_DUPLICATE%'
AND e.endpointname = 'Oracle Unified Directory (OUD)';

[rushikeshvartak]

if you don't want to run separate use case condition

SELECT ev.entitlement_valuekey AS entitlement_values__primarykey,
       CASE
           WHEN ev.status = 1 AND ev.entitlement_value LIKE '%o=xxx' AND ev.entitlement_value NOT LIKE '%_DUPLICATE%' THEN Concat(ev.entitlement_value, '_DUPLICATE')
           ELSE ev.entitlement_value
       END AS entitlement_values__Entitlement_Value
FROM entitlement_values ev
JOIN entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
JOIN endpoints e ON et.endpointkey = e.endpointkey
WHERE (ev.status = 1 AND ev.entitlement_value LIKE '%o=xxx' AND ev.entitlement_value NOT LIKE '%_DUPLICATE%'
       OR ev.status = 2 AND ev.entitlement_value LIKE '%o=xxx' AND ev.entitlement_value NOT LIKE '%_DUPLICATE%'
       OR ev.status = 1 AND ev.entitlement_value NOT LIKE '%o=xxx' AND ev.entitlement_value NOT LIKE '%_DUPLICATE%')
  AND e.endpointname = 'Oracle Unified Directory (OUD)';

[Roua · Accepted solution]

Thank you for your help!

i complicated it on myself, i just used the simple query : 

SELECT ev.entitlement_valuekey AS entitlement_values__primarykey,

Concat(ev.entitlement_value, '_NOTUSED') AS entitlement_values__Entitlement_Value

FROM entitlement_values ev

JOIN entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey

JOIN endpoints e ON et.endpointkey = e.endpointkey

WHERE  (ev.status = 1 OR ev.status = 2)

AND    (ev.entitlement_value LIKE '%o=xxx' OR ev.entitlement_value NOT LIKE '%o=xxx')

AND    ev.entitlement_value NOT LIKE '%_DUPLICATE%'

AND    e.endpointname = 'Oracle Unified Directory (OUD)';

and it worked! 
I also deactivated them using: 

SELECT ev.entitlement_valuekey AS entitlement_values__primarykey,  
 2 AS entitlement_values__status
FROM   entitlement_values ev
JOIN   entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
JOIN   endpoints e ON et.endpointkey = e.endpointkey
WHERE  e.endpointname = 'Oracle Unified Directory (OUD)';

again Thank you so much!