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

Fetching attributes from multiple endpoints

shivmano
Regular Contributor III
Regular Contributor III

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

4 REPLIES 4

pmahalle
All-Star
All-Star

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.


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

shivmano
Regular Contributor III
Regular Contributor III

@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
Saviynt Employee
Saviynt Employee

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
Regular Contributor III
Regular Contributor III

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