cancel
Showing results for 
Search instead for 
Did you mean: 

EV10 query to find items older than.

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

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

Regards. Gertjan
1 ACCEPTED SOLUTION

Accepted Solutions

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

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

Regards. Gertjan

View solution in original post

2 REPLIES 2

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

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

Regards. Gertjan

Cesedx
Not applicable

thanks!!!