Notification of Exchange Archive Task Reports

Hi All,

Exchange Mailbox Archive Task Report is a really great feature to understand the archiving status. But, the bad part of this feature is no notifications and you must check every server individually.

We created some scripts which can import all reports to SQL server. So you can get notified and easily find archive reports with SQL scripts or SQL reporting.

Please share your comments to improve this script.

This script is just compatible with EV11, EV11.0.1 on Windows 2008R2SP1 and Windows 2012, 2012R2

(I hope someday Symantec directly imports these reports to SQL and we don’t need to use this script)

You’ll need below software;

• http://gnuwin32.sourceforge.net/packages/sed.htm
  • This tool required for parse CSV file.

On SQL Server

• Install sed
  • Please add "\sed" end of the location: C:\Program Files (x86)\GnuWin32\sed
• Create a folder for gather reports
  • C:\EVArchive_Report

• Copy CSV Files script;
  • You can find Copy_CSV_Files.txt file attached.
    • Change path for “$source” with your server name.
    • If you have EV servers more than one just copy from “#!!!!!!!!” to “#!!!!!!!!” mark and just change source. ( copy as many as your servers)
    • Change extension to .ps1
• Import CSV files script;
  • You can find Import_CSV_Files.txt file attached.
    • Change "<SQLSERVERNAME>" with “YOURSQLSERVER/INSTANCE”
    • Change extension to .bat
  •  

Open SQL management studio

• Create a new database with name “EVREPORTS”
• Create 2 tables in new database with attached scripts. (Archive_Report_Table_Create and Task_Summary_Table_Create)
• Create 1 job with 2 steps.
  1. Go to SQL Manager
  2. SQL Server Agent
  3. Jobs > New Job
  4. Type “EVImportJob” as name
  5. Go to Steps and click new
  6. Type a random step name
  7. Keep type as T-SQL
  8. Select EVREPORTS as database
  9. Use Step1.txt as command
  10. Goto Advanced tab
  11. Set “On Failure action:” go to the next step
  12. Click OK.
  13. Go to step 5 and create another step with Step2.txt

You are ready to run scripts!

Run order will be;

• Copy_CSV_Files.ps1
• Import_CSV_Files.bat

Now, you have many options to create fancy SQL queries! You can create notifications, SQL reporting and historical archiving reports.

I'm planning to share some helpfully SQL queries soon.