SQL Query for nightly archive statistics

[kyuen]

Hello, I wanted to see if there was a query to find the nightly archiving statistics.  What I am looking for is the Exchange Server (s), Items Archived, Mailboxes Archived, Size of Items, Start time and End time.

The query below has some of what I need.  It does not have the Start time, End time and Mailboxes archived.  I wanted to run this as a job on our SQL server so we can send a message every morning with the info.

thanks in advance,

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,
EVMailboxArchives.dbo.ArchivePoint AP,
EVMailboxArchives.dbo.Saveset S
WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointId = EME.DefaultVaultId
AND EME.MbxStoreIdentity = EMS.MbxStoreIdentity
AND EMS.ExchangeServerIdentity = ESE.ExchangeServerIdentity
AND S.ArchivedDate > DATEADD(d, -1, getDate())
GROUP BY ESE.ExchangeComputer

[arnoldmathias · Accepted solution]

@kyuen here are my 2¢ on your query

Daily Archiving Report: I had written a query in Powershell for my environment a few years ago to query EV Archiving Task Reports and retrieve statistics. It is not a very clean method, but at that time it worked for me to get the same reports that you are looking for. You would need some expertise in Powershell to go through it and customize the code. The code is for 7 EV / Exchange servers (as it was in my environment at the time). I used to run this after all the archiving tasks have completed schedule and the scheduled task report is generated. Do let me know if you need help to modify it. Run this from a Powershell prompt on any EV server.

Size / Count of Items Archived per Exchange Server vs Vault Store: You would need to modify and enter the vault store database names and Exchange Server names to customise this script to your environment. Also, remember this is for 7 Exchange servers and 8 Vault Store databases so you would need to customise it. Unfortunately, I did not find the need or motivation to optimise the code further as it worked for me. Run this from a Powershell prompt on any EV server.

These two reports should give you good statistics to begin with. PM me if you may need assistance.

[GertjanA]

Hello. Depending on which version you are on, can you not use the Mailbox Archiving task reports to get this information? I'm not sure you get the time, but you do get the other statistics.

[kyuen]

Thank you Gertjan,

Yes the reports has the information, but I wanted to get an email with a summary of the info to our group so they can get and review the quick info it will save time and also not everyone has access to the Vault server. 

Thanks again,

[kyuen]

Thank you Arnoldmathias!

The 'Daily Archiving report' file looks to have everything I need, thanks!!!