Determine Oldest Items in Vault

[Andrew_Tankersl]

Is there a way to determine the oldest available items within a vault store without having to do a search in either CA or DA?

[CFreeX]

Not sure why, but run query below against your Vault Store to find the Archive name (MBXAlias) and Sequence Number (IndexSeqNo) then use Search.asp?advanced=3 to retrieve the item.

SELECT TOP(100) ss.IdDateTime, SS.ArchivedDate, SS.IdTransaction, ss.IndexSeqNo, AP.ArchivePointIdentity, ME.MbxAlias, ME.DefaultVaultId

FROM Saveset SS JOIN ArchivePoint AP on SS.ArchivePointIdentity = AP.ArchivePointIdentity 

JOIN EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ME ON AP.ArchivePointId = ME.DefaultVaultId 

Order by ss.IdDateTime 

[AndrewB · Accepted solution]

hi andrew! how've you been? here's a quick sql query i whipped up that should be able to help with what you're asking for. change the parameters in < > to match your environment. for the "select top x" you can do top 1 to see how old the oldest item is, top 10 for the 10 oldest items, etc.

use <name of vault store db>

select top <x> * from Saveset

order by IdDateTime asc

[AndrewB]

looks like we posted at the same time :)

[Andrew_Tankersl]

Thanks for the tip gents - I'll give it a shot

[JesusWept3]

Another couple of queries you can use.

This one will list all the archives, the oldest item by sent/received date and oldest item by archived date

SELECT A.ArchiveName, 
       CONVERT(VARCHAR(20), IV.OldestItemDateUTC, 100) "Oldest Sent/Received Date",
       CONVERT(VARCHAR(20), IV.OldestArchivedDateUTC, 100) "Oldest Archived Date"
FROM   Archive A, IndexVolume IV
WHERE  IV.RootIdentity = A.RootIdentity
  AND  IV.OldestItemDateUTC IS NOT NULL
ORDER BY IV.OldestItemDateUTC

This one will give you the yyyy-mm and the amount archived in that date period.
so like an out put might be

2001-01, 424
2001-02, 959

SELECT CONVERT(VARCHAR(7), idDateTime, 120) "Sent Dates",
       COUNT(IdDateTime) "No. Items Archived"
FROM   Saveset
GROUP BY CONVERT(VARCHAR(7), idDateTime, 120)
ORDER BY CONVERT(VARCHAR(7), idDateTime, 120)

[AndrewB]

note: run the first one against your EV direcroty db and the second one against your vault store db