Compliance Accelerator Random Sampling fails with Event ID 272 referencing 'Item has already been added'

Problem

Enterprise Vault (EV) Compliance Accelerator (CA) Random Sampling fails when more than one Monitored Employee has the same email address listed. The failure is listed in the EV Event Logs as below. Here are some examples of how this condition could arise:

- A new user takes over a user's email address after the original user has been deactivated in Active Directory. The original user remains active in CA until 30 synchronisation attempts in up to 30 days (default settings) have elapsed.
- A user is deactivated in Active Directory and CA after leaving the company. The same user then returns to the company and uses the same email address(es).
- A user is deactivated in Active Directory and CA after leaving the company. Another user with the same name joins the company and is assigned the same email address(es).
- A user is deactivated in Active Directory and CA after leaving the company. The user's email address(es) are manually added to another Monitored Employee to maintain Department Tagging for any messages sent to or received by the user's email address(es).
- Users are migrated from one domain to another. Each domain's users are contained in groups within their respective domains. These groups are listed as nested groups in one group in either domain, which is then added to CA as an Employee Group.

Error Message

Cause

A CA Monitored Employee can be set to synchronize individually with an Active Directory account or can be created via membership in a CA Employee Group that is synchronized with an Active Directory mail-enabled group, such as a Distribution List. When a Monitored Employee that is synchronized with AD either individually or as part of an AD Group has their Active Directory account is deleted, the synchronization process for that Monitored Employee will fail for 30 attempts within 30 days, by default, before the Monitored Employee is automatically deactivated in CA. However, the CA deactivation process leaves the email address(es) for the Monitored Employee in the CA Customer database, but tags it/them as deactivated so that it/they should not be used during any Department ID Tagging, Random Sampling or Search processing. Having duplicate email addresses, with at least one being assigned to an active Monitored Employee can cause Random Sampling to fail with the error above.

Workaround

The workaround is to remove the duplicate email addresses. To identify such addresses:

1. Log onto the SQL Server hosting the CA Customer database using an account with at least permission to read the database contents, such as the Vault Service Account (VSA), launch SQL Server Management Studio, and open a New Query window focused on the CA Customer.
2. List any duplicate email addresses by running the following SQL query in the Query window:

SELECT [Sampling Status] = 'Last First Pass:  ' + [Value] FROM tblConfig WHERE ([Key]) LIKE '%First Pass%'
UNION SELECT 'Last Sample:  ' + [Value] FROM tblConfig WHERE ([Key]) LIKE '%Last Sample%'
UNION SELECT 'Sample Status:  ' + [Value] FROM tblConfig WHERE ([Key]) LIKE '%Status%';
;WITH DupAddr AS (
SELECT Address
FROM tblAddress
GROUP BY Address
HAVING COUNT(Address) > 1),
DupAddrHist AS (
SELECT Address
FROM tblAddressHistory
GROUP BY Address
HAVING COUNT(Address) > 1)
SELECT ta.AddressID, ta.AddressTypeID, ta.Address, ta.AddressOwnerID, tau.DisplayName, tau.FirstName, tau.Surname, tau.Deactivated
FROM tblAddress AS ta
JOIN DupAddr ON DupAddr.Address = ta.Address
JOIN tblAddressUser AS tau ON ta.AddressOwnerID = tau.AddressOwnerID
UNION
SELECT tah.AddressID, tah.AddressTypeID, tah.Address, tah.AddressOwnerID, tau.DisplayName, tau.FirstName, tau.Surname, tau.Deactivated
FROM tblAddressHistory AS tah
JOIN DupAddrHist ON DupAddrHist.Address = tah.Address
JOIN tblAddressUser AS tau ON tah.AddressOwnerID = tau.AddressOwnerID
ORDER BY DisplayName, Address;

3. Open the CA Client with an account that has permissions to modify Monitored Employees (such as the VSA), click on the Employees tab, review the listed Employees and make any changes as needed to verify all email addresses are unique.
4. Restart all Storage Services on the EV Storage servers in order to force a re-synchronization of the Monitored Employee information with the Tagging process.
5. In some cases, the failure can cause Random Sampling to not run at the next scheduled time. If the 'Sample Status' value from the above query results does not show "Finished", please contact technical support for assistance.

Solution

This issue has been addressed in the following release(s):

Enterprise Vault 12.3.2
https://www.veritas.com/support/en_US/article.100043310