Click HERE to see how Saviynt Intelligence is transforming the industry. |
09/24/2024 04:12 AM
Dear All,
We are using the below query in analytics and sending all the information about
1. Application Name
2. Entitlements + Description
3. Entitlements Owner1, 2...
Now there is an issue where we are not getting the information from the entitlements if the customproperty5 contains only manager as approver " Manager " . and the Owner table is blank. See attached.
If customproperty5 contains " Owner1 " or " Owner2" this query listed below provides all the information.
See below the query we are using:
select ep.endpointname,ev.customproperty12 as 'RoleName', ev.description, ev.customproperty5, ev.customproperty6, ev.customproperty9, ev.customproperty8, ev.customproperty7, ev.customproperty10, ev.customproperty12, ev.customproperty13, u.username,u.email from entitlement_values ev, entitlement_owners eo, users u, entitlement_types et, endpoints ep where ev.entitlement_valuekey=eo.entitlement_valuekey and eo.userkey=u.userkey and et.entitlementtypekey=ev.entitlementtypekey and et.endpointkey=ep.endpointkey and ev.entitlementtypekey not in (1,2)
Many thanks
Kind Regards
Marco
Solved! Go to Solution.
09/24/2024 06:07 AM - edited 09/24/2024 06:08 AM
@MarcoApollo try below sample query
SELECT
ep.endpointname,
ev.customproperty12 AS 'RoleName',
ev.description,
ev.customproperty5,
ev.customproperty6,
ev.customproperty9,
ev.customproperty8,
ev.customproperty7,
ev.customproperty10,
ev.customproperty12,
ev.customproperty13,
u.username,
u.email
FROM
entitlement_values ev
LEFT JOIN
entitlement_owners eo ON ev.entitlement_valuekey = eo.entitlement_valuekey
LEFT JOIN
users u ON eo.userkey = u.userkey
JOIN
entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
JOIN
endpoints ep ON et.endpointkey = ep.endpointkey
WHERE
ev.entitlementtypekey NOT IN (1, 2)
AND (ev.customproperty5 = 'Manager' OR ev.customproperty5 IN ('Owner1', 'Owner2'));
09/24/2024 06:11 AM
SELECT ep.endpointname,
ev.customproperty12 AS 'RoleName',
ev.description,
ev.customproperty5,
ev.customproperty6,
ev.customproperty9,
ev.customproperty8,
ev.customproperty7,
ev.customproperty10,
ev.customproperty12,
ev.customproperty13,
u.username,
u.email
FROM entitlement_values ev
LEFT JOIN entitlement_owners eo
ON ev.entitlement_valuekey = eo.entitlement_valuekey
LEFT JOIN users u
ON eo.userkey = u.userkey
JOIN entitlement_types et
ON et.entitlementtypekey = ev.entitlementtypekey
JOIN endpoints ep
ON et.endpointkey = ep.endpointkey
WHERE ev.entitlementtypekey NOT IN ( 1, 2 );
09/25/2024 01:56 AM
Thank you all,
Both worked properly, we are getting all the information, now.
Kind Regards,
MArco