01-28-2015 12:34 AM
Hello all,
We have a retention category for mail-archived items which is 'keep forever'. There is a discussion going on internally to use Expiry, but the time-frame is not known yet. Discussions are everything older than 3 years, or maybe 5 or 7.
I need to find number of items and size that are older than an x-amount of years.
As example:
Find items which are archived older than 7 years, then older than 6 years etc. Based on that, I can do the maths on how many and how much will be deleted.
I've been trying to use several examples given on the forum, but they do not seem to match what I want. Can anyone come up with a query that allows me to gather this information?
What I would like as output:
Archivename, items older than 7 years, size of items older than 7 years.
Thanks!
Gertjan
Solved! Go to Solution.
01-28-2015 02:28 AM
Nvr mind.
I found the below one, which seems to fit the need.
SELECT A.ArchiveName "Archive Name",
COUNT(S.IdTransaction) AS ArchivedItemCount,
SUM(S.ItemSize)/1024 "Compressed Size (MB)",
SUM(SP.OriginalSize)/1024/1024 "Uncompressed Size (MB)"
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
YOURVAULTSTORE.dbo.ArchivePoint AP,
YOURVAULTSTORE.dbo.Saveset S,
YOURVAULTSTORE.dbo.SavesetProperty SP
WHERE A.RootIdentity = R.RootIdentity
AND R.VaultEntryId = AP.ArchivePointId
AND AP.ArchivePointIdentity = S.ArchivePointIdentity
AND S.SavesetIdentity = SP.SavesetIdentity
AND S.IdDateTime > DATEADD(year, -3, getDate())
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName
01-28-2015 02:28 AM
Nvr mind.
I found the below one, which seems to fit the need.
SELECT A.ArchiveName "Archive Name",
COUNT(S.IdTransaction) AS ArchivedItemCount,
SUM(S.ItemSize)/1024 "Compressed Size (MB)",
SUM(SP.OriginalSize)/1024/1024 "Uncompressed Size (MB)"
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
YOURVAULTSTORE.dbo.ArchivePoint AP,
YOURVAULTSTORE.dbo.Saveset S,
YOURVAULTSTORE.dbo.SavesetProperty SP
WHERE A.RootIdentity = R.RootIdentity
AND R.VaultEntryId = AP.ArchivePointId
AND AP.ArchivePointIdentity = S.ArchivePointIdentity
AND S.SavesetIdentity = SP.SavesetIdentity
AND S.IdDateTime > DATEADD(year, -3, getDate())
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName
01-28-2015 04:57 AM
thanks!!!