cancel
Showing results for 
Search instead for 
Did you mean: 
torsque
Level 4
Partner Accredited

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;

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.

Comments
AndrewB
Moderator
Moderator
Partner    VIP    Accredited

thank you for contributing this

Saswata_Basu
Level 4
Employee Accredited Certified

Great work Torsque, Symantec should plan to include the Archiving Report in its Daily Operation Report in EV Reporting Module. Can we create SQL Reporting Service reports from the EVReports Database you had imported the Archiving Report.

torsque
Level 4
Partner Accredited

Thanks! Sure, you can create reports via SQL reporting. Also, you can create daily notifications with SQL reporting.

susika
Level 3

great work, but on my server with german language settings I get some errors with bulk import like this one:

Datenkonvertierungsfehler beim Massenladen (Typenkonflikt oder ungültiges Zeichen für die angegebene Codepage) für 1-Zeile, 1-Spalte (Mailbox_display_name).

Meldung 4864, Ebene 16, Status 1, Zeile 1

 

after 10 times it stops.

Michael_Bilsbor
Level 6
Accredited

Hi,

I was the Director of Customer Experience at Symantec who got these reports developed so I'm glad you liked them.

 

As you say, the feature is incomplete and I hope one day Symantec will go back and finish it.

There were two other changes that were specified at the time but not implemented due to time constraints. 

1. An overall archive report.  At the moment as you'll know the report is per server, however the summary values themselves are held in the database.  So the idea was to then have a web page which would list all the report from the different servers together as one page.  

2. Archiving Heath Status check.  So the idea was that System Status/SCOM would be enhanced.  There would be checks which would interpret the values from the archive report to determine if there is an issue.  So for example if it's X days since an anything was archived from Archive Task X, then that would generate a warning.

 

 

torsque
Level 4
Partner Accredited

@Susika, It's just English compatible :( But if you can send the reports in German language I can reorganize scripts for you.

@Michael, thank you for your comment. Actually our main goal was have overall archive report via e-mail. I attached a screenshot that I get from this script.(taskreports.png)

Also it is possiable to have failed archive report per user daily via e-mail! (archiverepot.png)

 

And really thank very much Michael for leading such a great feature.

 

 

 

Version history
Last update:
‎03-23-2015 03:43 PM
Updated by: