Article Number
000029481
Applies To
Product(s): Archer
Version(s): All Versions
Primary Deployment: On Premises/AWS Hosted/AWS SaaS
Description
This article explains how to clear/modify all Email Addresses for a particular LDAP Group using SQL scripts to prevent Emails from being sent.
Resolution
Check the below SQL scripts:
Kindly note that necessary measures such as, but not limited to, taking a database backup and testing the script in a non-prod environment first should be done before executing any SQL queries and/or applying any changes in the Database.
1. The below script updates the Email Addresses of users (coming through a certain LDAP configuration) to a certain value; in the below script/case, it changes the Email Addresses to 'abc@xyz.com'.
Make sure to edit the email and ldap_config_id values in the below script for each specific use case.
Note: You can find the ldap_config_id value for each LDAP configuration in the Manage LDAP Configurations page as follows:
UPDATE tblXUsersContactTypes SET xuserconttype_value = 'abc@xyz.com' FROM dbo.tbluser AS u JOIN dbo.tblXUsersContactTypes AS e ON u.user_id = e.user_id WHERE e.cont_type_id=7 and u.ldap_config_id=1
2. The below script updates the Email Address for users which are part of a certain Group (specified in group_name) where it changes the @ to @_
UPDATE tblXUsersContactTypes SET xuserconttype_value = replace(xuserconttype_value,'@','@_') FROM tblXUsersContactTypes e JOIN dbo.tbluser AS u JOIN tblLDAPConfig L on U.ldap_config_id=L.config_id JOIN tblXGroupsUsers GU on GU.user_id=U.user_id JOIN tblGroup G on G.group_id=GU.group_id ON e.user_id = u.user_id WHERE group_name = 'Archer Support Team' and e.cont_type_id=7
