Retention categories and expired mail

[Rem_y]

Hi folks, I recently extracted vault information from a single shortcut a user of mine is unable to retrieve

I'm trying to demonstrate the reason he can't retrieve the item is because it has been subject to a retention category and is an expired item

Can someone point me in the right direction for translating the following Retention Category ID -  175FF8DC8685D9D4A8B419BA5ABD145331b10000 - into an actual folder name as it would appear in the VAC policies, so I can say "it was archived into X category"

Secondly, how can I translate any of the following into a specific message reference so I can search for it and demonstrate, that it has expired?

Archive ID, Archive Transaction ID, Retention Category, Saveset ID 
 

[JesusWept3 · Accepted solution]

So what you want to do is go to the properties of the Item and find the "Saveset ID"
201306262275566~201306191649510000~Z~D0181F4FA488F6B33B92D1123CE00D91

Pay attention to the end of the Saveset ID after the ~Z~
D0181F4FA488F6B33B92D1123CE00D91

Format this in to a valid transaction ID, it needs to be 36 characters, and is formatted like 8-4-4-4-12
i.e. D0181F4F-A488-F6B3-3B92-D1123CE00D91

If it has a ~0~ or ~1~ etc and not ~Z~ this means its an EV2007 saveset, and you need to add a 0 to the end to make it the correct length

Now that you have the transaction ID you can do something like

USE EVVSYourVaultStore_01
SELECT IdTransaction, ArchivedDate, idDateTime FROM Saveset WHERE idTransaction = 'D0181F4F-A488-F6B3-3B92-D1123CE00D91'

Now if the item has been expired, that should return no results.

You can get information from the Retention Category though, you can do a query like

SELECT RetentionCategoryName "Retention Category",
       (CAST(RetentionPeriod AS varchar(3)) + ' ' + 
        CASE RetentionPeriodUnits
        WHEN 0 THEN 'Days'
        WHEN 1 THEN 'Weeks'
        WHEN 2 THEN 'Months'
        WHEN 3 THEN 'Years'
        END) "Retention Period",
        CASE OnHold WHEN 0 THEN 'True' WHEN 1 THEN 'False' END "Allow Deletes",
        CASE ExpiryDateBasis WHEN 0 THEN 'Sent/Received Date' WHEN 1 THEN 'Archived Date' WHEN 2 THEN 'Use Site Setting' END "Expiry Based On"
FROM RetentionCategoryEntry
WHERE RetentionCategoryId = '175FF8DC8685D9D4A8B419BA5ABD145331b10000'

Then if you look at the SavesetID again, we can see the Archived Date and Sent/Received Date
201306262275566~201306191649510000~Z~D0181F4FA488F6B33B92D1123CE00D91

The first part is the Archived Date (06/26/2013) and Sent Date (06/19/2013)
So lets say the Retention comes back and says 7 Days expiry based on Archived Date
06/26/2013 + 7 Days = Eligible for expiry 07/03/2013

Based on Modified Date (Sent/Received Date)
06/19/2013 + 7 Days = Eligible for Expiry 06/26/2013

[Rem_y]

Jesus Wept, thank you for your information. this has provided me the solution. Many thanks