Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

Need to get all the Entitlement/Role Owners

MarcoApollo
New Contributor III
New Contributor III

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

 

3 REPLIES 3

Raghu
All-Star
All-Star

@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'));


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

rushikeshvartak
All-Star
All-Star

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


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

MarcoApollo
New Contributor III
New Contributor III

Thank you all,

Both worked properly, we are getting all the information, now.

Kind Regards,

MArco