SQL query to get Entitlements Hierarchy

[FranciscoS]

Dear community,

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

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.

[pmahalle · Accepted solution]

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>'

[FranciscoS]

Hi Paddy,

It worked! Many thanks!

With your query, we get:

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:

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

Thanks in advance!

Kind regards