Legal Hold Scripts

Article:TECH72381  |  Created: 2009-01-15  |  Updated: 2014-04-07  |  Article URL http://www.symantec.com/docs/TECH72381
Article Type
Technical Solution

Product(s)

Environment

Issue



Legal Hold Scripts


Solution



The SQL Scripts below will report and validate if any DA databases have added holds to items in the Vault Stores.


SCRIPT 1:  Determines which DA Customer databases are placing Legal Holds on Vault Store databases

Instructions:
1. Using SQL Query Analyzer, copy the text below into a New Query window and execute the script against the appropriate Vault Store database.


DECLARE @CustomerDB nvarchar(100)
DECLARE @HUGID nvarchar(100)
DECLARE @FullLength int
DECLARE @TrimmedLength int

CREATE TABLE #DBTable (
DatabaseName nvarchar(60))


DECLARE TBLCursor CURSOR FOR
 SELECT HoldUserGroupID FROM HoldGroup
OPEN TBLCursor
FETCH NEXT FROM TBLCursor INTO @HUGID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FullLength = (SELECT LEN(@HUGID))
SET @TrimmedLength = (@FullLength - 40)

SET @CustomerDB = SUBSTRING(@HUGID, 4, @TrimmedLength)
INSERT INTO #DBTable (DatabaseName) VALUES (@CustomerDB)


FETCH NEXT FROM TBLCursor INTO @HUGID
END

SELECT DISTINCT DatabaseName AS 'DA Databases with holds' FROM #DBTable

CLOSE TBLCursor
DEALLOCATE TBLCursor
DROP TABLE #DBTable






SCRIPT 2:   This is designed to supply BY CASE the amount of legal holds the DA customer database reports as 'on-hold' PER VAULT STORE

Important Notes:  If crossing multiple SQL servers, interconnect the Sql servers by running the following command: EXECUTE sp_addlinkedserver SQLServerName
   If using an Instance, enclose the Sql server name \ instance with brackets.   Example:  [SQLServerName\Instance1].EVVaultStore.dbo.tablename

- This script is setup to allow up to 5 Vault Stores.  Additional vault stores can be added by:
1. Adding another VSCountX in the temporary table
2. Increase the DECLARE variables:  @VSCountX , @VSCountTotX
3. SET the new variables to 0
4. Add another SET section in the Vault Store section of the code
5. Add the @VScountTotX to the @VSCount SET line (after the Vault Store section)
6. Add the @VSCountTotX in the SET section after the Vault Store section
7. Add the new variable in the INSERT and SELECT statements at the end of the script

Instructions:
1. Run against the DA Customer databases

2. Replace SQLServerName.EVVaultStoreX with the appropriate Sql server name and vault Store.  
   There are 10 entries, two per vault store.
   (example: EVVAultStore1, EVVAultStore2.... EVVAultStore5)

3. This can be run with less then 5 vault stores without removing entries


CREATE TABLE #DAGroup (
DAName nvarchar(250),
DACount int,
DALHCount int,
VSCount int,
VSCount1 int,
VSCount2 int,
VSCount3 int,
VSCount4 int,
VSCount5 int,
NotOnHold int,
DAStatusWord nvarchar(20),
DALHGID nvarchar(100),
DACaseID int)

DECLARE @DAName nvarchar(250)
DECLARE @DALHCount int
DECLARE @DACount int
DECLARE @DAStatusID int
DECLARE @DAStatusWord nvarchar(20)
DECLARE @DALHGID nvarchar(100)
DECLARE @DACaseID int
DECLARE @VSCount int
DECLARE @NotOnHold int
DECLARE @VSCount1 int
DECLARE @VSCount2 int
DECLARE @VSCount3 int
DECLARE @VSCount4 int
DECLARE @VSCount5 int
DECLARE @VSCountTot int
DECLARE @VSCount1Tot int
DECLARE @VSCount2Tot int
DECLARE @VSCount3Tot int
DECLARE @VSCount4Tot int
DECLARE @VSCount5Tot int

SET @VSCount = 0
SET @VSCountTot = 0
SET @VSCount1Tot = 0
SET @VSCount2Tot = 0
SET @VSCount3Tot = 0
SET @VSCount4Tot = 0
SET @VSCount5Tot = 0

DECLARE DAScan CURSOR FOR
SELECT [Name], StatusID, LegalHoldGroupID, CaseID
FROM tblCase
WHERE LegalHoldState = 400
OPEN DAScan
FETCH NEXT FROM DAScan INTO
@DAName, @DAStatusID, @DALHGID, @DACaseID
WHILE @@FETCH_STATUS=0

BEGIN
SET @DAStatusWord = 'Other'
IF @DAStatusID = 20
SET @DAStatusWord = 'Open'
IF @DAStatusID = 21
SET @DAStatusWord = 'Closed'
IF @DAStatusID = 23
SET @DAStatusWord = 'Exception'
IF @DAStatusID = 24
SET @DAStatusWord = 'Deleted'

SET @DALHCount =
(SELECT COUNT(*) FROM tblIntDiscoveredItems
WHERE CaseID = @DACaseID AND LegalStatus = 426)

SET @DACount =
(SELECT COUNT(*) FROM tblIntDiscoveredItems
WHERE CaseID = @DACaseID)

SET @NotOnHold = @DACount - @DALHCount

-- This is the section where the individual Vault Stores need to be entered
-- Change the SQLserverName.VaultStoreName with the correct information

SET @VSCount1 =
(SELECT COUNT(*) FROM (SELECT DISTINCT SavesetIdentity, HoldGroupIdentity FROM SQLServerName.EVVaultStore1.dbo.HoldSaveset
WHERE HoldGroupIdentity =
(SELECT HoldGroupIdentity FROM SQLServerName.EVVaultStore1.dbo.HoldGroup
WHERE HoldUserGroupID = @DALHGID)) AS new_table)

SET @VSCount2 =
(SELECT COUNT(*) FROM (SELECT DISTINCT SavesetIdentity, HoldGroupIdentity FROM SQLServerName.EVVaultStore2.dbo.HoldSaveset
WHERE HoldGroupIdentity =
(SELECT HoldGroupIdentity FROM SQLServerName.EVVaultStore2.dbo.HoldGroup
WHERE HoldUserGroupID = @DALHGID)) AS new_table)

SET @VSCount3 =
(SELECT COUNT(*) FROM (SELECT DISTINCT SavesetIdentity, HoldGroupIdentity FROM SQLServerName.EVVaultStore3.dbo.HoldSaveset
WHERE HoldGroupIdentity =
(SELECT HoldGroupIdentity FROM SQLServerName.EVVaultStore3.dbo.HoldGroup
WHERE HoldUserGroupID = @DALHGID)) AS new_table)

SET @VSCount4 =
(SELECT COUNT(*) FROM (SELECT DISTINCT SavesetIdentity, HoldGroupIdentity FROM SQLServerName.EVVaultStore4.dbo.HoldSaveset
WHERE HoldGroupIdentity =
(SELECT HoldGroupIdentity FROM SQLServerName.EVVaultStore4.dbo.HoldGroup
WHERE HoldUserGroupID = @DALHGID)) AS new_table)

SET @VSCount5 =
(SELECT COUNT(*) FROM (SELECT DISTINCT SavesetIdentity, HoldGroupIdentity FROM SQLServerName.EVVaultStore5.dbo.HoldSaveset
WHERE HoldGroupIdentity =
(SELECT HoldGroupIdentity FROM SQLServerName.EVVaultStore5.dbo.HoldGroup
WHERE HoldUserGroupID = @DALHGID)) AS new_table)

--  This is the end of the Vault Store information


SET @VSCount = @VSCount1 + @VSCount2 + @VSCount3 + @VSCount4 + @VSCount5
SET @VSCountTot = @VSCountTot + @VSCount
SET @VSCount1Tot = @VSCount1Tot + @VSCount1
SET @VSCount2Tot = @VSCount2Tot + @VSCount2
SET @VSCount3Tot = @VSCount3Tot + @VSCount3
SET @VSCount4Tot = @VSCount4Tot + @VSCount4
SET @VSCount5Tot = @VSCount5Tot + @VSCount5

INSERT INTO #DAGroup
(DAName, DACount, DALHCount, VSCount, VSCount1, VSCount2, VSCount3, VSCount4, VSCount5, NotOnHold, DAStatusWord, DALHGID, DACaseID) VALUES
(@DAName, @DACount, @DALHCount, @VSCount, @VSCount1, @VSCount2, @VSCount3, @VSCount4, @VSCount5, @NotOnHold, @DAStatusWord, @DALHGID, @DACaseID)

FETCH NEXT FROM DAScan INTO
@DAName, @DAStatusID, @DALHGID, @DACaseID
END

SELECT DAName AS 'Case Name',
DAStatusWord AS 'Case Status',
DACount AS 'Total Count',
DALHCount AS 'Legal Hold Count',
VSCount AS 'Vault Store Total Count',
VSCount1 AS 'EVVaultStore1 Count',   -- Change the Vault Store name
VSCount2 AS 'EVVaultStore2 Count',   -- Change the Vault Store name
VSCount3 AS 'EVVaultStore3 Count',   -- Change the Vault Store name
VSCount4 AS 'EVVaultStore4 Count',   -- Change the Vault Store name
VSCount5 AS 'EVVaultStore5 Count',   -- Change the Vault Store name
NotOnHold AS 'Items Not on Hold',
DALHGID AS 'LH GroupID',
DACaseID AS 'CaseID'
FROM #DAGroup
ORDER BY DAName

SELECT
@VSCountTot AS 'Total Vault Store Count',
@VSCount1Tot AS 'Total EVVaultStore1 Count',   -- Change the Vault Store name
@VSCount2Tot AS 'Total EVVaultStore2 Count',   -- Change the Vault Store name
@VSCount3Tot AS 'Total EVVaultStore3 Count',   -- Change the Vault Store name
@VSCount4Tot AS 'Total EVVaultStore4 Count',   -- Change the Vault Store name
@VSCount5Tot AS 'Total EVVaultStore5 Count'   -- Change the Vault Store name



CLOSE DAScan
DEALLOCATE DAScan
DROP TABLE #DAGroup






SCRIPT 3:   This is designed to supply BY VAULT STORE the amount of legal holds the Vault Store database reports as 'on-hold' PER DA CASE

Important Notes:  If crossing multiple SQL servers, interconnect the Sql servers by running the following command: EXECUTE sp_addlinkedserver SQLServerName
   If using an Instance, enclose the Sql server name \ instance with brackets.   Example:  [SQLServerName\Instance1].EVVaultStore.dbo.tablename

Instructions:
1. Run against the Vault Store databases

2. Replace the DA customer database name twice and
the SQL Server Name once, mid way through the script


CREATE TABLE #VSGroup (
CaseName nvarchar(100),
HUGID nvarchar(250),
HGID int,
CaseCount int)

DECLARE @HUGID nvarchar(250)
DECLARE @HGID int
DECLARE @CaseCount int
DECLARE @FullLength int
DECLARE @TrimmedLength int
DECLARE @CustomerDB nvarchar(60)

INSERT INTO #VSGroup (HUGID, HGID)
SELECT DISTINCT HoldUserGroupID, HoldGroupIdentity
FROM HoldGroup


DECLARE VSScan CURSOR FOR
SELECT HGID, HUGID FROM #VSGroup

OPEN VSScan
FETCH NEXT FROM VSScan INTO
@HGID, @HUGID
WHILE @@FETCH_STATUS=0
BEGIN

SET @FullLength = (SELECT LEN(@HUGID))
SET @TrimmedLength = (@FullLength - 40)

SET @CustomerDB = SUBSTRING(@HUGID, 4, @TrimmedLength)

/* ***********************************
**  Change the EVBADiscovery twice
**  -include the proper SQL server name
**   as well on second entry)
**
** **********************************/

IF @CustomerDB = 'EVBADiscovery' -- replace database name
BEGIN
-- Obtain the case Name
UPDATE #VSGroup SET CaseName =
(SELECT [Name] FROM SQLServerName.EVBADiscovery.dbo.tblCase  -- replace SQL server name and DA database name
WHERE LegalHoldGroupID = @HUGID)
WHERE HGID = @HGID

-- Obtain the Vault Store count for this case
SET @CaseCount =
(SELECT COUNT(*) FROM
(SELECT DISTINCT SavesetIdentity, HoldGroupIdentity FROM HoldSaveset
WHERE HoldGroupIdentity = @HGID) As new_table)
UPDATE #VSGroup SET CaseCount = @CaseCount WHERE HGID = @HGID
END

FETCH NEXT FROM VSScan INTO
@HGID, @HUGID
END


SELECT
CaseName AS 'Case Name',
CaseCount AS 'Legal Holds on this Vault Store',
HUGID AS 'Hold User Group ID'
FROM #VSGroup WHERE CaseCount > 0
COMPUTE SUM(CaseCount)


CLOSE VSScan
DEALLOCATE VSScan
DROP TABLE #VSGroup






SCRIPT 4:  Determines the summary count of items that are currently on Legal Hold within the Vault Store by Archive ID

Important Notes: If crossing multiple SQL servers, interconnect the Sql servers by running the following command: EXECUTE sp_addlinkedserver SQLServerName
  If using an Instance, enclose the Sql server name \ instance with brackets.   Example:  [SQLServerName\Instance1].EVVaultStore.dbo.tablename

Instructions:
1. Replace 'ABCDEF1234567890ABCDEF1234567890ABC10000EVSite'  with the appropriate ArchiveID number.

2. If the EnterpriseVaultDirectory is on a different SQL server, Add the 'SQLServerName.' in front of EnterpriseVaultDirectory (Three times within the script).
       Example: SQLServerABC.EnterpriseVaultDirectory.dbo.ArchiveView

3. Using SQL Query Analyzer, copy the text below into a New Query window and execute the script against the Vault Store database that contains the Archive in question.

4. Verify the correct Vault Store DB is being queried through the output of the query.



DECLARE @Temp1 nvarchar(127) SET @Temp1 = 'ABCDEF1234567890ABCDEF1234567890ABC10000EVSite'

DECLARE @ArchiveName nvarchar(256)
SET @ArchiveName =
(SELECT ArchiveName FROM EnterpriseVaultDirectory.dbo.ArchiveView WHERE VaultEntryId = @Temp1)

DECLARE @VaultName nvarchar(50)
SET @VaultName =
(SELECT DatabaseDSN FROM EnterpriseVaultDirectory.dbo.VaultStoreEntry WHERE VaultStoreEntryID =
(SELECT VaultStoreEntryID FROM EnterpriseVaultDirectory.dbo.ArchiveView WHERE VaultEntryId = @Temp1))

DECLARE @Summary3 int
SET @Summary3 =
(SELECT COUNT(*) FROM (SELECT DISTINCT SavesetIdentity, HoldGroupIdentity
FROM HoldSaveset WHERE SavesetIdentity IN
(SELECT SavesetIdentity FROM Saveset WHERE ArchivePointIdentity =
(SELECT ArchivePointIdentity FROM ArchivePoint WHERE ArchivePointID = @Temp1))) AS new_table)


SELECT @VaultName AS 'VaultStore DB', @ArchiveName AS 'Archive Name', @Summary3 AS 'Number of Legal Holds'






SCRIPT 5:  Determines which DA Customer databases have a Legal Hold on a single item by SavesetID

Instructions:
1.  Replace the SavesetID in Line 1 (use the single quotes).

2.  Execute the script against the appropriate Vault Store.

 
DECLARE @SavesetID varchar(69) SET @SavesetID = '000000000000000~200801010123450000~1~2E640DFA55C04422B817170A85DC61F'
 
DECLARE @OriginalTransactionID varchar(32)
DECLARE @strTemp varchar(36)
DECLARE @strTemp2 varchar(36)
DECLARE @NewTransactionID varchar(36)
  IF LEN(@SavesetID) = 68
BEGIN
 SET @OriginalTransactionID = right(@SavesetID, 31)
 SET @strTemp2 = left(@OriginalTransactionID, 8) + '-'
 SET @strTemp = right(@OriginalTransactionID,(len(@OriginalTransactionID) - 8)) SET @strTemp2 = @strTemp2 + left(@strTemp, 4) + '-'
 SET @strTemp = right(@OriginalTransactionID,(len(@OriginalTransactionID) - 12)) SET @strTemp2 = @strTemp2 + left(@strTemp, 4) + '-'
 SET @strTemp = right(@OriginalTransactionID,(len(@OriginalTransactionID) - 16)) SET @strTemp2 = @strTemp2 + left(@strTemp, 4) + '-'
 SET @strTemp = right(@OriginalTransactionID,(len(@OriginalTransactionID) - 20)) SET @strTemp2 = @strTemp2 + left(@strTemp, 11) + '0'
 SET @NewTransactionID = @strTemp2
END   IF LEN(@SavesetID) = 69
BEGIN
 SET @OriginalTransactionID = right(@SavesetID, 32)
 SET @strTemp2 = left(@OriginalTransactionID, 8) + '-'
 SET @strTemp = right(@OriginalTransactionID,(len(@OriginalTransactionID) - 8)) SET @strTemp2 = @strTemp2 + left(@strTemp, 4) + '-'
 SET @strTemp = right(@OriginalTransactionID,(len(@OriginalTransactionID) - 12)) SET @strTemp2 = @strTemp2 + left(@strTemp, 4) + '-'
 SET @strTemp = right(@OriginalTransactionID,(len(@OriginalTransactionID) - 16)) SET @strTemp2 = @strTemp2 + left(@strTemp, 4) + '-'
 SET @strTemp = right(@OriginalTransactionID,(len(@OriginalTransactionID) - 20)) SET @strTemp2 = @strTemp2 + left(@strTemp, 12) 
 SET @NewTransactionID = @strTemp2
END   DECLARE @TransID uniqueidentifier SET @TransID = @NewTransactionID
DECLARE @CustomerDB nvarchar(100)
DECLARE @FullLength int
DECLARE @TrimmedLength int
 
CREATE TABLE #HGtmp (
HUGIdentity nvarchar(100))
  DECLARE TBLCursor CURSOR FOR
SELECT HoldGroupIdentity, SavesetIdentity FROM HoldSaveset
OPEN TBLCursor
DECLARE @HGIdentity int, @SSID int
FETCH NEXT FROM TBLCursor INTO @HGIdentity, @SSID
WHILE @@FETCH_STATUS = 0
BEGIN
  IF @SSID =
(SELECT SavesetIdentity FROM Saveset
WHERE idTransaction = @transID)
BEGIN
DECLARE TBLCursor1 CURSOR FOR
SELECT HoldGroupIdentity, HoldUserGroupID FROM HoldGroup
OPEN TBLCursor1
DECLARE @HGIdentity1 int, @HUGIdentity nvarchar(100)
FETCH NEXT FROM TBLCursor1 INTO @HGIdentity1, @HUGIdentity
WHILE @@FETCH_STATUS = 0
BEGIN
  IF @HGIdentity = @HGIdentity1
BEGIN
SET @FullLength = (SELECT LEN(@HUGIdentity))
SET @TrimmedLength = (@FullLength - 40)
SET @CustomerDB = SUBSTRING(@HUGIdentity, 4, @TrimmedLength)
INSERT INTO #HGtmp (HUGIdentity) VALUES (@CustomerDB)
END
FETCH NEXT FROM TBLCursor1 INTO @HGIdentity1, @HUGIdentity
END
 
CLOSE TBLCursor1
DEALLOCATE TBLCursor1
END
 
FETCH NEXT FROM TBLCursor INTO @HGIdentity, @SSID
END
 
SELECT DISTINCT HUGIdentity AS 'DA db with Legal Holds' FROM #HGtmp   CLOSE TBLCursor
DEALLOCATE TBLCursor
DROP TABLE #HGtmp
 

SCRIPT 6:  Determines which DA Customer databases have a Legal Hold on a single item by TransactionID

Instructions:
1.  Replace the TransactionID in Line 1 (use the single quotes).

2.  Execute the script against the appropriate Vault Store.


DECLARE @TransID uniqueidentifier SET @TransID = '12f45e78-a234-1b3c-de34-123456789abc'
DECLARE @CustomerDB nvarchar(100)
DECLARE @FullLength int
DECLARE @TrimmedLength int
CREATE TABLE #HGtmp (
HUGIdentity nvarchar(100))

DECLARE TBLCursor CURSOR FOR
SELECT HoldGroupIdentity, SavesetIdentity FROM HoldSaveset
OPEN TBLCursor

DECLARE @HGIdentity int, @SSID int
FETCH NEXT FROM TBLCursor INTO @HGIdentity, @SSID
WHILE @@FETCH_STATUS = 0
BEGIN

IF @SSID =
(SELECT SavesetIdentity FROM Saveset
WHERE idTransaction = @transID)
BEGIN

DECLARE TBLCursor1 CURSOR FOR
SELECT HoldGroupIdentity, HoldUserGroupID FROM HoldGroup
OPEN TBLCursor1
DECLARE @HGIdentity1 int, @HUGIdentity nvarchar(100)

FETCH NEXT FROM TBLCursor1 INTO @HGIdentity1, @HUGIdentity
WHILE @@FETCH_STATUS = 0
BEGIN

IF @HGIdentity = @HGIdentity1
BEGIN
SET @FullLength = (SELECT LEN(@HUGIdentity))
SET @TrimmedLength = (@FullLength - 40)
SET @CustomerDB = SUBSTRING(@HUGIdentity, 4, @TrimmedLength)
INSERT INTO #HGtmp (HUGIdentity) VALUES (@CustomerDB)
END

FETCH NEXT FROM TBLCursor1 INTO @HGIdentity1, @HUGIdentity
END
CLOSE TBLCursor1
DEALLOCATE TBLCursor1
END
FETCH NEXT FROM TBLCursor INTO @HGIdentity, @SSID
END
SELECT DISTINCT HUGIdentity AS 'DA db with Legal Holds' FROM #HGtmp

CLOSE TBLCursor
DEALLOCATE TBLCursor
DROP TABLE #HGtmp

 

 
SCRIPT 7:  Provides a listing of all Legal Hold error codes and causes as listed in Technote TECH61297 (see Related Articles below).

Instructions:
1.  Open a new Query window with the focus on the appropriate DA Customer Database.

2.  Set the SQL Output to file under Query | Results To | Results To File.

3.  Execute the script against the DA Customer Database. Provide a location and filename for the file as prompted.

4.  Open the file in any text editor and review the results.

 

 

SET NOCOUNT ON
---Configure any frequently called solutions
DECLARE @CommonSolutions nvarchar(max)
SET @CommonSolutions = 'Common solutions to Legal Hold errors:
1. Check the Enterprise Vault, Discovery Accelerator and SQL Servers for TCP Chimney and / or TCP Offload Engine.
Disable if found to be enabled. Refer to Technote http://www.symantec.com/docs/TECH55653.
2. Go to the Configuration Tab | Settings | Legal Hold section:
   - Edit the Placing Hold Items Batch Size setting to half of the current value,
   - Edit the Removing Hold Items Batch Size setting to half of the current value,
   - Save the changes and Restart the Enterprise Vault Accelerator Manager Service (EVAMS) to implement the changes.
3. Run IISRESET from a Command Prompt on the DA Server to clear the IIS cache.
4. Restart the EV Directory and Storage Services on the EV Servers servicing the items being placed on Legal Hold.
5. Reapply Legal Holds on just the failed items per the instructions in Technote http://www.symantec.com/docs/TECH66365.
If the Legal Hold errors continue after following the above steps, please contact Symantec Enterprise Vault Technical Support for further assistance.'

---Determine if there are any Legal Hold errors and loop through the errors and provide basic troubleshooting steps
DECLARE @LHEList int
SET @LHEList = (SELECT COUNT(DiscoveredItemID) AS 'Number of Items with Legal Hold errors'
FROM tblIntDiscoveredItems tidi WITH (NOLOCK)
WHERE LegalStatus IN (425, 427))
---If no Legal Hold errors exist, end the check
IF @LHEList = 0 BEGIN
PRINT 'No Legal Hold errors found.'
GOTO FINISHED
END
---If Legal Hold errors exist, begin providing more details
IF @LHEList > 0
BEGIN

---Provide a listing of current Cases, Searches and item counts with Legal Hold errors
SELECT DISTINCT tc.CaseID, tc.name 'Case Name'
    , tc.LegalHoldState
    , ts1.name 'Legal Hold State'
    , tc.LegalHoldStatus, ts2.Name 'Legal Hold Status'
    , tc.LegalHoldGroupID
    , tis.SearchID, tis.Name 'Search Name', tis.RunDate 'Search Run Date'
    , tidi.LegalStatus 'Item LegalStatus', ts3.name 'Item Legal Hold Status'
    , RTRIM(LTRIM(SUBSTRING(tidi.LegalError, CHARINDEX('Error Code:', tidi.LegalError) + 11, 100))) AS 'Legal Error Code'
    , COUNT(tidi.DiscoveredItemID) 'Item Count'
FROM tblIntDiscoveredItems tidi
JOIN tblCase tc ON tidi.CaseID = tc.CaseID
JOIN tblStatus ts1 ON tc.LegalHoldState = ts1.StatusID
JOIN tblStatus ts2 ON tc.LegalHoldStatus = ts2.StatusID
JOIN tblIntSearches tis ON tidi.SearchID = tis.SearchID
JOIN tblStatus ts3 ON tidi.LegalStatus = ts3.StatusID
WHERE tidi.LegalStatus IN (425, 427)
GROUP BY tc.CaseID, tc.name, tc.LegalHoldState ,ts1.name, tc.LegalHoldStatus, ts2.Name, tc.LegalHoldGroupID, tis.SearchID, tis.Name, tis.RunDate, tidi.LegalStatus, ts3.name, RTRIM(LTRIM(SUBSTRING(tidi.LegalError, CHARINDEX('Error Code:', tidi.LegalError) + 11, 100)))
ORDER BY tc.CaseID, tis.SearchID, tidi.LegalStatus

---Provide more troubleshooting information for each Legal Hold error
DECLARE @CID int
DECLARE @LE nvarchar(25)
---Create a CURSOR to loop through the different error codes
DECLARE LHE CURSOR FOR
SELECT DISTINCT CaseID, RTRIM(LTRIM(SUBSTRING(LegalError, CHARINDEX('Error Code:', LegalError) + 11, 100)))
FROM tblIntDiscoveredItems
WHERE LegalStatus IN (425, 427)
---Open the CURSOR and loop through the error codes as long as error codes are available
OPEN LHE
FETCH NEXT FROM LHE INTO @CID, @LE
WHILE @@FETCH_STATUS = 0
BEGIN

---If the error code does not match any of the known error codes, provide the Technote documenting Legal Hold Errors
---This section will need to be updated whenever codes are modified or added
IF @LE NOT IN (768, 769, 770, 771, 772, 773, 774, 775, 776, 777, 778, 779, 780
,'-2147220728', '-2147220729', '-2147220730', '-2147220732', '-2147220736')
BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'This error code may not be listed in our database.
If no information for the Legal Hold Error Code is listed above and the LegalError column shows a
truncated number similar to -214722, please refer to Technote http://www.symantec.com/docs/TECH61297
for more information on how to obtain the full error code. Then refer to the same Technote for more
information on the error code. Otherwise please contact Symantec Technical Support.'
PRINT ''
GOTO GETNEXT
END

---Information for each known error code starts here
IF @LE = 768 BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'The item was unavailable, possibly due to an RPC Service being unavailable.'
PRINT @CommonSolutions
PRINT ''
GOTO GETNEXT
END

IF @LE = 769 BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'The Archive containing the item was full.'
PRINT ''
GOTO GETNEXT
END

IF @LE = 770 BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'The item could not be accessed due to the Storage Service being busy.'
PRINT ''
GOTO GETNEXT
END

IF @LE = 771 BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'The Vault Service Account did not have access to the item due to a permission change.'
PRINT ''
GOTO GETNEXT
END

IF @LE = 772 BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'A bad parameter was passed to the Legal Hold operation that prevented the item from being placed on Legal Hold.'
PRINT @CommonSolutions
PRINT ''
GOTO GETNEXT
END

IF @LE = 773 BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'An ambiguous parameter was passed to the Legal Hold operation that prevented the item from being placed on Legal Hold.'
PRINT @CommonSolutions
PRINT ''
GOTO GETNEXT
END

IF @LE = 774 BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'An internal failure occurred.'
PRINT @CommonSolutions
PRINT ''
GOTO GETNEXT
END

IF @LE = 775 BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'The item was been deleted before it could be placed on hold and no longer exists in the Vault Store Database.'
PRINT ''
GOTO GETNEXT
END

IF @LE = 776 BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'The Archive, Vault Store or Retention Category under which the item was Archived does not exist.'
PRINT ''
GOTO GETNEXT
END

IF @LE = 777 BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'The Legal Hold for the item has been barred from removal.'
PRINT ''
GOTO GETNEXT
END

IF @LE = 778 BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'The license to allow Legal Hold placement does not exist.
Contact your Symantec Sales representative to obtain the applicable license required to place items on Legal Hold.
Please refer to the Discovery Accelerator Installation Guide for the proper steps to install the license.'
PRINT ''
GOTO GETNEXT
END

IF @LE = 779 BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'The license to allow Legal Hold placement has expired.
Contact your Symantec Sales representative to obtain the applicable license required to place items on Legal Hold.
Please refer to the Discovery Accelerator Installation Guide for the proper steps to install the license.'
PRINT ''
GOTO GETNEXT
END

IF @LE = 780 BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'An invalid device has been referenced. Contact Symantec Technical Support for further assistance.'
PRINT ''
GOTO GETNEXT
END

IF @LE = '-2147220728' BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'The Archive or Vault Store in which the item resides or the Retention Category under which the item was Archived no longer exists in the EnterpriseVaultDirectory database.'
PRINT ''
GOTO GETNEXT
END

IF @LE = '-2147220729' BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'The item was been deleted before it could be placed on hold and no longer exists in the Vault Store Database.'
PRINT ''
GOTO GETNEXT
END

IF @LE = '-2147220730' BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'There are communications issues between the Enterprise Vault Server for the associated Vault Store and the Discovery Accelerator Server.
If the AcceleratorService DTrace states:
     No such interface supported (0x80004002)
, verify the Registry entries and EV Binaries on both Servers are the correct matched versions.
If the AcceleratorService DTrace states:
     Exception: An internal failure occurred. Internal Error: The parameter is incorrect. [0x80070057.
     Info:{C2.EN_US} An COM error occurs [adding] holds, Error Code: -2147220730 Diag:HRESULT: 80040306
, follow the instructions in Technote http://www.symantec.com/docs/TECH180872.'
PRINT ''
GOTO GETNEXT
END

IF @LE = '-2147220732' BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'There are communications issues between the StorageOnlineOpns Process on the Enterprise Vault Server(s) for the associated Vault Store(s) and the Discovery Accelerator Server.
This issue has been found to be caused by a hung StorageOnlineOpns Process that is awaiting information from some source, such as Discovery Accelerator.
The cause of the StorageOnlineOpns Process hang can be having Backtrace enabled on the EV Server for troubleshooting purposes.
See Technote http://www.symantec.com/docs/TECH66365 for steps to ensure Backtrace is fully disabled.'
PRINT ''
GOTO GETNEXT
END

IF @LE = '-2147220736' BEGIN
PRINT 'Information for Legal Hold Error Code ' + @LE + ':'
PRINT 'The Enterprise Vault Storage Service for the associated Vault Store is not found to be accessible.'
PRINT @CommonSolutions
PRINT ''
GOTO GETNEXT
END

---Get the next set of error codes
GETNEXT:
FETCH NEXT FROM LHE INTO @CID, @LE

---Stop the loop and deallocate the CURSOR if there are no more error codes
END
CLOSE LHE
DEALLOCATE LHE

---Finished with Legal Hold errors
PRINT 'For more details on Legal Hold error codes please see the following Technote:
http://www.symantec.com/docs/TECH61297'
END
---Finished with no Legal Hold errors
FINISHED:




Legacy ID



327820


Article URL http://www.symantec.com/docs/TECH72381


Terms of use for this information are found in Legal Notices