Video Screencast Help
Security Community Blog

Get all List of all Archives

Created: 08 Dec 2009 • 1 comment
Wayne Humphrey's picture
+2 2 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

Flir's picture

 

Hey, I’m trying to use this code but am unable to find ExchangeServerIdentity in both the ExchangeMailboxEntry and ExchangeServerEntry.   It is in ExchangeServerEntry but not ExchangeMailboxEntry.  I’m running 9.0.2.  Am I missing something?  Any help is greatly appreciated.

-2
Login to vote