Video Screencast Help
Symantec Appoints Michael A. Brown CEO. Learn more.
Archiving and eDiscovery Community Blog

Get all List of all Archives

Created: 13 Dec 2009 • Updated: 29 May 2014 • 1 comment
Wayne Humphrey's picture
+4 4 Votes
Login to vote
This SQL Script will get all List of all Archives and dispaly the following:
  • Mailbox Name
  • Exchange Server
  • Number of Items (Mailbox) 
  • Number of (Archive)
  • Mailbox Size (MB) 
  • Archive Size (MB)
  • Total Size (MB) 
  • Archive Created
  • Archive Updated
SELECT 
LEFT(MbxDisplayName,20) 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,
EVVaultStore01..ArchivePoint AS VS1
 
WHERE 
EME.DefaultVaultID  = VS1.ArchivePointID AND
EME.ExchangeServerIdentity = ESE.ExchangeServerIdentity 

If you have more than one Vault Store you will need to join this with a union

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

Comments 1 CommentJump to latest comment

John Santana's picture

Wayne,

Thank you for sharingthe SQL script here.

However, I got the below error message:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'EVVaultstore1.archivepoint'.

When trying to execute against the EVVaultStore1 database. I'm using EV 9.0.4

 

Kind regards,

John Santana
IT Professional

--------------------------------------------------

Please be nice to me as I'm newbie in this forum.

0
Login to vote