Video Screencast Help
Search Video Help Close Back
to help

Get all List of all Archives

Created: 08 Dec 2009 | 1 comment
Wayne Humphrey's picture
Wayne Humphrey Trusted Advisor Partner
0 0 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

Blog Entry Filed Under:
, , ,

Comments 1 Comment • Jump to latest comment

Flir's picture
Flir
Get all List of all Archives - Comment:21 Sep 2011 : Link

 

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.

0
Login to vote