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 (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.

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 (http://www.microsoft.com/info/cpyright.htm).

Comments (50)

  1. dodeitte says:

    To convert from UTC to another time zone, there are a couple of ways of accomplishing this.  The easiest way for now, while we look at adding that functionality to the report is to do the following steps:

    1. Open the report in design view.

    2. Go to the Time(UTC) field, it should look like [MessageIdTime].

    3. Right click on the field and select Expression.

    4. Enter the following:

    =System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!MessageIdTime.Value)

    5. Save and publish the report.

    The thing to remember is that this will convert to the local time of the Report Server, not the client running the report, as expressions are evaluated on the server.

  2. dodeitte says:

    @Garry

    You can try deleting the data source and recreating it.  The report contains just the data source and report.  If you delete both of those, you would be starting over from scratch.  You can always switch the report to using a custom data source instead of the shared data source and fill in the connection string with your information.

  3. Anonymous says:

    Hi there

    I'm having the same issues with the "Cannot create a connection to data source 'DataSource1'". I've followed all the steps suggested above, including the "Windows Integrated Security" option versus the "Credentials Stored". To no avail.

    From any machine on the network, I can generate a report using only one account. I have mirrored permissions at every layer I can find for this working user and for a Domain Local Security Group I've created.

    Still nothing, any help out there?

    Regards

  4. dodeitte says:

    @vlazari@sits.md

    You would have to edit the report to apply the offset.  Currently the report just displays what's stored in the database (UTC).

  5. dodeitte says:

    @BrITman

    I reproed it on SQL 2005 as well.  I'm looking into it.  Thanks!

  6. dodeitte says:

    @Zac

    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

  7. dodeitte says:

    @Chad McGreanor

    Yes, we're planning on releasing a new report for Lync Server 2010.  In the meantime the current report will work.

  8. dodeitte says:

    @Garry

    Glad it's working for you.

  9. dodeitte says:

    @BrITman

    Glad to hear that that fixed your issue.  Thanks for the feedback!

  10. dodeitte says:

    @Steve

    This sounds like you entered the connection string incorrectly. Make sure that you’re using the latest version of the reports and follow the instructions above to configure the connection string. Also, if you’re using the Lync version of the reports, you posted this comment in the OCS blog post, so make sure to use the instructions in the Lync version blog post if that’s the case.

  11. dodeitte says:

    @Yves

    Did you follow the steps above to create the Data Source and change the report to use it?

  12. dodeitte says:

    @Anonymous

    I've been playing around with a similar idea for an update to the report.  It looks to be possible, you'd need to grab a couple more things from the database and edit the layout of the report.

  13. dodeitte says:

    @OCSguy

    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.

  14. dodeitte says:

    @Kurt

    Search is something that we're looking at for the Lync version of the report.

  15. dodeitte says:

    @Ecl

    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.

  16. dodeitte says:

    @Rose

    You should be able to since it's just an SSRS report, but I haven't tried it.  You would want to make sure that the report is named differently than the OCS report.

    If you can wait a little longer, I'm putting the finishing touches on the Lync version of the report.  It will include some additional features that people have requested, so that might be a better option for you.

  17. dodeitte says:

    @Michael Trebiani

    The timestamp is stored in the database in GMT.  If you wanted to localize it you would need to edit the report.

  18. dodeitte says:

    @Shiri

    The archiving database schema is not defined anywhere, but you can just open the LCSLog database with SQL Management Studio and look at everything.

  19. BrITman says:

    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.

  20. ZachF says:

    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?

  21. ECL says:

    Hi

    Will this work on lynch 2010?

    Thanks

    Ecl

  22. yves says:

    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.

  23. Michael Menzie says:

    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 mmenzie@mediacomcc.com if you can help

  24. Peter says:

    Hello,

    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.

    Thanks.

  25. OCSguy says:

    @peter

    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.

  26. OCSguy says:

    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?

  27. OCSguy says:

    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?

  28. Peter says:

    Hello,

    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.

  29. Chad McGreanor says:

    Are you going to update this report to support Lync 2010?

  30. Michael Trebiani says:

    Where does the report pull the IM timestamp information?  I am in the central time zone, but it looks like my timestamps are showing up as GMT.

  31. Ducnv says:

    I deployment OCS Archiving as you introduction. But when view report then receive message.

    An error occurred during client rendering.

    An error has occurred during report processing.

    Query execution failed for dataset 'LCSLOG'.

    Invalid object name 'Messages'.

    Please help me.

  32. BrITman says:

    Good news from my last post ref RTF issue — version 0.6 working good now 🙂

  33. Dan_IT says:

    I followed the directions and I am getting this error.

    An error has occurred during report processing. (rsProcessingAborted)

    Query execution failed for dataset 'lcslog'. (rsErrorExecutingCommand)

    Parser: The query contains the user1 parameter, which is not declared.

    Any thoughts?

  34. Garry says:

    Hi,

    I deleted my old OCS Archiving Report and then followed the steps to install v0.6 I also get the error: 'Cannot create a connection to data source 'DataSource1'. I was careful to ensure that the report referenced the LcsLog data source.

    After deleting the new OCS Archiving Report and then recreating it to use the previous .RDL file to effectively roll back to the version that worked for me, I now get:

    "Cannot create a connection to data source 'LcsLog". This worked flawlessly for me before.

    It's as though something is not being deleted properly when the new report is uploaded…

    Any ideas?

    Thanks.

  35. vlazari@sits.md says:

    Thank you for a very nice report! But How can I put in it GMT+2?

  36. Garry says:

    Hi dodeitte – I recreated entirely as you suggested and it fixed the problem. Many thanks, it's working a treat with Lync Server 2010!

  37. Anonymous says:

    Is there a way to modify the report to show the conversations in groups by sender or receiver.  For example a report would show the conversation ID, Sender, Receiver and to view the contents of the conversation ID, one would click on the conversation ID

  38. Dan_IT says:

    I really do like this report.  Just some suggestions.  Something for Group Chat, Conferencing, as well as the standard IM stuff would be great in some sort of a report.

  39. TheWeakestLync says:

    Is the Lync 2010 Updated Report soon to arrive ? Just curious 🙂

  40. Greg Fisher says:

    This report works great!  Thank you so much for making this.  Much better than the old powershell way of doing it.  Those querries used to take forever as we keep conversations indefinitely.  Being able to apply a date filter is perfect!  

  41. Your Momma says:

    Thanks works great. Have it working on my Lync server.

  42. Shiri says:

    Do you have the schema for archiving in Lync?

  43. Rose says:

    we have upgraded some of the users to lync and we still have ocs in place. i already implement OCS Archiving Report. can i install Lync 2010 IM Archive Report on the same server? will both application work?

  44. la_bruin says:

    My employer is looking for a search functionality not by user but by arbitrary terms found in the message field, is that even possible?

  45. Reverson says:

    Hello dodeitte!

    I'm from Brazil and utility OCSArchivingReport.

    I would like to know if you have any way to change the time that appears on the report because it is not compatible with my country's time zone (UTC -3:00 – Brazil).

    If in case there is a quick fix, would compile the file, so the time is compatible? Accepted ideas.

  46. Rooster_dude says:

    Where in the report do you specify a different time zone and how should it be specified?

    thanks!

  47. Reverson says:

    If I understand your question.

    In the field Time (UTC), have to add three hours.

    This is an example of my report:

    9/20/2012 11:37:12

    9/20/2012 11:37:19

    Instead of eleven o'clock should be fourteen o'clock.

    Thank you!

  48. mderosia says:

    Thank you much for this article, additional comments, and report download. This saved me alot of time with a Lync 2010 IM export. The formatting is way better than I can get using the Export-CsArchivingData command. It is nice to have all IMs in one spreadsheet to hand over to HR.

  49. Steve says:

    I have managed to get the report installed and produced some results from the search.
    However, when I try to view details of the call I get this:

    An error has occurred during report processing. (rsProcessingAborted)
    Query execution failed for dataset ‘SessionMessages’. (rsErrorExecutingCommand)
    Invalid object name ‘dbo.Messages’.

    Also

    When I click the dashboard (I have to enter my server (ASA-DB-001) report I get this:

    An error has occurred during report processing. (rsProcessingAborted)
    Cannot create a connection to data source ‘SQLServer’. (rsErrorOpeningConnection)
    Keyword not supported: ‘asa-db-001;integrated security’.

    What would be causing this?

  50. Rogério Galvão says:

    Hi,

    I am Brazilian.
    How do I get the database information with accents.

    example:

    Ne3o = Não
    Agradee7o = Agradeço