01-24-2010 07:48 AM
Solved! Go to Solution.
01-25-2010 03:51 PM
OK last SQL Query i promise, this one does the following
- Makes sure the user is enabled for expiry (enterpriseVaultDirectory.dbo.DeleteExpiredItems = 1)
- Discounts items in the SavesetHold table (if they're on hold, they would not be deleted)
- Links to the Archive table and not the ExchangeMailboxEntry table (so that it encompasses archives without users, shared archives etc)
SELECT A.ArchiveName, COUNT(DISTINCT(S.IdTransaction)) AS ItemsToExpire, RCE.RetentionCategoryName
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RCE,
EVVaultStore.dbo.Saveset S,
EVVaultStore.dbo.ArchivePoint AP,
EVVaultstore.dbo.HoldSaveset HS
WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointId = R.VaultEntryId
AND R.RootIdentity = A.RootIdentity
AND S.RetentionCategoryIdentity = RCE.RetentionCategoryIdentity
AND S.SavesetIdentity != HS.SavesetIdentity
AND S.ArchivePointIdentity != HS.ArchivePointIdentity
AND S.IdDateTime < DateAdd(year, -1, GetDate())
AND RCE.RetentionCategoryName = 'Business'
AND A.DeleteExpiredItems = 1
GROUP BY A.ArchiveName, RCE.RetentionCategoryName
ORDER BY A.ArchiveName
01-24-2010 08:07 AM
01-24-2010 07:07 PM
01-25-2010 07:23 AM
01-25-2010 01:55 PM
Actually i think this query would be better, but would require running a SQL Query per retention category and knowing its retention period:
SELECT EME.mbxDisplayName, COUNT(S.IdTransaction) AS ItemsToExpire, RCE.RetentionCategoryName
FROM EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RCE,
EVVaultStore.dbo.Saveset S,
EVVaultStore.dbo.ArchivePoint AP
WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointId = EME.DefaultVaultId
AND S.RetentionCategoryIdentity = RCE.RetentionCategoryIdentity
AND S.IdDateTime < DateAdd(year, -1, GetDate())
AND RCE.RetentionCategoryIdentity = 'Business'
GROUP BY EME.MbxDisplayName, RCE.RetentionCategoryName
ORDER BY EME.MbxDisplayName
This would return all users that have items archived in the 'Business' Retention category, and based on the fact that in my example its a 1 year retention category, it then does everything with a modified date of 1 year or more older
hope that helps
01-25-2010 03:51 PM
OK last SQL Query i promise, this one does the following
- Makes sure the user is enabled for expiry (enterpriseVaultDirectory.dbo.DeleteExpiredItems = 1)
- Discounts items in the SavesetHold table (if they're on hold, they would not be deleted)
- Links to the Archive table and not the ExchangeMailboxEntry table (so that it encompasses archives without users, shared archives etc)
SELECT A.ArchiveName, COUNT(DISTINCT(S.IdTransaction)) AS ItemsToExpire, RCE.RetentionCategoryName
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RCE,
EVVaultStore.dbo.Saveset S,
EVVaultStore.dbo.ArchivePoint AP,
EVVaultstore.dbo.HoldSaveset HS
WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointId = R.VaultEntryId
AND R.RootIdentity = A.RootIdentity
AND S.RetentionCategoryIdentity = RCE.RetentionCategoryIdentity
AND S.SavesetIdentity != HS.SavesetIdentity
AND S.ArchivePointIdentity != HS.ArchivePointIdentity
AND S.IdDateTime < DateAdd(year, -1, GetDate())
AND RCE.RetentionCategoryName = 'Business'
AND A.DeleteExpiredItems = 1
GROUP BY A.ArchiveName, RCE.RetentionCategoryName
ORDER BY A.ArchiveName