cancel
Showing results for 
Search instead for 
Did you mean: 

EV Hold Data

Deepak_Jindal
Level 4

Hi,

 

Please let me know how can we verify if someone has put EV hold via Cleaewell console? Is there any way through which we can confirm that this data has been put on Hold under EV ?

1 REPLY 1

Daly
Level 5
Partner Employee Accredited Certified

Hello Deepak,

I've taken a look internally and can see we have some SQL from the EV support team that will report all items on-hold against a user, as this query would may need some engineering as it may not be the same database, it would be best to raise a support case with EV if you have any issues with the below:

Here is the SQL:

 

USE EVVSExpressVaultStore_1

 

DECLARE @ArchiveName nvarchar(75)

DECLARE @ArchiveID nvarchar(75)

DECLARE @FolderID nvarchar(75)

 

SET @ArchiveID = Null

SET @ArchiveName = NULL

SET @FolderID = NULL

 

SELECT

DISTINCT SQLServer

,VSE.VaultStoreName

,VSE.DatabaseName

,IdTransaction

,(Cast (ss.idchecksumhigh as varchar(7)) + Cast(ss.idchecksumLow as varchar(8)) + '~' + Replace(Replace(Replace(Replace(CONVERT(VARCHAR(23), ss.iddatetime, 121),'-',''),' ',''),'.',''),':','') + '0~' + 'Z' + '~' + Replace(Cast(ss.idtransaction as varchar(36)),'-','') ) as SavesetID

,ArchivedDate AS "Archived Date"

,IDDateTime AS "Item Creation Date"

,CASE WHEN JA.TransactionID is null THEN 'Not in JA Table' ELSE 'In JA Table' END AS SS_IN_JA

,CASE WHEN WF.ArchiveTransactionID is null THEN 'Not in WF' ELSE 'In Watchfile' END AS SS_IN_WF

,CASE WHEN JD.ItemSeqNo is NOT null THEN CONVERT(VARCHAR(24), JD.DeletionDate) ELSE 'N/A' END AS JD_DeletionDate_UTC

,CASE WHEN JD.DeletionReason = '0' THEN 'None' WHEN JD.DeletionReason = '1' THEN 'By User' WHEN JD.DeletionReason = '2' THEN 'By Expiry' WHEN JD.DeletionReason = '3' THEN 'By System' WHEN JD.DeletionReason is NULL THEN 'N/A' ELSE 'Unknown' END AS DeletionReason

,CASE WHEN JD.IndexCommitted = '1' THEN 'Removed' WHEN JD.IndexCommitted = '0' THEN 'Not Removed' ELSE 'N/A' END AS 'Item Removed From Index'

,CASE WHEN Archive.ArchiveStatus = '1' THEN 'Archive Enabled' WHEN Archive.ArchiveStatus = '3' THEN 'Archive Disabled' WHEN Archive.ArchiveStatus = '4' THEN 'Marked for Deletion' ELSE 'Other' END AS ArchiveStatus

,Archive.ArchiveName

,CAST (FolderPath AS NVARCHAR(MAX)) AS FolderPath

,CASE WHEN EMAF.IsManaged = '1' THEN 'Managed' ELSE 'Not Managed' END AS 'Exchange Managed Folder?'

,CASE WHEN EMAF.ExistsInMbx = '1' THEN 'Exists' WHEN EMAF.ExistsInMbx = '2' THEN 'Synchronizing' WHEN EMAF.ExistsInMbx = '0' THEN 'Not in Mbx' ELSE 'Unknown' END AS 'Folder in Mailbox?'

,ss.RetentionCategoryIdentity

,RCE.RetentionCategoryName

,RCE.RetentionPeriod

,CASE WHEN RCE.RetentionPeriodUnits = '0' THEN 'Days' WHEN RCE.RetentionPeriodUnits = '1' THEN 'Weeks' WHEN RCE.RetentionPeriodUnits = '2' THEN 'Months' ELSE 'Years' END AS RetentionIncrement

,CASE WHEN RCE.OnHold = '1' THEN 'Delete Prevented by Retention' ELSE 'Delete Allowed by Retention' END AS RetentionOnHold

,r1.VaultEntryId as ArchiveID

,r2.VaultEntryId as FolderID

,CASE WHEN SS.collectionidentity is null THEN 'Not Collected' ELSE 'Collected' END AS CollectionStatus

,CASE WHEN Collection.collectionidentity is NOT null THEN Collection.RelativeFileName ELSE 'Not Collected' END AS CollectionName

,CASE WHEN SStore.StoreIdentifier is NOT null THEN SStore.StoreIdentifier ELSE 'Not Single Clip (Centera)' END AS SingleClipName

,CASE WHEN VH.SavesetIdentity is null THEN 'Not on Legal Hold' ELSE 'On hold' END AS OnHold_DA

,CASE WHEN VI.VaultEntryId is null THEN 'Archive Not Of Interest' ELSE 'Archive Of Interest' END AS OfInterest_DA

 

FROM

EnterpriseVaultDirectory.dbo.Root r1

LEFT JOIN EnterpriseVaultDirectory.dbo.Root r2 on r1.rootidentity = r2.containerrootidentity

JOIN EnterpriseVaultDirectory.dbo.Archive Archive on r1.rootidentity = Archive.rootidentity

JOIN EnterpriseVaultDirectory.dbo.ArchiveFolder AF on r2.RootIdentity = AF.RootIdentity

JOIN EnterpriseVaultDirectory.dbo.ExchangeMbxArchiveFolder EMAF on AF.RootIdentity = EMAF.RootId

JOIN EnterpriseVaultDirectory.dbo.VaultStoreEntry VSE on Archive.VaultStoreEntryId = VSE.VaultStoreEntryId

JOIN Vault on r2.VaultEntryId = Vault.Vaultid

JOIN Saveset ss on Vault.VaultIdentity = ss.VaultIdentity

LEFT OUTER JOIN JournalArchive JA on JA.TransactionID = ss.IDTransaction

LEFT OUTER JOIN WatchFile WF on WF.ArchiveTransactionID = ss.IDTransaction

Left Outer Join JournalDelete JD ON JD.ArchivePointIdentity = ss.ArchivePointIdentity AND jd.ItemSeqNo = ss.IndexSeqNo

JOIN EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RCE on RCE.RetentionCategoryIdentity = ss.RetentionCategoryIdentity

LEFT JOIN Collection on ss.CollectionIdentity = Collection.CollectionIdentity

LEFT OUTER JOIN view_Holds VH on VH.SavesetIdentity = ss.SavesetIdentity

LEFT OUTER JOIN EnterpriseVaultDirectory.dbo.VaultInterest VI on VI.VaultEntryId = r1.VaultEntryID

LEFT OUTER JOIN SavesetStore SStore on SStore.SavesetIdentity = ss.SavesetIdentity

WHERE

r1.VaultEntryid= ISNULL(@ArchiveID, r1.VaultEntryid)

AND ArchiveName = ISNULL(@ArchiveName, ArchiveName)

AND r2.VaultEntryid = ISNULL(@FolderID, r2.VaultEntryId)

The column to determine if on hold is 'onhold DA'.

Please do not use the above if you are not comfortable running SQL queries, please contact support.