Forum Discussion

Merv's avatar
Merv
Level 6
15 years ago

SQL query to search for expired savesets?

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! Merve
  • 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

  • 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?advanced

    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
  • 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. Merve
  • 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

    Lastly!
    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

  • 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

  • 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