reporting on the age of archived messages

[Al_Dunbar]

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

[JesusWept3]

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

???

[JesusWept3 · Accepted solution]

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) 

[Al_Dunbar]

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.

[Al_Dunbar]

looks perfect. We'll try it out and (hopefully) mark your last post as the answer. Thanks for such a speedy response!

[JesusWept3]

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

[Al_Dunbar]

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...

[JesusWept3]

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)" 

[Al_Dunbar]

Thanks for the useful followup comments, they are sure to come in handy...