07-21-2014 02:23 AM
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 ?
07-24-2014 03:29 AM
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.