Extracting entitlements as separate columns through analytics

[soumik_das]

Hi,

I am creating an analytics where we are trying to get a user and their accounts of a certain endpoint. On top of that, we are also trying to get the entitlements of those accounts. The endpoint has two entitlement types for every account, "Profile" and "Role". We have been able to get the users and their corresponding accounts but are having trouble getting two entitlements. Can you please help us here?
The query to get the user and entitlement without entitlement type is posted below:

select u.username,u.customproperty3 ,u.departmentnumber, u.JOBDESCRIPTION, u.startdate, a.name,a.status,a.created_on as from users u, accounts a where (u.customproperty3=65 or u.customproperty3=01) and a.endpointkey=5 and u.statuskey=1 and a.accountid=u.customproperty28 and a.created_on <= date_sub(now(),INTERVAL 7 DAY)

[rushikeshvartak]

SELECT U.USERNAME,
       U.CUSTOMPROPERTY3,
       U.DEPARTMENTNUMBER,
       U.JOBDESCRIPTION,
       U.STARTDATE,
       A.NAME,
       A.STATUS,
       A.CREATED_ON,
       EV.ENTITLEMENT_VALUE
FROM   USERS U,
       ACCOUNTS A,
       ACCOUNT_ENTITLEMENTS1 AE,
       ENTITLEMENT_VALUES EV
WHERE  ( U.CUSTOMPROPERTY3 = 65
          OR U.CUSTOMPROPERTY3 = 01 )
       AND A.ENDPOINTKEY = 5
       AND U.STATUSKEY = 1
       AND A.ACCOUNTID = U.CUSTOMPROPERTY28
       AND A.CREATED_ON <= Date_sub(Now(), INTERVAL 7 DAY)
       AND AE.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY
       AND A.ACCOUNTKEY = AE.ACCOUNTKEY 

[soumik_das]

Hi @rushikeshvartak ,

Here I am able to get the results for which there is an entitlement value. Is it possible to get the entitlements which don't have any value from this query. And also can I get the entitlement type along with it?

Thanks and regards,
Soumik Das

[rushikeshvartak · Accepted solution]

SELECT U.username,
       U.customproperty3,
       U.departmentnumber,
       U.jobdescription,
       U.startdate,
       A.name,
       A.status,
       A.created_on,
       EV.entitlement_value,
       et.entitlementname AS enttype
FROM   users U,
       accounts A,
       account_entitlements1 AE,
       entitlement_values EV,
       entitlement_types et
WHERE  ( U.customproperty3 = 65
          OR U.customproperty3 = 01 )
       AND A.endpointkey = 5
       AND U.statuskey = 1
       AND A.accountid = U.customproperty28
       AND A.created_on <= Date_sub(Now(), interval 7 day)
       AND AE.entitlement_valuekey = EV.entitlement_valuekey
       AND A.accountkey = AE.accountkey
       AND et.entitlementtypekey = ev.entitlementtypekey 

[soumik_das]

Hi @rushikeshvartak ,

Thanks a lot for your solution.

Thanks and regards,
Soumik Das