Description
In the event there are issues with applying or removing Legal Holds in Enterprise Vault (EV) Discovery Accelerator (DA), certain information will be needed to begin investigating the issue. This information is available in the DA databases and Vault Store databases. The following queries will obtain the information - all that is needed to execute them is access to SQL Server Management Studio with an account that has SQL permissions to execute SELECT queries. These queries do not change any data and only gather specific information needed to investigate DA Legal Hold issues.
How to execute the queries
1. Use SQL Server Management Studio to connect to the Database Engine that services the DA databases and the EV Vault Store databases.
2. Execute the following queries one at a time and against the applicable databases as indicated.
3. Copy the query output to a spreadsheet for easier analysis:
3.1. Execute each query one at a time against the applicable database as indicated.
3.2. Right-click in the results of each output section.
3.3. Select All.
3.4. Right-click in the results again.
3.4. Copy With Headers.
3.6. Paste into a new spreadsheet.
3.7. Label each sheet with the DA Customer database name or Vault Store database name and the query number as indicated below.
Note: The DA databases may be on a different SQL server\instance than the EV databases.
Queries:
-- 1. DA Configuration database information, execute against the DA Configuration database:
SELECT [Script] = ''
, [Configuration Database] = ''
, CustomerID
, Name
, Version
, DirectoryServer
, Server AS 'SQL Server'
, InitialCatalog
, CustomerGUID
FROM tblCustomer
WHERE Type = 51
UNION ALL
SELECT
'1 - Customer Info'
, db_name()
, NULL, NULL, NULL, NULL, NULL, NULL, NULL
ORDER BY CustomerID;
-- 2. DA Customer database total Legal Hold counts, run against each DA Customer database:
SELECT
[Script] = ''
, [DA Customer Database] = ''
, [CustomerGUID] = (SELECT CAST([Value] AS UNIQUEIDENTIFIER) FROM tblConfig WHERE [Key] LIKE 'CustomerGUID')
, [LegalKey (HoldConsumer.HoldConsumerGUID)] = (SELECT CAST([Value] AS UNIQUEIDENTIFIER) FROM tblConfig WHERE [Key] LIKE 'LegalKey_%')
, [No Hold] = (SELECT COUNT(DiscoveredItemID) FROM tblIntDiscoveredItems (NOLOCK) WHERE LegalStatus = 420)
, [To Add Hold] = (SELECT COUNT(DiscoveredItemID) FROM tblIntDiscoveredItems (NOLOCK) WHERE LegalStatus = 421)
, [Adding Hold] = (SELECT COUNT(DiscoveredItemID) FROM tblIntDiscoveredItems (NOLOCK) WHERE LegalStatus = 422)
, [To Remove Hold] = (SELECT COUNT(DiscoveredItemID) FROM tblIntDiscoveredItems (NOLOCK) WHERE LegalStatus = 423)
, [Removing Hold] = (SELECT COUNT(DiscoveredItemID) FROM tblIntDiscoveredItems (NOLOCK) WHERE LegalStatus = 424)
, [Hold Error] = (SELECT COUNT(DiscoveredItemID) FROM tblIntDiscoveredItems (NOLOCK) WHERE LegalStatus = 425)
, [Hold] = (SELECT COUNT(DiscoveredItemID) FROM tblIntDiscoveredItems (NOLOCK) WHERE LegalStatus = 426)
, [Item Deleted] = (SELECT COUNT(DiscoveredItemID) FROM tblIntDiscoveredItems (NOLOCK) WHERE LegalStatus = 427)
UNION ALL
SELECT '2 - Total LH Count'
, db_name()
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
ORDER BY [DA Customer Database] DESC;
-- 3. DA Customer database Legal Hold counts, execute against each DA Customer database:
-- 3.1. Legal Hold counts per Case:
SELECT
[Script] = ''
, [DA Customer Database] = ''
, tc.CaseID
, [Case_Status] = CONVERT(nvarchar(25), tc.StatusID) + ' - ' + ts.Name
, [Type - Name] = CASE tc.FolderType WHEN 330 THEN 'Case - ' WHEN 331 THEN 'Research Folder - ' WHEN 332 THEN 'Hidden Research Folder - ' END + tc.Name
, tc.MarkedForDeletion
, tc.DeletedByPrincipalID
, [Legal Hold State] = CONVERT(nvarchar(25), tc.LegalHoldState) + ' - ' + ts1.Name
, [Legal Hold Status] = CONVERT(nvarchar(25), tc.LegalHoldStatus) + ' - ' + ts2.Name
, [LegalHoldGroupID (HoldGroup.HoldUserGroupID)] = tc.LegalHoldGroupID
, [CustomerGUID] = (SELECT [Value] FROM tblConfig WHERE [KEY] LIKE '%CustomerGUID%')
, [LegalKey (HoldConsumer.HoldConsumerGUID)] = (SELECT LEFT(RIGHT([Value], LEN([Value]) - 1), LEN([Value]) - 2) FROM tblConfig WHERE [Key] LIKE 'LegalKey_%')
, [Legal Hold Count] = COUNT (tidi.DiscoveredItemID)
FROM tblCase AS tc (NOLOCK)
JOIN tblStatus AS ts ON tc.StatusID = ts.StatusID
JOIN tblStatus AS ts1 ON tc.LegalHoldState = ts1.StatusID
JOIN tblStatus AS ts2 ON tc.LegalHoldStatus = ts2.StatusID
JOIN tblIntDiscoveredItems AS tidi (NOLOCK) ON tc.CaseID = tidi.CaseID AND tidi.LegalStatus = 426 /*Hold*/
WHERE (tc.LegalHoldState = 400 OR tc.LegalHoldGroupID IS NOT NULL)
GROUP BY tc.CaseID, tc.StatusID, ts.name, tc.FolderType, tc.MarkedForDeletion, tc.DeletedByPrincipalID, tc.Name, tc.LegalHoldState, ts1.Name, tc.LegalHoldStatus, ts2.Name, tc.LegalHoldGroupID
UNION
SELECT
'3.1 - LH Count per Case'
, db_name()
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
ORDER BY [DA Customer Database] DESC, [Type - Name];
-- 3.2. Legal Hold counts per Case per Vault Store:
SELECT
[Script] = ''
, [DA Customer Database] = ''
, tc.CaseID
, [Case_Status] = CONVERT(nvarchar(25), tc.StatusID) + ' - ' + ts.Name
, [Type - Name] = CASE tc.FolderType WHEN 330 THEN 'Case - ' WHEN 331 THEN 'Research Folder - ' WHEN 332 THEN 'Hidden Research Folder - ' END + tc.Name
, [Legal Hold State] = CONVERT(nvarchar(25), tc.LegalHoldState) + ' - ' + ts1.Name
, [Legal Hold Status] = CONVERT(nvarchar(25), tc.LegalHoldStatus) + ' - ' + ts2.Name
, [LegalHoldGroupID (HoldGroup.HoldUserGroupID)] = tc.LegalHoldGroupID
, [CustomerGUID] = (SELECT [Value] FROM tblConfig WHERE [KEY] LIKE '%CustomerGUID%')
, [LegalKey (HoldConsumer.HoldConsumerGUID)] = (SELECT LEFT(RIGHT([Value], LEN([Value]) - 1), LEN([Value]) - 2) FROM tblConfig WHERE [Key] LIKE 'LegalKey_%')
, [Vault Store] = tvs.Name
, [Legal Hold Count] = COUNT (tidi.DiscoveredItemID)
FROM tblCase AS tc (NOLOCK)
JOIN tblStatus AS ts ON tc.StatusID = ts.StatusID
JOIN tblStatus AS ts1 ON tc.LegalHoldState = ts1.StatusID
JOIN tblStatus AS ts2 ON tc.LegalHoldStatus = ts2.StatusID
JOIN tblIntDiscoveredItems AS tidi (NOLOCK) ON tc.CaseID = tidi.CaseID AND tidi.LegalStatus = 426 /*Hold*/
JOIN tblVaults AS tv ON tidi.VaultID = tv.VaultID AND tidi.LegalStatus = 426 /*Hold*/
JOIN tblVaultStore AS tvs ON tv.VaultStoreID = tvs.VaultStoreID
WHERE (tc.LegalHoldState = 400 OR tc.LegalHoldGroupID IS NOT NULL)
GROUP BY tc.CaseID, tc.StatusID, ts.name, tc.FolderType, tc.Name, tc.LegalHoldState, ts1.Name, tc.LegalHoldStatus, ts2.Name, tc.LegalHoldGroupID, tvs.Name
UNION ALL
SELECT
'3.2 - LH Count per Case per Vault Store'
, db_name()
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
ORDER BY [DA Customer Database] DESC, [Type - Name];
-- 3.3. Legal Hold counts per Case per Archive:
SELECT
[Script] = ''
, [DA Customer Database] = ''
, tc.CaseID
, [Case_Status] = CONVERT(nvarchar(25), tc.StatusID) + ' - ' + ts.Name
, [Type - Name] = CASE tc.FolderType WHEN 330 THEN 'Case - ' WHEN 331 THEN 'Research Folder - ' WHEN 332 THEN 'Hidden Research Folder - ' END + tc.Name
, [Legal Hold State] = CONVERT(nvarchar(25), tc.LegalHoldState) + ' - ' + ts1.Name
, [Legal Hold Status] = CONVERT(nvarchar(25), tc.LegalHoldStatus) + ' - ' + ts2.Name
, [LegalHoldGroupID (HoldGroup.HoldUserGroupID)] = tc.LegalHoldGroupID
, [CustomerGUID] = (SELECT [Value] FROM tblConfig WHERE [KEY] LIKE '%CustomerGUID%')
, [LegalKey (HoldConsumer.HoldConsumerGUID)] = (SELECT LEFT(RIGHT([Value], LEN([Value]) - 1), LEN([Value]) - 2) FROM tblConfig WHERE [Key] LIKE 'LegalKey_%')
, [Vault Store] = tvs.Name
, tv.KVSVaultName
, tv.KVSVaultEntryID
, [Legal Hold Count] = COUNT (tidi.DiscoveredItemID)
FROM tblCase AS tc (NOLOCK)
JOIN tblStatus AS ts ON tc.StatusID = ts.StatusID
JOIN tblStatus AS ts1 ON tc.LegalHoldState = ts1.StatusID
JOIN tblStatus AS ts2 ON tc.LegalHoldStatus = ts2.StatusID
JOIN tblIntDiscoveredItems AS tidi (NOLOCK) ON tc.CaseID = tidi.CaseID AND tidi.LegalStatus = 426 /*Hold*/
JOIN tblVaults AS tv ON tidi.VaultID = tv.VaultID AND tidi.LegalStatus = 426 /*Hold*/
JOIN tblVaultStore AS tvs ON tv.VaultStoreID = tvs.VaultStoreID
WHERE (tc.LegalHoldState = 400 OR tc.LegalHoldGroupID IS NOT NULL)
--AND tv.KVSVaultEntryID = '123ABC456DEF789GHI123ABC456DEF789GHI0000EVSiteAlias' -- Use this line for a specific ArchiveID
/*AND tv.KVSVaultEntryID IN ( -- Use this block for multiple ArchiveIDs
-- List ArchiveIDs in a single-quote enclosed comma-separated list below
'123ABC456DEF789GHI123ABC456DEF789GHI0000EVSiteAlias'
, '223ABC456DEF789GHI123ABC456DEF789GHI0000EVSiteAlias'
, '3123ABC456DEF789GHI123ABC456DEF789GHI0000EVSiteAlias'
-- List ArchiveIDs in a single-quote enclosed comma-separated list above
)*/
GROUP BY tc.CaseID, tc.StatusID, ts.name, tc.FolderType, tc.Name, tc.LegalHoldState, ts1.Name, tc.LegalHoldStatus, ts2.Name, tc.LegalHoldGroupID, tvs.Name, tv.KVSVaultName, tv.KVSVaultEntryID
UNION ALL
SELECT
'3.3 - LH Count per Case per Archive'
, db_name()
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
ORDER BY [DA Customer Database] DESC, tv.KVSVaultName, [Type - Name];
-- 4. Vault Store database Legal Hold information, execute against each Vault Store database:
-- 4.0. Obtain the list of all Vault Store databases and associated SQL Servers by running the following against the EnterpriseVaultDirectory database, and then run the remaining step 4 queries against each Vault Store database listed on the output:
SELECT
[Script] = ''
, [SQL Server] = SQLServer
, [Vault Store Database] = DatabaseName
FROM EnterpriseVaultDirectory..VaultStoreEntry
UNION ALL
SELECT
'4.0 - Vault Store Databases'
, NULL, NULL
ORDER BY [SQL Server], [Vault Store Database];
-- 4.1. Tables information (compare to View information):
SELECT
[Script] = ''
, [VS Database] = ''
, hc.HoldConsumerIdentity
, [HoldConsumerGUID (tbl.Config.LegalKey)] = hc.HoldConsumerGUID
, [HoldGroupIdentity] = hg.HoldGroupIdentity
, [HoldUserGroupID (tblCase.LegalHoldGroupID)] = hg.HoldUserGroupID
, [HoldSaveset item count for HoldGroupIdentity] = COUNT(hs.HoldIdentity)
FROM HoldConsumer AS hc (NOLOCK)
JOIN HoldGroup AS hg (NOLOCK) ON hc.HoldConsumerIdentity = hg.HoldConsumerIdentity
JOIN HoldSaveset AS hs (NOLOCK) ON hg.HoldGroupIdentity = hs.HoldGroupIdentity
GROUP BY hc.HoldConsumerIdentity, hc.HoldConsumerGUID, hg.HoldUserGroupID, hg.HoldGroupIdentity
UNION ALL
SELECT
'4.1 - Tables Info'
, db_name()
, NULL, NULL, NULL, NULL, NULL
ORDER BY [VS Database] DESC, hc.HoldConsumerIdentity, hg.HoldGroupIdentity;
-- 4.2. View information (compare to Tables information):
SELECT
[Script] = ''
, [VS Database] = ''
, HoldConsumerGUID, HoldConsumerIdentity, HoldUserGroupID, HoldGroupIdentity, HoldDataIdentity
, [Items_on_hold] = COUNT(SavesetIdentity)
FROM view_Holds (NOLOCK)
GROUP BY HoldConsumerGUID, HoldConsumerIdentity, HoldUserGroupID, HoldGroupIdentity, HoldDataIdentity
UNION ALL
SELECT
'4.2 - View Info'
, db_name()
, NULL, NULL, NULL, NULL, NULL, NULL
ORDER BY [VS Database] DESC, HoldConsumerGUID, HoldUserGroupID, HoldDataIdentity;
-- 4.3. Legal Hold counts per Hold type:
SELECT DISTINCT
[Script] = ''
, [VS Database] = ''
, [Legal Hold Type] = 'DA'
, [Total number of Legal Holds applied on the Vault Store Database] =
(SELECT COUNT(SavesetIdentity) FROM HoldSaveset WHERE HoldGroupIdentity IN (
SELECT HoldGroupIdentity FROM HoldGroup WHERE HoldUserGroupID LIKE 'DA_%'))
, [Numbers of distinct items on Legal Hold in the Vault Store Database] = (
SELECT COUNT(DISTINCT(SavesetIdentity)) FROM HoldSaveset WHERE HoldGroupIdentity IN (
SELECT HoldGroupIdentity FROM HoldGroup WHERE HoldUserGroupID LIKE 'DA_%'))
, hs.HoldGroupIdentity
, hg.HoldUserGroupID
, [Number of Legal Hold items per HoldUserGroupID] = COUNT(hs.SavesetIdentity)
FROM HoldSaveset AS hs
JOIN HoldGroup AS hg on hs.HoldGroupIdentity = hg.HoldGroupIdentity
WHERE hg.HoldUserGroupID LIKE 'DA_%'
GROUP BY hs.HoldGroupIdentity, hg.HoldUserGroupID
UNION ALL
SELECT DISTINCT ''
, ''
, 'Clearwell/eDP'
, (SELECT COUNT(SavesetIdentity) FROM HoldSaveset WHERE HoldGroupIdentity IN (
SELECT HoldGroupIdentity FROM HoldGroup WHERE HoldUserGroupID LIKE 'CW-%'))
, (SELECT COUNT(DISTINCT(SavesetIdentity)) FROM HoldSaveset WHERE HoldGroupIdentity IN (
SELECT HoldGroupIdentity FROM HoldGroup WHERE HoldUserGroupID LIKE 'CW-%'))
, hs.HoldGroupIdentity
, hg.HoldUserGroupID
, COUNT(hs.SavesetIdentity)
FROM HoldSaveset AS hs
JOIN HoldGroup AS hg on hs.HoldGroupIdentity = hg.HoldGroupIdentity
WHERE hg.HoldUserGroupID LIKE 'CW-%'
GROUP BY hs.HoldGroupIdentity, hg.HoldUserGroupID
UNION ALL
SELECT
'4.3 - Legal Hold Type Counts'
, db_name()
, NULL, NULL, NULL, NULL, NULL, NULL
ORDER BY [VS Database] DESC, hg.HoldUserGroupID DESC;
-- 4.4. Vault Store database Legal Hold information per Archive:
WITH APID_CTE AS
(SELECT ap.ArchivePointIdentity, a.ArchiveName, r.RootIdentity, r.VaultEntryId
FROM ArchivePoint AS ap (NOLOCK)
JOIN EnterpriseVaultDirectory.dbo.Root AS r (NOLOCK) ON ap.ArchivePointId = r.VaultEntryId
JOIN EnterpriseVaultDirectory.dbo.Archive AS a (NOLOCK) ON r.RootIdentity = a.RootIdentity OR r.ContainerRootIdentity = a.RootIdentity
--WHERE r.VaultEntryID = '123ABC456DEF789GHI123ABC456DEF789GHI0000EVSiteAlias' -- Use this line for a specific ArchiveID
/*WHERE r.VaultEntryID IN ( -- Use this block for multiple ArchiveIDs
-- List ArchiveIDs in a single-quote enclosed comma-separated list below
'123ABC456DEF789GHI123ABC456DEF789GHI0000EVSiteAlias'
, '223ABC456DEF789GHI123ABC456DEF789GHI0000EVSiteAlias'
, '3123ABC456DEF789GHI123ABC456DEF789GHI0000EVSiteAlias'
-- List ArchiveIDs in a single-quote enclosed comma-separated list above
)*/
)
SELECT DISTINCT
[Script] = ''
, [VS Database] = ''
, APID_CTE.ArchiveName
, APID_CTE.ArchivePointIdentity
, APID_CTE.RootIdentity
, APID_CTE.VaultEntryId
, [DA_tblConfig_LegalKey] = hc.HoldConsumerGUID
, [DA_tblCase_LegalHoldGroupID] = hg.HoldUserGroupID
, [Hold Type] = CASE WHEN hg.HoldUserGroupID LIKE 'DA_%' THEN 'DA Legal Hold'
WHEN hg.HoldUserGroupID LIKE 'CW-%' THEN 'Clearwell/eDP Data Hold'
ELSE 'Unknown Hold Type' END
, [Legal Holds] = COUNT(HS.HoldIdentity)
FROM HoldSaveset AS hs (NOLOCK)
JOIN APID_CTE ON hs.ArchivePointIdentity = APID_CTE.ArchivePointIdentity
JOIN HoldGroup AS hg (NOLOCK) ON hs.HoldGroupIdentity = hg.HoldGroupIdentity
JOIN HoldConsumer AS hc (NOLOCK) ON hg.HoldConsumerIdentity = hc.HoldConsumerIdentity
GROUP BY APID_CTE.ArchiveName, APID_CTE.ArchivePointIdentity, APID_CTE.RootIdentity, APID_CTE.VaultEntryId, hc.HoldConsumerGUID, hg.HoldUserGroupID
UNION ALL
SELECT DISTINCT
'4.4 - Legal Holds Per Archive'
, db_name()
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
ORDER BY [VS Database] DESC, hc.HoldConsumerGUID, APID_CTE.ArchiveName, [Hold Type] DESC, hg.HoldUserGroupID;
-- 5. Vault Interest information, execute against the EnterpriseVaultDirectory database:
USE EnterpriseVaultDirectory;
SELECT
[Script] = ''
, [Database] = ''
, *
FROM VaultInterest
UNION ALL
SELECT
'5 - VaultInterest'
, db_name()
, NULL, NULL, NULL, NULL, NULL
ORDER BY [Database] DESC, ConsumerName, VaultEntryId;
Once this data has been gathered, please contact Technical Support, advise the nature of the DA Legal Hold issue and provide the spreadsheet for review.
Optional Scripts
- To determine if an item is on Legal Hold in EV:
1. Determine TransactionID of the item. If the SavesetID is known, the TransactionID will be the set of characters after the last tilde in the SaveSetID. For example, if the SavesetID is 190001011234567~190001010000000000~Z~1234ABCD12AB34CD56EF123456ABCDEF, the TransactionID will be 1234ABCD12AB34CD56EF123456ABCDEF.
2. Format the TransactionID in the format 8-4-4-4-12. For example if the TransactionID is 1234ABCD12AB34CD56EF123456ABCDEF, the formatted TransactionID will be 1234ABCD-12AB-34CD-56EF-123456ABCDEF.
3. Edit the TransactionID as indicated in the following query and execute the query against the Vault Store database(s) in question (as Legal Hold can be applied on an item from multiple DA Customers and Cases, the output can list multiple entries for the same TransactionID):
SELECT
[VaultStore database] = db_name()
, [DA Customer database] = LEFT(RIGHT(hg.HoldUserGroupID, (LEN(hg.HoldUserGroupID) - 3)), LEN(RIGHT(hg.HoldUserGroupID, (LEN(hg.HoldUserGroupID) - 3))) - 37)
, [DA Customer (DA_tblConfig.LegalKey_DA)] = hc.HoldConsumerGUID
, [DA Case (DA_tblCase.LegalHoldGroupID)] = hg.HoldUserGroupID
, [TransactionID] = s.IdTransaction
, hs.ArchivePointIdentity
, hs.SavesetIdentity
, hs.HoldIdentity
, [DA_tblInmtDiscoveredItems.LegalHoldID] = CONVERT(nvarchar(50), hs.HoldIdentity) + '~' + CONVERT(nvarchar(50), hs.SavesetIdentity)
FROM HoldSaveset AS hs
JOIN Saveset AS s ON hs.SavesetIdentity = s.SavesetIdentity
JOIN HoldGroup AS hg ON hs.HoldGroupIdentity = hg.HoldGroupIdentity
JOIN HoldConsumer AS hc ON hg.HoldConsumerIdentity = hc.HoldConsumerIdentity
WHERE s.IdTransaction = '10A97530-3A64-A5E7-3B92-295811860A81' -- Edit TransactionID here in 8-4-4-4-12 format
ORDER BY [DA Customer database], [DA Case (DA_tblCase.LegalHoldGroupID)], hs.HoldIdentity;
Note
There may be circumstances where manual steps are needed to programmatically remove Legal Holds. An example would be decommissioning a DA Customer or DA environment without first removing all Legal Holds. In this event, Technical Support requires an approval email from an authoritative individual stating to remove Legal Hold and that the implications are well understood. If such a circumstance is identified, please indicate your approval via email reply stating that you have the authority to make this decision on behalf of the company and include the following statement, along with the required HoldConsumerGUID/LegalKey value(s) and any optional information (Customer Name, CustomerGUID, Customer Database Name) as per the information gathered above or as guided by Technical Support. Please include your title in your email signature.
I am confirming and approving our request for Arctera to remove Legal Hold from all Vault Store databases that are applied from the DA Customer(s) listed below. I am authorised by my company to make and approve such a request. I understand that all data in these Archives can and may be permanently and irrevocably deleted. I also understand data from these Archives may not be available for Review and/or Export/Production.
HoldConsumerGUID/LegalKey (required) Customer Name (optional) CustomerGUID (optional) Customer Database Name (optional)
<HoldConsumerGUID/LegalKey 1> <DA Customer Name 1> <DACustomerGUID 1> <DA Customer Database Name 1>
<HoldConsumerGUID/LegalKey 2> <DA Customer Name 2> <DACustomerGUID 2> <DA Customer Database Name 2>
. . .
<Signature and Title of authorising party>
Was this content helpful?
Translated Content
Please note that this document is a translation from English, and may have been machine-translated. It is possible that updates have been made to the original version after this document was translated and published. Veritas does not guarantee the accuracy regarding the completeness of the translation. You may also refer to the English Version of this knowledge base article for up-to-date information.