Fetch all entitlements assigned to a user account for a particular endpoint

[AS5278]

Hi all,

We have a requirement to display all the existing(already assigned) entitlements for a user account for that particular endpoint in a dropdown in the ARS.

I tried framing SQL queries by taking help from existing posts in forums but was unable to achieve it.

Please guide if this is achievable.

Thanks,

Atul Singh

[pmahalle · Accepted solution]

Hi @AS5278 ,

Use below query:

select ev.entitlement_value as ID from accounts a, user_accounts ua, entitlement_values ev, account_entitlements1 ae, entitlement_types et, endpoints e where a.accountkey = ua.accountkey and ua.accountkey = ae.accountkey and ae.entitlement_valuekey = ev.entitlement_valuekey and ev.entitlementtypekey=et.entitlementtypekey and et.endpointkey=e.endpointkey and e.endpointname='<Provide endpoint name here>' and ua.userkey=${requestee}

[rushikeshvartak · Accepted solution]

SELECT EP.displayname                       AS APPLICATION,
       ACC.NAME                             AS ACCOUNTNAME,
       EV.entitlement_value                 AS ENTITLEMENTNAME,
       U.username                           AS USERID,
       Concat(U.firstname, ' ', U.lastname) AS 'User Full Name',
       U.email                              AS USEREMAIL,
       CASE
         WHEN U.statuskey IS NULL THEN 'Not Available'
         WHEN U.statuskey = 0 THEN 'Inactive'
         WHEN U.statuskey = 1 THEN 'Active'
         ELSE U.statuskey
       END                                  'USER STATUS VALUE',
       U.owner                              AS USERMANGER,
       CASE
         WHEN ACC.status IS NULL THEN ''
         WHEN ACC.status = 1 THEN 'Active'
         WHEN ACC.status = 2 THEN 'In Active'
         ELSE ACC.status
       END                                  'ACCOUNT STATUS'
FROM   accounts ACC
       JOIN endpoints EP
         ON ACC.endpointkey = EP.endpointkey
            AND EP.displayname IN ( 'APPNAME' )
       LEFT JOIN account_entitlements1 AE1
              ON ACC.accountkey = AE1.accountkey
       JOIN entitlement_values EV
         ON AE1.entitlement_valuekey = EV.entitlement_valuekey
       JOIN entitlement_types ET
         ON EV.entitlementtypekey = ET.entitlementtypekey
       LEFT JOIN user_accounts UA
              ON UA.accountkey = ACC.accountkey
       LEFT JOIN users U
              ON U.userkey = UA.userkey