We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

SQL query to get Entitlements Hierarchy

FranciscoS
New Contributor III
New Contributor III

Dear community,

We are trying to fetch Profile and Role to create an Analytics report.

FranciscoS_0-1697455949735.png

This can be found in every Account > Entitlement Hierarchy tab.

However, we are not able to fetch this value from DataAnalyzer through SQL query. We do not find these columns in accounts table...

Could you help us, please? Do you know how to retrieve these values for the accounts that I have already selected:

SELECT U.USERNAME AS 'USERNAME', U.LASTNAME AS 'USER LAST NAME', U.FIRSTNAME AS 'USER FIRST NAME', U.TERMDATE AS 'USER TERMINATION DATE', U.CUSTOMPROPERTY30 AS 'GOELAND BRAND', U.DEPARTMENTNUMBER AS 'DEPARTMENT NUMBER', U.EMPLOYEEID AS 'EMPLOYEE ID', U.EMPLOYEETYPE AS 'EMPLOYEE TYPE', U.JOBDESCRIPTION AS 'JOB DESCRIPTION', A.NAME AS 'Goeland User Account', A.ACCOUNTID AS 'Goeland User Account ID' FROM ACCOUNTS A, USER_ACCOUNTS UA, USERS U, ENDPOINTS E, SECURITYSYSTEMS S WHERE A.ACCOUNTKEY = UA.ACCOUNTKEY AND U.USERKEY = UA.USERKEY AND A.ENDPOINTKEY = E.ENDPOINTKEY AND E.SECURITYSYSTEMKEY = S.SYSTEMKEY AND U.STATUSKEY = 1 AND E.STATUS = 1 AND A.STATUS IN (1 , 'MANUALLY PROVISIONED') AND U.USERNAME NOT IN ('ADMIN' , 'AWSADMIN') AND E.ENDPOINTKEY=25 ORDER BY U.USERNAME , S.SYSTEMNAME , E.ENDPOINTNAME;

Kind regards,

Francisco J.

 

 

 

2 REPLIES 2

pmahalle
All-Star
All-Star

Hi @FranciscoS ,

User below query to get entitlement of type Profile and Role for specific user. Provide the required values.

SELECT
U.USERNAME,
A.NAME,
A.ACCOUNTID,
E.ENTITLEMENT_VALUE,
ET.ENTITLEMENTNAME
FROM
ENTITLEMENT_VALUES E,
ENTITLEMENT_TYPES ET,
ACCOUNT_ENTITLEMENTS1 AE,
ACCOUNTS A,
USER_ACCOUNTS UA,
USERS U,
ENDPOINTS EP
WHERE
ET.ENTITLEMENTTYPEKEY = E.ENTITLEMENTTYPEKEY AND
E.ENTITLEMENT_VALUEKEY = AE.ENTITLEMENT_VALUEKEY AND
AE.ACCOUNTKEY = A.ACCOUNTKEY AND
A.ACCOUNTKEY = UA.ACCOUNTKEY AND
UA.USERKEY = U.USERKEY AND
A.ENDPOINTKEY = EP.ENDPOINTKEY AND
ET.ENTITLEMENTNAME in ('Profile', 'Role') AND
EP.ENDPOINTNAME='<Provide endpoint name here>' AND
U.USERNAME='<Provide username here>'


Pandharinath Mahalle(Paddy)
If this reply answered your question, please Accept As Solution to help other who may have a same problem. Give Kudos 🙂

FranciscoS
New Contributor III
New Contributor III

Hi Paddy,

It worked! Many thanks!

With your query, we get:

FranciscoS_0-1697463003119.png

However, we want a little modification of that query since we would like to have 1 row per account and colums Role and Profile with their values:

FranciscoS_1-1697463016992.png

Do you know how to adapt this query to get this result?

Thanks in advance!

Kind regards