000029481 - How to modify email addresses for an LDAP Group with a SQL script in RSA Archer

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support on Jun 28, 2019
Version 3Show Document
  • View in full screen mode

Article Content

Article Number000029481
Applies ToRSA Product Set: Archer
RSA Version/Condition: 5.5.x, 6.x
IssueThis article explains how to clear all email addresses for a particular LDAP group to avoid emails going out. 
TasksA script is required to do this.
ResolutionThis is the script. The email and ldap_config need to be edited for the specific use case.

UPDATE tblXUsersContactTypes SET xuserconttype_value = 'sajhflkhsf@ksajhflkhsf.com'
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

This script narrows to a group and changes the @ to @_ for all records that match criteria.

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 Corpsys Support Team' and e.cont_type_id=7