08-09-2012 01:35 PM
The closest I have found to what we need is mentioned here:
https://www-secure.symantec.com/connect/forums/report-mailbox-size-base-policy-settings
We have already implemented EV (version 8.0.5.1048) and have archived messages from mailboxes and PSTs. We are now being asked to provide a profile of the ages of archived messages by volume. I suspect that if management found that we were maintaining a significantly large volume of very old email (especially after importing huge volumes from very old PST files), they might re-think their initial decision to not expire email based on age.
None of the titles of the available data analysis or operations reports mentions anything about message timestamps or age. Is there a standard report that can do what we want, either on a site-wide, vault store group wide, or even partition-side basis?
Failing that, if the information is available in the EV databases we might be able to get what we need with a SQL query. Any pointers on which tables to look in would be a great help.
Thanks in advance for any assistance you can provide us.
/ Al Dunbar
Solved! Go to Solution.
08-09-2012 02:10 PM
This will give you the Year, Amount of items and Size of items regardless of the users.... you will have to run this against each Vault Store you have
so replace "USE myVaultStore" with the database name of your vault store (i.e EVVSMailStore_1)
USE yourVaultStore SELECT YEAR(IdDatetime) "Year", COUNT(IdDateTime) "Items Archived", SUM(ItemSize)/1024 "Item Size (MB)" FROM Saveset GROUP BY YEAR(IdDateTime)
08-09-2012 02:06 PM
what are you looking for ? like...
2007 - 2189389 messages
2008 - 4839550 messages
2009 - 5839596 messages etc?
Or
MyUser1 - 2007 - 12345 messages
MyUser1 - 2008 - 69594 messages
MyUser2 - 2007 - 7035 messages
???
08-09-2012 02:10 PM
This will give you the Year, Amount of items and Size of items regardless of the users.... you will have to run this against each Vault Store you have
so replace "USE myVaultStore" with the database name of your vault store (i.e EVVSMailStore_1)
USE yourVaultStore SELECT YEAR(IdDatetime) "Year", COUNT(IdDateTime) "Items Archived", SUM(ItemSize)/1024 "Item Size (MB)" FROM Saveset GROUP BY YEAR(IdDateTime)
08-09-2012 02:11 PM
Probably the first form for now, although having a report by user might also be useful later.
Because this relates to an issue with the volumes of storage EV is consuming, we are more interested in the aggregate volumes of the messages than in just the number of messages.
08-09-2012 02:13 PM
looks perfect. We'll try it out and (hopefully) mark your last post as the answer. Thanks for such a speedy response!
08-09-2012 02:15 PM
And this query will give you the Archive Name, Years, item count and item size
again, edit the database names to match
SELECT A.ArchiveName "Archive Name", YEAR(s.IdDatetime) "Year", COUNT(s.IdDateTime) "Items Archived", SUM(s.ItemSize)/1024 "Item Size (MB)" FROM yourVault.dbo.Saveset s, yourVault.dbo.ArchivePoint ap, EnterpriseVaultDirectory.dbo.Root R, EnterpriseVaultDirectory.dbo.Archive A WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity AND AP.ArchivePointId = R.VaultEntryId AND R.RootIdentity = A.RootIdentity GROUP BY A.ArchiveName, YEAR(IdDateTime) ORDER BY A.ArchiveName
08-09-2012 02:45 PM
I ran the query on one of our larger vault stores, and it yeilded results, but with the following error message:
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.
I don't know if the results are nearly complete or if that error aborted the query. I'll ask our database guys about that tomorrow.
Also, I rather doubt that any of the messages were sent prior to1989, as we had no email system then... ;)
And the two messages from 1904 were probably the worlds first examples of spam...
08-09-2012 02:59 PM
out of whack dates are always spam, always!
anywho! you may need to do something like
SUM(S.ItemSize)/1024/1024 "Item Size (GB)"
OR do something like SUM(CONVERT(bigint, s.itemSize))/1024 "Item Size (MB)"
08-10-2012 08:34 AM
Thanks for the useful followup comments, they are sure to come in handy...