000029481 - script to modify emails for a ldap group

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000029481
Applies ToRSA Product Set: Archer
RSA Product/Service Type: Archer
RSA Version/Condition: 5.5 SP1
Platform: Windows
Platform (Other): null
O/S Version: Customer doesn't know/not sure-see notes
Product Name: null
Product Description: null
IssueWe are looking 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 teh @ 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