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 (http://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.
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.
A big Thank You to Rich Thorp for helping me put together this report!
Setup and working but only see message text for toast messages, so looks like not converting correctly to text for the Rich Text items. Data I can see is in the table, also if I remove the conversion call in your code I see the raw data, so with all the formatting codes. Not sure if related - but using SQL 2008 SP1 Reporting Services host, with database instance on another SQL 2008 SP1 host, both using Windows 2008 R2.
I was hoping to use this for OCS 2007, but not R2. can this be tweaked for that? or do you know where I can find another one?
Unfortunately the database schema changed between versions, so you will need to change the queries. You can find information on the R1 Archiving database schema here: http://technet.microsoft.com/en-us/library/bb964005(office.12).aspx
Will this work on lynch 2010?
Yes, in it's current form, the report does pull data from a Lync Server 2010 archiving database. The database schema is similar enough that the report will pull the information needed. There are additional tables in the database that might be interesting to include in the report. Once Lync Server 2010 RTMs, we'll look at updating the report. There is also a PowerShell command for pulling information from the archiving database. You can run "Get-Help Export-CsArchivingData -Examples" for more information on what is possible.
I reproed it on SQL 2005 as well. I'm looking into it. Thanks!
I'm getting an error... An error occurred during client rendering.
An error has occurred during report processing.
Cannot create a connection to data source 'DataSource1'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
I can connect to the FE sql database from another sql server just fine. I remember the first time i set this up with the older rdl file it worked. Not sure if any updates would cause this error. Any suggestions? Where else should i be looking at for errors? TIA.
Well if there is anyone out there willing to help me get this working on OCS 2007 (Non R2) i would greatly appreciate the assistance. my email address is firstname.lastname@example.org if you can help
Did you follow the steps above to create the Data Source and change the report to use it?
I have the similar issue reported by Yves. I managed to run the report on the SQL Report Servicesc server. However running the report from any other remote computer will fail with the same error than Yves.
Make sure you have permissions set properly and are logged in with the correct account. Also, I didn't need to use the instance name in the data source name, so you could try leaving it out.
I'm seeing the same issue that BrItman did - only the toast messages are showing up. Also using Server 2008 R2 and SQL 2008 Version 10.0.2531.0. I've disabled RTF and HTML locally and verified group policy either matches or is not defined for these settings. Any ideas as to why the messages are missing in the report?
Do the messages show up in the database? I know of an issue with the way the report is parsing RTF formatted messages from the database, which might be your issue. If so, we're working on a fix.
Yes, the messages are in the database. I can see them when I run the powershell script.
I look forward to the next version of your report, do you think it might be available before the Holidays?
I managed to resolve my issue by not using the "Windows integrated security" radio button. In used instead: "Credentials stored secyrely in the report server" radio button and check the "User as Windows credentials when connecting to the data store".
The user used there should have the read permission to the LcsLog database.