AD accout name in user's custom attributes

[nitishdas]

Hello,

Need help in updating the Active directory's account name into any of the custom property values of the user if possible.

[sudeshjaiswal]

Hello @nitishdas,

You can either use sav4sav connector to update user table or during account import map the account name into some user customproperty in the user_attribute json.

Thanks.

[rushikeshvartak]

Below are possible options 

• Use custom query - Not proposed & best practice
• Use Sav4sav REST Connector to update users customproperty from accounts

[Mervinr]

Hi Team,

Thanks for the reply, Currently we are usingSAV4SAV DB connector, in that we having some update query in updateuserjson as below:

Current Query

{"updateUserQry":["update users set customproperty1 = '${user.firstname}' ,updatedate = utc_timestamp(), customproperty64 = CASE WHEN comments like '%User Terminated By%' THEN 'Locked' ELSE ' ' END where userkey = ${user.id}"]}

And now are trying to add one more user's customproperty along with update query like below:

New Query

{"updateUserQry":["update users set customproperty1 = '${user.firstname}' ,updatedate = utc_timestamp(), customproperty64 = CASE WHEN comments like '%User Terminated By%' THEN 'Locked' ELSE ' ' END where userkey = ${user.id}",CUSTOMPROPERTY40 = (SELECT a.NAME FROM ACCOUNTS a INNER JOIN USER_ACCOUNTS ua ON ua.ACCOUNTKEY=a.ACCOUNTKEY INNER JOIN ENDPOINTS e ON e.endpointkey=a.endpointkey WHERE ua.USERKEY=USERKEY AND e.endpointname='Active Directory' and a.status = 1)]}

can you please confirm above condition look fine or not.

[rushikeshvartak]

Query looks correct , I think you will trigger update user task from User update rule , if yes it will work

[Mervinr]

Hi Team,

We tried the following option:

Updating the SAV4SAV DB connection using the query in updateaccount JSONand userupdatejson.

UPDATEACCOUNTJSON 

{"updateUserQry":["update users set CUSTOMPROPERTY42 = (SELECT a.NAME FROM ACCOUNTS a INNER JOIN USER_ACCOUNTS ua ON ua.ACCOUNTKEY=a.ACCOUNTKEY INNER JOIN ENDPOINTS e ON e.endpointkey=a.endpointkey WHERE ua.USERKEY=USERKEY AND e.endpointname='Active Directory' and a.status = 1)"]}

UPDATEUSERJSON

{"updateUserQry":["update users set customproperty1 = '${user.firstname}' ,updatedate = utc_timestamp(), customproperty64 = CASE WHEN comments like '%User Terminated By%' THEN 'Locked' ELSE ' ' END where userkey = ${user.id}, customproperty41 = (SELECT a.NAME FROM ACCOUNTS a INNER JOIN USER_ACCOUNTS ua ON ua.ACCOUNTKEY=a.ACCOUNTKEY INNER JOIN ENDPOINTS e ON e.endpointkey=a.endpointkey WHERE ua.USERKEY=USERKEY AND e.endpointname='Active Directory' and a.status = 1)"]}

Updating the AD connection user_attribute.

USER_ATTRIBUTE

[CUSTOMPROPERTY41::sAMAccountName#String]

We are not seeing the updated value in the user custom property value after there is a change in the account name.

If we are going with the analytic option, we need to schedule a job, and it should run on a daily basis. The client feels we are not burdening the instance.

Thanks

Mervin

[sudeshjaiswal]

Hello @Mervinr & @nitishdas,

The query appears fine.
However, there are two things to keep in mind:
Firstly, the updateuserjson function is triggered when you initiate an updateuser task or if want to perform an update through importing, then you should use the userimport json.

Thanks.

[Mervinr]

Hi Team,

Thanks for prompt response, we are making changes in the AD (Samaccount name) and in next recon cycle this updated samaccount name come to account id into saviynt.

So now in this case we need to use accountupdate json in SAV4SAV DB connector, so that whenever there is change in account name it will trigger the query to make update to respective user table that we are mentioning in the query.

So can you please confirm we can with using accountupdate json instead of userupdate json in sav4sav DB connector.

Thanks

Mervin

[rushikeshvartak]

Then you need to generate task from analytics with action as 'Update Account'

[Mervinr]

Hi Team,

We tried the following option:

Updating the SAV4SAV DB connection using the query in updateaccount JSONand userupdatejson.

UPDATEACCOUNTJSON

{"updateUserQry":["update users set CUSTOMPROPERTY42 = (SELECT a.NAME FROM ACCOUNTS a INNER JOIN USER_ACCOUNTS ua ON ua.ACCOUNTKEY=a.ACCOUNTKEY INNER JOIN ENDPOINTS e ON e.endpointkey=a.endpointkey WHERE ua.USERKEY=USERKEY AND e.endpointname='Active Directory' and a.status = 1)"]}

UPDATEUSERJSON

{"updateUserQry":["update users set customproperty1 = '${user.firstname}' ,updatedate = utc_timestamp(), customproperty64 = CASE WHEN comments like '%User Terminated By%' THEN 'Locked' ELSE ' ' END where userkey = ${user.id}, customproperty41 = (SELECT a.NAME FROM ACCOUNTS a INNER JOIN USER_ACCOUNTS ua ON ua.ACCOUNTKEY=a.ACCOUNTKEY INNER JOIN ENDPOINTS e ON e.endpointkey=a.endpointkey WHERE ua.USERKEY=USERKEY AND e.endpointname='Active Directory' and a.status = 1)"]}

Updating the AD connection user_attribute.

USER_ATTRIBUTE

 [CUSTOMPROPERTY41::sAMAccountName#String]

We are not seeing the updated value in the user custom property value after there is a change in the account name.

If we are going with the analytic option, we need to schedule a job, and it should run on a daily basis. The client feels we are not burdening the instance.

Thanks

Mervin

[rushikeshvartak]

You need to schedule the analytics job. limit analytics query such that where account name & users cp4 is not matching so records will be less and it wont be burden