Video Screencast Help
Symantec Appoints Michael A. Brown CEO. Learn more.

SQL Query for Case Details Per Vault Store

Created: 11 Dec 2012 • Updated: 11 Dec 2012 | 1 comment
AKL's picture

Hello All

Recently I was being victim of two groups of legal team, one who needed me to run expiry and another who needs to keep on applying legal holds everywhere and I am trying to bring them to some common ground.

To do same, they requested me for case details that are stopping item/archive deletion from Enterprise vault and I am trying to accompalish same.

We're running Enterprise vault 9.0.1 and Discovery Accelarator 9.0.1. I used below article to start with.(SQL query mentioned in point 3)

After attempting to delete an Enterprise Vault (EV) archive, the archive remains marked for deletion and several errors are logged in the EV Event Viewer

The above query works great for single user archives, but I was really looking to give complete report and using this for 10K+ archives doesn't seems feasible, so I attempted to modify above query so that it gives me case details per vault store in place of per archive. I came up with below query so far:

select tc.Caseid, tc.Name 'Case/Department/Folder Name', tvs.Name 'Vault Store Name', tis.SearchID, tis.Name 'Search Name', tsv.NumHits from tblVaultStore tvs
--select Count (tis.Name), Sum (tsv.NumHits) from tblVaultStore tvs
Join tblIntVaultStore tivs ON tvs.VaultStoreID = tivs.VaultStoreId
Join tblIntSearches tis on tivs.CaseId = tis.CaseID
Join tblSearchVault tsv on tis.SearchID = tsv.SearchID
Join tblCase tc on tc.CaseID = tivs.CaseId
where tvs.Name = 'TestReProcess'
Union
select tc.Caseid, tc.Name 'Case/Department/Folder Name', tvs.Name 'Vault Store Name', tis.SearchID, tis.Name 'Search Name', tsva.NumHits from tblVaultStore tvs
--select Count (tis.Name), Sum (tsva.NumHits) from tblVaultStore tvs
Join tblIntVaultStore tivs ON tvs.VaultStoreID = tivs.VaultStoreId
Join tblIntSearches tis on tivs.CaseId = tis.CaseID
Join tblSearchVaultsArchived tsva on tsva.SearchID = tis.SearchID
Join tblCase tc on tc.CaseID = tivs.CaseId
where tvs.Name = 'TestReProcess'
Order by tvs.Name

There're couple issues with above though:

  1. It cannot give only count or sum as I attempted. The query errors out with missing group by attribute for tvs.name.
  2. It runs query same way for all vault stores and basically give very close results. I am assuming that's because it is giving results of all cases ran against each vault store irrespective of there uniqueness i.e. if Case A was ran on Vault Store 1 and Vault Store 2, same results will be generated for both vault stores.
  3. Also, I was trying to somehow club legalhold details & case open/close details into same results but couldn't figure out which table contains those details

This is my first time I am digging deep into DA SQL architecture so kinda hitting end point here. If someone can review & make appropiate corrections in above query please? OR if anyone has query ready that they're using which generates case count & number of hit details per vault store or per site, that'll be really helpful ! If you feel that overall approach is incorrect and there should be complete different path, kindly me know that as well.

Any insight will be appreciated !

Thanks

Comments 1 CommentJump to latest comment

Kenneth Adams's picture

AKL,

Have you resolved this issue yet?  If not, please let me know what your end result should be and I'll see what I can do to help.

FYI, the tblSearchVaultsArctived table contains the index volumes searched by accepted searches.  Accepted searches are the only searches that can get items placed on Legal Hold - provided the DA Case or Cases in which the searches were run are on Legal Hold.

The tblSearchVault table contains the index volumes searched by active or pending searches.  As active or pending, their hits would not be on hold.

You may also want to check out TECH72381, "Legal Hold Scripts", at http://www.symantec.com/docs/TECH72381.  Script 2 provides a listing of hold counts per Vault Store as known to the DA Customer database.

Ken

Ken Adams

Backline Support for CA, DA, ACE, UCE, PSTD, ARMS, EVDC
US Support Region