Sample OCS Archiving Report Available

Update 6/2/13 - Sample Lync Server Archiving Report is now available! Click here for more information.
Update 5/29/13 - Updated report download link and moved report to TechNet Gallery.
Update 11/26/10 - OCSArchivingReport - 0.6 is available. Fixed an issue with RTF text not displaying in the results.
Update 8/26/10 - This blog post will serve as the official home of the OCSArchivingReport. Please post any questions/feedback in the comments.
Update 8/9/10 - OCSArchivingReport - 0.5 is available. Fixed an issue with multiparty IMs displaying incorrect results. Fixed the form automatically pulling data when loaded.

An easy way to get information out of the archiving database has been a common request from customers, but unfortunately a canned report wasn't inculded out of the box.  That is why I have created this SQL report as an easy way to query the database. 

It builds on the functionality provided by the Archiving PowerShell script that was written (https://communicationsserverteam.com/archive/2009/09/28/584.aspx) and adds a GUI interface, the ability to filter by date, and message formatting.  It has been tested against OCS 2007 R2 and SQL Reporting Services 2005.  You will need to have a functioning SQL Reporting Services server before trying to deploy this report.

Installation

  1. Download the latest version of the OCSArchivingReport.
  2. Open Report Manager – http(s)://<SRS Server>/Reports
  3. Click on New Folder
  4. Give the folder a name – i.e. OCSArchivingReport
  5. Click OK
  6. Click on the folder you just created
  7. Click on Upload File
  8. Browse to the location where you downloaded OCSArchivingReport.rdl and select the file
  9. Click OK to upload the report
  10. Click on New Data Source
  11. Enter LcsLog for the Data Source name
  12. Enter Data Source=<SQL Servername>;Initial Catalog=LcsLogfor the Connection String
    1. Replace <SQL Servername> with your SQL server\instance
  13. Select the Windows integrated security radio button
  14. Click OK
  15. Click on OCSArchivingReport
    1. You will see the following error: The report server cannot process this report. The data source connection information has been deleted. (rsInvalidDataSourceReference).   This is normal, since we haven’t linked the report to the data source we just created.
  16. Click on the Properties tab
  17. Click on Data Sources in the left-hand column
  18. Make sure A shared data source radio button is selected
  19. Click the Browse button
  20. Expand OCSArchivingReport and click on LcsLog
  21. Click OK
  22. Click Apply

The report is now linked to the data source and ready to be used.

Using the Report

The report allows you to enter the SIP URI of any 2 users that you want to view archived messages from.  If you enter “Any User” (case sensitive) for either of the user input boxes, you are able to view any message from any user to a specific user as well as any user to any other user.  You can use the Start Date and End Date to narrow down the search to a specific date range.  Once you have entered all of the inputs, click on View Report.
The results of the search are shown.  The First User column represents the sender of the message and the Second User column represents the recipient of the message.  The Message column shows the message that was sent as well any formatting on the message.  Changing Show Toast to Yes will show the toast messages as well as the Toast column.

 

Click here to download the latest version of the report.

A big Thank You to Rich Thorp for helping me put together this report!

 

* This is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of Use (https://www.microsoft.com/info/cpyright.htm).