Mail archive size reporting for specific time periods prior to an EV setup

[AlanJ]

Hi,

I am looking for a way to pull a report for mail archives for specific periods before EV was setup in an environment. I have a client who is currently running EV 9.0.2 and has had EV in their environment since 2009, so pulling reports year on year since then is no problem as they do have reporting for that, but they have requested reports on the datasize for the years prior to 2009 and obviously I can only see the archive status from once EV became operational in their environment.

They have a seven year retention period, so what they are looking for is reports on each of those seven years and want to know what  the total yearly data size is.

Is there any way that the metadata can be queried instead of the actual date when the individual mail item was archived?

Is this possibale at all?

Thanks in advance.

[Wayne_Humphrey]

I think I might just have what you need. Don’t forget to change EVVSMailboxVaultStore1Ptn1 to your relevant Vault Store Database name.

SELECT 
MbxDisplayName AS 'Mailbox',
ExchangeComputer AS 'Exchange Server',
MbxItemCount AS '#Items (Mailbox)',
VS1.ArchivedItems AS '#Items (Archive)',
MbxSize/1024 AS 'Mbx Size (MB)',
VS1.ArchivedItemsSize/1024 AS  'Archive Size(MB)',
(mbxsize+VS1.ArchivedItemsSize)/1024 AS 'Total Size(MB)',
VS1.CreatedDate AS 'Archive Created',
VS1.ModifiedDate AS 'Archive Updated', 
MbxExchangeState AS 'Exchange State'
FROM
EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry AS EME,
EnterpriseVaultDirectory.dbo.ExchangeServerEntry AS ESE,
EVVSMailboxVaultStore1Ptn1_1..ArchivePoint AS VS1
WHERE
EME.DefaultVaultID  = VS1.ArchivePointID AND
EME.MbxArchivingState = 1 AND
EME.MbxStoreIdentity = ESE.ExchangeServerIdentity

If you have more than one Vault Store you will need to join them with UNION and change EVMailboxVS02 to your relevant Vault Store Database name

UNION
SELECT 
MbxDisplayName AS 'Mailbox',
ExchangeComputer AS 'Exchange Server',
MbxItemCount AS '#Items (Mailbox)',
VS1.ArchivedItems AS '#Items (Archive)',
MbxSize/1024 AS 'Mbx Size (MB)',
VS1.ArchivedItemsSize/1024 AS  'Archive Size(MB)',
(mbxsize+VS1.ArchivedItemsSize)/1024 AS 'Total Size(MB)',
VS1.CreatedDate AS 'Archive Created',
VS1.ModifiedDate AS 'Archive Updated', 
MbxExchangeState AS 'Exchange State'
FROM
EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry AS EME,
EnterpriseVaultDirectory.dbo.ExchangeServerEntry AS ESE,
EVMailboxVS02..ArchivePoint AS VS1
WHERE
EME.DefaultVaultID  = VS1.ArchivePointID AND
EME.MbxArchivingState = 1 AND
EME. MbxStoreIdentity = ESE.ExchangeServerIdentity

Hope this helps :)

[JesusWept3 · Accepted solution]

So you want your reports to look something like

Year / Items / Size ....right?
i.e.

2005 / 1000 /10298
2006 / 2949 / 39505

The query would look something like this 

USE    EVYourVaultStore
SELECT DATEPART(yyyy, idDateTime) "Year",
       COUNT(idDateTime) "Archived Items",
       SUM(itemSize)/1024 "Item Size (MB)"
FROM   Saveset
GROUP BY DATEPART(yyyy,idDateTime)

However if you want the query to list
ArchiveName - Year - Items - Size..... the query would look like this

SELECT A.ArchiveName, 
       DATEPART(yyyy, s.idDateTime) "Year",
       COUNT(s.idDateTime) "Archived Items",
       SUM(s.itemSize)/1024 "Item Size (MB)"
FROM   EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.Root R,
       EVYourVaultStore.dbo.ArchivePoint AP,
       EVYourVaultStore.dbo.Saveset S
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
GROUP BY A.ArchiveName, DATEPART(yyyy,idDateTime)

[Rob_Wilcox1]

Also worth remembering that prior to EV being introduced, users may have been deleting chunks of data, moving them out to PSTs and so on.. so scanning for data prior to EV would be error-prone. Do they allow end-users to perform deletes on archived items? If so, then the reliability of archived item data usage is also somewhat questionable. What are they wanting to do with this data, if it could be obtained?

[AlanJ]

The users do not have the ability to delete from their archives. I don't think that the users would have deleted mails prior to EV, but of course I have no way of knowing that.

The reason that they want this data is for disk space utilization and forcasting. Trying to forcast future email volumes has its own challanges, but if we are able to provide them with reports highlighting disk space used for each of the seven years then at least that will give them some idea as to how much disk space they will need for the next 3 - 5 years.

[JesusWept3]

honestly i'm in agreement with Rob, your best bet is to go by the last 3 years as opposed to prior to that.
The chances of users not deleting anything or auto-archiving stuff to PST is so small
If you run those numbers I would be surprised if you didn't see huge increases year on year
so like 2006 to be like 200 items, 2007 300 items, 2008 500 items 2009 1300 items etc

But anywho, the queries i gave above will work for you

[AlanJ]

Thanks JesusWept3. Those queries you gave did the trick.

We have provided the client with the last 3 years of data, but should they wish to see data further back than that, at least we can provide it as a matter of interest rather than as a matter of fact.