how to update users cp with all active endpoints provisioned to user

saurabhpad
New Contributor II
New Contributor II

hi,

We have requirement were we are updating cp 36 with users active accounts endpointname. Below is the query we have tried but it is not working and concating new value with old one. simple concat will not work as details are present in multiple rows. 

We are trying below query

update users JOIN USER_ACCOUNTS USER_ACCOUNTS ON USER_ACCOUNTS.USERKEY = USERS.USERKEY JOIN ACCOUNTS ON ACCOUNTS.ACCOUNTKEY = USER_ACCOUNTS.ACCOUNTKEY JOIN ENDPOINTS ON ACCOUNTS.ENDPOINTKEY = ENDPOINTS.ENDPOINTKEY set users.customproperty36=concat(users.customproperty36,ENDPOINTS.endpointname) where users.userkey='2825' and accounts.status in
('1','Active','Manually Provisioned')

We have also tried GROUP_CONCAT String _AGG 

 

is there aby way to populate user cpvalue with all endpoints name provisioned to user

4 REPLIES 4

adriencosson
Regular Contributor III
Regular Contributor III

Hi @saurabhpad ,

I have tried the below using SELECT query and GROUP_CONCAT operator : it displays the list of Endpoint Names based on account status, separated by a comma :

 

SELECT u.username, GROUP_CONCAT(e.endpointname SEPARATOR ',') as 'Concat Endpoints' FROM users u left join user_accounts ua on u.userkey=ua.userkey left join accounts a on ua.accountkey=a.accountkey left join endpoints e on a.endpointkey=e.endpointkey where a.status in ('Manually Provisioned',1) GROUP BY u.username

 

Hope this helps !

Regards,
Adrien COSSON

saurabhpad
New Contributor II
New Contributor II

Thanks for reply @adriencosson  Yes this query returns endpoints name but i want to update users customproperty with these endpoints name, is there any way to update users cp value with these endpoints name

pmahalle
Valued Contributor II
Valued Contributor II

Hi @saurabhpad ,

Use SAVforSAV in order to update the user's CP with endpoint list. Use above query in UserImport XML with appropriate mapping.

Use attached sample user import xml.


Regards,

Pandharinath Mahalle(Paddy)
If this reply answered your question, please Accept it As Solution to help others who may have a similar problem.

saurabhpad
New Contributor II
New Contributor II

Thanks for reply @pmahalle  can we use execute extension queries instead of using  SAVforSAV