Video Screencast Help

SQL query to search for expired savesets?

Created: 24 Jan 2010 • Updated: 14 Oct 2010 | 5 comments
Merv's picture
This issue has been solved. See solution.

Hi Folks,

We are going to turn on storage expiry and expire based on modified date? Am looking for reports for expired savesets similar to the storage expiry report but mote detailed. Kinda like the expiry report which is on a per user basis but what we need now is at a admin level to get a snapshot if the items which will be deleted.

Thanks in advance!

Discussion Filed Under:

Comments 5 CommentsJump to latest comment

JesusWept3's picture

Well the Storage Expiry report on the server will use SQL queries and will break it down via the retention category

for instance:
lets say you expire items older than 90 days,
Modified Date/Sent Date query would be like

SELECT COUNT(*) FROM Saveset WHERE idDateTime < getDate(now()-90)

Based on Archived Date it would be

SELECT COUNT(*) FROM Saveset WHERE ArchivedDate < getDate(now()-90)

However the expiry report that is used through the buttons in outlook actually use the users index to determine what items are being expired, how many etc

So through SQL it's easy, on a per user basis its easy, but for everyone?
the best you can do to simulate that is to go to http://yourEVServer/EnterpriseVault/search.asp?adv...

then you can do a search based using dates against everyones archive, that should give you a list
just as long as the items are not on hold and the user is enabled for expiry

Merv's picture

Hi JW,

thanks for the detailed response. :)
So getting an expiry report isn't so simple..
About the savesets by modified date is it easy to include which vaulid/user is the saveset from? Let me try this out and check the saveset table for what columns it has. Thanks again.

JesusWept3's picture

yeah thats easy enough, you could do the following

SELECT EME.mbxDisplayName, COUNT(S.IdTransaction) AS ItemsToExpire

FROM EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
             EVVaultStoreDB.dbo.Saveset S,
             EVVaultStoreDB.dbo.ArchivePoint AP

WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
       AND AP.ArchivePointId = EME.DefaultVaultId
       AND S.IdDateTime < getDate() - 90

GROUP BY EME.MbxDisplayName

getDate()-90 should be however many days your expiry is set to
if you wanted to change this to use Archive Date and not modified Date, you'd use "AND S.ArchivedDate < getDate()"

Another caveat to the script above is that its only for Enabled users in the ExchangeMailboxEntry table, because its simpler to write that pushing across to the Root/Vault/Archive tables.

If you want to exclude items that aren't on hold (if using Discovery Accelerator) you can link to the SavesetHold table and use a NOT query using SavesetIdentity and ArchivePointIdentity

When enterprise vault runs storage expiry, it calculates it to the very second

So lets say you are targeting 90 days ago
if you run expiry in the morning it may say it has 100 items to expire, but lets say 90 days ago in the afternoon you imported a PST file , you might find if you run the expiry a few hours later, it comes back with 10000 items to expire

JesusWept3's picture

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

JesusWept3's picture

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