11-19-2015 10:47 AM
Can someome help me? I need a SQL Query that returns the number of items in an archive based on mbx alias?
Any help appreciated.
EV V1101
Solved! Go to Solution.
11-19-2015 11:09 AM
This will give you a whole bunch of stats including item count. Replace <mailbox vault store db> with the name of your mailbox vault store database and replace <alias> with the mailbox alias you want to target. you can also remove the last line entirely and you'll get a list of all your archives
SELECT
MbxDisplayName AS 'Mailbox',
MbxAlias AS 'Mailbox Alias',
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,
EnterpriseVaultDirectory.dbo.ExchangeMailboxStore AS EMS,
<mailbox vault store db>.dbo.ArchivePoint AS VS1
WHERE
EME.DefaultVaultID = VS1.ArchivePointID AND
EME.MbxArchivingState = 1 AND
EMS.ExchangeServerIdentity = ESE.ExchangeServerIdentity
AND EME.MbxAlias = '<alias>'
11-19-2015 11:09 AM
This will give you a whole bunch of stats including item count. Replace <mailbox vault store db> with the name of your mailbox vault store database and replace <alias> with the mailbox alias you want to target. you can also remove the last line entirely and you'll get a list of all your archives
SELECT
MbxDisplayName AS 'Mailbox',
MbxAlias AS 'Mailbox Alias',
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,
EnterpriseVaultDirectory.dbo.ExchangeMailboxStore AS EMS,
<mailbox vault store db>.dbo.ArchivePoint AS VS1
WHERE
EME.DefaultVaultID = VS1.ArchivePointID AND
EME.MbxArchivingState = 1 AND
EMS.ExchangeServerIdentity = ESE.ExchangeServerIdentity
AND EME.MbxAlias = '<alias>'
11-19-2015 01:03 PM
Thank you very much Andrew!!