Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.
No ratings
SB
Saviynt Employee
Saviynt Employee

Use Case

Query for a consolidated report to find all entitlements part of an organization and/or application roles.

Pre-requisites

NA

Applicable Version(s)

ALL

Solution

Below query can be used to identify all entitlements part of an organization in case of request rule-based apps and all entitlements part of application roles in case of endpoint level role-based apps.

select distinct * from
 (
(
select distinct
               IFNULL(c.CUSTOMPROPERTY11, 'N/A') AS 'APPLICATION NAME'
               , c.CUSTOMERNAME AS 'Name of App Role OR Organization'
     , 'Organization' AS 'App Role OR Organization'
     , IFNULL((CASE
                                WHEN c.STATUS = 1 THEN 'Active'
                                WHEN c.STATUS = 2 THEN 'Inactive'
                 END),
                 'N/A') AS 'Rule Status'
     , IFNULL(ev.ENTITLEMENT_VALUE, 'N/A') AS 'ENTITLEMENT NAME'
     , IFNULL(ep.DISPLAYNAME, 'N/A') AS 'APPLICATION NAME OF THE ENTITLEMENT'
     , IFNULL(ev.DISPLAYNAME,'N/A') AS 'ENTITLEMENT DISPLAY NAME'
     , IFNULL(et.ENTITLEMENTNAME, 'N/A') AS 'ENTITLEMENT TYPE'
     , IFNULL((CASE
                                WHEN LOWER(ev.CUSTOMPROPERTY1) = 'true' THEN 'Yes'
                                WHEN LOWER(ev.CUSTOMPROPERTY1) = 'false' THEN 'No'
                 END),
                 'N/A') AS 'REQUESTABLE'
     ,         IFNULL((CASE
                                WHEN ev.risk = 0 THEN 'None'
                                WHEN ev.risk = 1 THEN 'Very Low'
                                WHEN ev.risk = 2 THEN 'Low'
                                WHEN ev.risk = 3 THEN 'Medium'
                                WHEN ev.risk = 4 THEN 'High'
                                WHEN ev.risk = 5 THEN 'Very High'
                 END),
                 'None') AS 'Risk'
               , IFNULL((CASE
                                WHEN ev.STATUS = 1 THEN 'Active'
                                WHEN ev.STATUS = 2 THEN 'Inactive'
                                WHEN ev.STATUS = 0 THEN 'Inactive'
                 END),
                 'N/A') AS 'ENTITLEMENT STATUS'
      from
                 customer c
     left join customer_entitlementvalues cev on cev.CUSTOMERKEY=c.CUSTOMERKEY
     left join entitlement_values ev on ev.ENTITLEMENT_VALUEKEY = cev.ENTITLEMENT_VALUES
     left join entitlement_types et on et.ENTITLEMENTTYPEKEY = ev.ENTITLEMENTTYPEKEY
     left join endpoints ep on ep.ENDPOINTKEY = et.ENDPOINTKEY
group by 2,5,6             
order by 2,5
)
UNION
(
select distinct
                IFNULL(rep.DISPLAYNAME, 'N/A') AS 'APPLICATION NAME'
               , r.ROLE_NAME AS 'Name of App Role OR Organization'
     , 'App Role' AS 'App Role OR Organization'
     , IFNULL((CASE
                                WHEN r.STATUS = 1 THEN 'Active'
                                WHEN r.STATUS = 0 THEN 'Inactive'
                 END),
                 'N/A') AS 'Rule Status'
     , IFNULL(ev.ENTITLEMENT_VALUE, 'N/A') AS 'ENTITLEMENT NAME'
     , IFNULL(ep.DISPLAYNAME, 'N/A') AS 'APPLICATION NAME OF THE ENTITLEMENT'
     , IFNULL(ev.DISPLAYNAME, 'N/A') AS 'ENTITLEMENT DISPLAY NAME'
     , IFNULL(et.ENTITLEMENTNAME, 'N/A') AS 'ENTITLEMENT TYPE'
     , IFNULL((CASE
                                WHEN LOWER(ev.CUSTOMPROPERTY1) = 'true' THEN 'Yes'
                                WHEN LOWER(ev.CUSTOMPROPERTY1) = 'false' THEN 'No'
                 END),
                 'N/A') AS 'REQUESTABLE'
     ,         IFNULL((CASE
                                WHEN ev.risk = 0 THEN 'None'
                                WHEN ev.risk = 1 THEN 'Very Low'
                                WHEN ev.risk = 2 THEN 'Low'
                                WHEN ev.risk = 3 THEN 'Medium'
                                WHEN ev.risk = 4 THEN 'High'
                                WHEN ev.risk = 5 THEN 'Very High'
                 END),
                 'None') AS 'RISK'
               , IFNULL((CASE
                                 WHEN ev.STATUS = 1 THEN 'Active'
                                WHEN ev.STATUS = 2 THEN 'Inactive'
                                WHEN ev.STATUS = 0 THEN 'Inactive'
                 END),
                 'N/A') AS 'ENTITLEMENT STATUS'
      from
                roles r
     left join role_entitlements rev on rev.ROLEKEY=r.ROLEKEY
     left join entitlement_values ev on ev.ENTITLEMENT_VALUEKEY = rev.ENTITLEMENT_VALUEKEY
     left join entitlement_types et on et.ENTITLEMENTTYPEKEY = ev.ENTITLEMENTTYPEKEY
     left join endpoints ep on ep.ENDPOINTKEY = et.ENDPOINTKEY
     left join endpoints rep on rep.ENDPOINTKEY=r.ENDPOINTKEY
group by 2,5,6             
order by 2,5
))x

 

NOTE: Organizations are not directly tagged with the endpoints. Therefore above query also enables you to identify which applications use the organization based on the entitlement endpoint returned in the output.



 

Version history
Last update:
‎09/06/2023 02:52 PM
Updated by:
Saviynt Employee
Contributors