How to get the size of a specific archive?

[mistervault]

Hello,

I need to get the size of a specific archive by SQL query or script.

I don't want to use the report usage because I need to script it.

Thanks

[AndrewB]

SELECT ESE.ExchangeComputer "Exchange Server", 
       COUNT(S.IdTransaction) "Items Archived",
       SUM(S.ItemSize)/1024 "Size of Items (MB)"
FROM   EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
       EnterpriseVaultDirectory.dbo.ExchangeMailboxStore EMS,
       EnterpriseVaultDirectory.dbo.ExchangeServerEntry ESE,
       yourVaultStore.dbo.ArchivePoint AP,
       yourVaultStore.dbo.Saveset S
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = EME.DefaultVaultId
  AND  EME.MbxStoreIdentity = EMS.MbxStoreIdentity
  AND  EMS.ExchangeServerIdentity = ESE.ExchangeServerIdentity
GROUP BY ESE.ExchangeComputer

[TonySterling · Accepted solution]

Andrew's query will give you items size per Exchange server but if that isn't what you want then try this:

SELECT A.ArchiveName, COUNT(S.IdTransaction) AS ArchivedItemCount, SUM(S.ItemSize/1024) AS ArchivedItemSize
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
YourVaultStoreDB.dbo.ArchivePoint AP,
YourVaultStoreDB.dbo.Saveset S

WHERE (A.ArchiveName = 'archivename' )
AND S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointId = R.VaultEntryID
AND R.RootIdentity = A.RootIdentity

GROUP BY A.ArchiveName

[TonySterling]

Or this one from Alex found here:

https://www-secure.symantec.com/connect/forums/ev-sql-query-list-all-maiboxes-mailboxes-and-out-ev-archives-mailbox-display-name-exchange-se#comment-7521381

SELECT EME.MbxDisplayName "Mailbox Name",
       ESE.ExchangeComputer "Exchange Server",
       EMS.Name "Exchange Database",
       VSE.VaultStoreName "Vault Store"
FROM   ExchangeMailboxEntry EME,
       ExchangeMailboxStore EMS,
       ExchangeServerEntry ESE,
       Root R,
       Archive A,
       VaultStoreEntry VSE
WHERE  EME.MbxStoreIdentity = EMS.MbxStoreIdentity
  AND  EMS.ExchangeServerIdentity = ESE.ExchangeServerIdentity
  AND  EME.DefaultVaultId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
  AND  A.VaultStoreEntryId = VSE.VaultStoreEntryId
ORDER BY ESE.ExchangeComputer, EMS.Name, EME.MbxDisplayName

[mistervault]

In fact, I just realized something.

When we access to this page : http://localhost/enterprisevault/usage.asp

After, when we select a specific store, it calls the file "listvaults.asp" : 

http://localhost/enterprisevault/listvaults.asp?vaultstoreeid=xxxxx&InfoLevel=1&AltServer=

This file "listvaults.asp" is located here : C:\Program Files (x86)\Enterprise Vault\webapp

It's the file used by Vault to perform the summary report by size in VBScript, interesting.