cancel
Showing results forΒ 
Search instead forΒ 
Did you mean:Β 

SQL Query for Case Details Per Vault Store

AKL
Level 6
Certified

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...

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

1 ACCEPTED SOLUTION

Accepted Solutions

Kenneth_Adams
Level 6
Employee Accredited Certified

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

View solution in original post

1 REPLY 1

Kenneth_Adams
Level 6
Employee Accredited Certified

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