cancel
Showing results for 
Search instead for 
Did you mean: 

finding out the age of stub files in mailboxes

Mikeydee135
Level 4

Hi All

 

We're considering turning on Shortcut cleanups to remove stub files in Exchange mailboxes to reduce the number of items stored in same, while discussing how to balance this with the disruption/confusion it might casue having to refer users to the archive explorer or search rather than where they're used to seeing the shortcuts a questions was asked, how many shortcuts are over x age, how many over y? in short what will be gain in terms of esxcahnge for the policies so we can weight these against potential confusion.

 

i know most things EV are held in SQL somewhere so this can't be too hard to find out. either through an off the shelf report (i can't find one) or through a customer report or query. 

 

how can we find out how many stubs relate to messages over 4,5,10,15 years in the excahgne mailboxes if we found that hundreds of thousands are for messages over 10 years then we'd definately gain from removing these as our exchagne environment could do with a little pressure released.

 

Thanks 

 

Mike

1 ACCEPTED SOLUTION

Accepted Solutions

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

Sure, you just change ArchivedDate to IdDateTime.

SELECT    ex.MbxDisplayName,
        CONVERT(VARCHAR(4), s.IdDateTime, 120) "Sent Dates",
        COUNT(s.IdDateTime) "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), s.IdDateTime, 120)
ORDER BY ex.MbxDisplayName, CONVERT(VARCHAR(4), s.IdDateTime, 120)

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

 

What are you basing Expiry on?  See Article URL http://www.symantec.com/docs/TECH35761

To delete the shortcuts based on received date base it on Modified date.  You will probably want to set Storage Expiry to run Never so you don't inadvertently delete the archived mail.  :)

View solution in original post

7 REPLIES 7

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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

JosephRodgers
Level 4
Employee Accredited Certified

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

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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)

Mikeydee135
Level 4

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

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

Sure, you just change ArchivedDate to IdDateTime.

SELECT    ex.MbxDisplayName,
        CONVERT(VARCHAR(4), s.IdDateTime, 120) "Sent Dates",
        COUNT(s.IdDateTime) "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), s.IdDateTime, 120)
ORDER BY ex.MbxDisplayName, CONVERT(VARCHAR(4), s.IdDateTime, 120)

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

 

What are you basing Expiry on?  See Article URL http://www.symantec.com/docs/TECH35761

To delete the shortcuts based on received date base it on Modified date.  You will probably want to set Storage Expiry to run Never so you don't inadvertently delete the archived mail.  :)

Mikeydee135
Level 4

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.

MarkBarefoot
Level 6
Employee

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