finding out the age of stub files in mailboxes

This will tell you the count for items by archived date which will be the age of the shortcut.  You can use the where clause if you want and put in a date range or run it without. Runs against your vault store database:

SELECT ex.MbxDisplayName,
"ArchiveDate" = left (convert (varchar, s.ArchivedDate,20),4),
"Count" = count (s.ArchivedDate)
FROM Saveset s
Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
-- WHERE s.ArchivedDate between '2007-08-01' and '2007-08-08'
GROUP BY ex.MbxDisplayName, left (convert (varchar, s.ArchivedDate,20),4)
ORDER BY ArchiveDate desc

--If you don't care to see it by mailbox run this one:

Select "ArchiveDate" = left (convert (varchar, s.ArchivedDate,20),4),
"Count" = count (s.ArchivedDate)
FROM Saveset s
Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
-- WHERE s.ArchivedDate between '2007-08-01' and '2007-08-08'
GROUP BY  left (convert (varchar, s.ArchivedDate,20),4)
ORDER BY ArchiveDate desc

You can also check out this link for queries to find old mail (modified date vs archive date)

https://www-secure.symantec.com/connect/forums/determine-oldest-items-vault

"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

I think he wants a total count of shortcuts so just the top * won't get him that.

I did plagerize JW2's scripts as they are quite a bit cleaner, both run against the vault store database.  The first still gives count for each archive by year.  The second is just a count by year.

SELECT    ex.MbxDisplayName,
        CONVERT(VARCHAR(4), ArchivedDate, 120) "Sent Dates",
        COUNT(ArchivedDate) "No. Items Archived"
FROM     Saveset S
Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
GROUP BY ex.MbxDisplayName, CONVERT(VARCHAR(4), ArchivedDate, 120)
ORDER BY ex.MbxDisplayName, CONVERT(VARCHAR(4), ArchivedDate, 120)

SELECT    CONVERT(VARCHAR(4), ArchivedDate, 120) "Sent Dates",
        COUNT(ArchivedDate) "No. Items Archived"
FROM     Saveset S
GROUP BY CONVERT(VARCHAR(4), ArchivedDate, 120)
ORDER BY CONVERT(VARCHAR(4), ArchivedDate, 120)

Hi Tony!

That one's fantastic, almost. is it possible to change "archived date" to the date the original email was received/modifed rather than when it was archived, we've only had EV for 10 months so the archived date is not so good, especially for imported PST files, but the modified date of the original message is what we're hoping to use to cleanup the shortcuts so that users can have the latest x years easily visible but if they need something dating back to the begining of time they'll have to use archive explorer.

Thanks so much for your help with this.

Regards

Mike

Tony 

it would have taken me days to work that query out and my eyes would have been bleeding long before i got it to work.

thanks for your help with this, not sure how we received and have archived messages from both 1904 and 2087 but i'll hazzard a guess that's our fault and not the fault of the query :)

thanks again for you help, this information will make descision making much simpler.

Mike

p.s. yes, i had in ind to follwo a procedure similar to the one in the article you've attached, thanks for that also though.

The random dates are normally from external email servers so nothing you, or anything, has done wrong.