Fetching attributes from multiple endpoints

[shivmano]

Hi Team,

I am currently using below query to fetch the entitlements from application A into user's cp61. Now in addition to this, I also need to fetch the account name from another application B into cp1 on users table. Please can someone help me modify this query to achieve this. 

Please note the requirement is user having application A only with entitlements, that has to be fetched to cp61, users having application B only, that has to be fetched to cp1, for users having both application A and B, both cp61 and cp1 should be populated accordingly 

select distinct u.username as username,GROUP_CONCAT(distinct ev.displayname SEPARATOR '|') as customproperty61 from account_entitlements1 ae, accounts a, endpoints e, entitlement_values ev, user_accounts ua, users u where ae.entitlement_valuekey=ev.entitlement_valuekey and ae.accountkey=a.accountkey and a.endpointkey=e.endpointkey and e.endpointname='A' and ua.accountkey=a.accountkey and ua.userkey=u.userkey and ev.status='1' and ev.entitlementtypekey='123' group by u.USERNAME order by a.name

[pmahalle]

Hi @shivmano ,

Use below query, which will take to update user's CP1 with application B's account name. It will take care to update only those users, whose CP1 is not updated with account name or not matching.

SELECT U.USERNAME AS USERNAME, A.NAME AS CUSTOMPROPERTY1 FROM USERS U, ACCOUNTS A, USER_ACCOUNTS UA, ENDPOINTS E WHERE U.USERKEY=UA.USERKEY AND UA.ACCOUNTKEY = A.ACCOUNTKEY AND A.ENDPOINTKEY=E.ENDPOINTKEY AND E.ENDPOINTNAME LIKE '%application B%' AND A.STATUS IN ('1','2','Manually Provisioned') AND (U.CUSTOMPROPERTY1 IS NULL OR U.CUSTOMPROPERTY1 != A.NAME)

Hope it helps.

[shivmano]

@pmahalle , thank you for the response. But I am trying to get application B's accountname along with entitlement display name for application A in a single query

[DixshantValecha · Accepted solution]

Hi @shivmano,

Use below query,and let me know if this works for you:-

SELECT DISTINCT
u.username,
GROUP_CONCAT(DISTINCT CASE WHEN e.endpointname = 'A' THEN ev.displayname END SEPARATOR '|') AS customproperty61,
GROUP_CONCAT(DISTINCT CASE WHEN e.endpointname = 'B' THEN a.name END SEPARATOR '|') AS customproperty1
FROM user_accounts ua
LEFT JOIN accounts a ON ua.accountkey = a.accountkey
LEFT JOIN endpoints e ON a.endpointkey = e.endpointkey
LEFT JOIN account_entitlements1 ae ON a.accountkey = ae.accountkey
LEFT JOIN entitlement_values ev ON ae.entitlement_valuekey = ev.entitlement_valuekey
LEFT JOIN users u ON ua.userkey = u.userkey
WHERE (e.endpointname = 'A' OR e.endpointname = 'B')
AND (ev.status = '1' OR ev.status IS NULL)
AND (ev.entitlementtypekey = '123' OR ev.entitlementtypekey IS NULL)
GROUP BY u.username
ORDER BY a.name;

[shivmano]

@DixshantValecha , thank you for sharing the query. It works