Doug Deitterick's Blog

Information about Microsoft Lync, OCS, and Exchange UM.

Sample Lync Server Archiving Report Available

Sample Lync Server Archiving Report Available

  • Comments 100
  • Likes

Update 11/26/13 - I have release an updated version of the reports.  This version of the reports includes a Dashboard.  See the changelog below for more details on what's new.

The Sample Lync Server Archiving Report is an update to the popular Sample OCS Archiving Report, however this report has been completely redesigned based on feedback from the community.  We've expanded the report to include more information from the archiving database and we've changed the layout of the report to make the information easy to consume.

Features

  • Dashboard report to provide an overview about information in the archiving database
  • View detailed information about P2P IM conversations and conferences
  • Search for IMs and Conferences by SIP address and date
  • Select from multiple Lync Server Archiving databases to search against
  • Web-based SSRS reports can be used by anyone with rights and a web browser

The reports have been tested against Lync Server 2010 and Lync Server 2013 using SQL Reporting Services 2012.  You will need to have a functioning SQL Reporting Services server before trying to deploy this report.

Installing the Reports

  1. Download the latest version of the Sample Lync Server Archiving Report
  2. Extract the zip file
  3. Open Report Manager – http(s)://<SRS Server>/Reports
  4. Click on New Folder
  5. Give the folder a name – i.e. LyncArchivingReport
  6. Click OK
  7. Click on the folder you just created
  8. Click on Upload File
  9. Browse to the location where you extracted the LyncArchivingReport zip file and select Search.rdl
  10. Click OK to upload the report
  11. Repeat Steps 9 and 10 for the rest of the reports
  12. (Optional) - To remove the reports from the default view, since you can't directly access them:
    1. Go to the properties of the Conference Details Report by clicking on the drop-down and selecting Manage 
    2. Click on Hide in tile view
    3. Click Apply
    4. Go back to the LyncArchivingReport folder and repeat for the Conversation Details Report
  13. To configure the report to use your SQL Server(s), you will need to open the Search report in SQL Server Report Builder
    1. Click on the drop-down for the Search report and select Edit in Report Builder
    2. If this is the first time you've used Report Builder on this computer, select Run when the Do you want to run this application? prompt appears
  14. In SQL Server Report Builder, in the Report Data section, expand Parameters and double-click on SqlConnectionString
  15. In the Report Parameter Properties window, click on Available Values
  16. Click on Specify values
  17. Click on Add
  18. Enter your Lync Server Archiving Server SQL Server information in boxes in the following format:
    1. Label: SQL Server Friendly Name
    2. Value: Data Source=<SQL Server FQDN\Instance>;Failover Partner=<SQL Server FQDN\Instance>;Initial Catalog=LcsLog
      1. i.e. Data Source=LAB-SQL1\Lync;Failover Partner=LAB-SQL2\Lync;Initial Catalog=LcsLog
      2. Note: If you're not using SQL Mirroring for the LcsLog database, then you don't need to include the Failover Partner piece.
  19. Repeat Steps 17 and 18 for all Lync Server archiving databases in your environment
  20. (Optional) - If you only have one archiving database, you can set the default value so that you don't need to select the SQL Server every time you use the report
    1. Click on Default Values
    2. Click on Specify values
    3. Click on Add
    4. In the drop down, select your SQL Server
    5. Click on General
    6. Select Hidden under Select parameter visibility
  21. Click OK
  22. Click Save in the Quick Access Toolbar to save the changes to the reporting server
  23. Close SQL Server Report Builder
  24. Repeat Steps 13 through 23 for the Dashboard report.

If there are users that will be running the Dashboard report that don't have dbo rights to the LcsLog database, they may get the following error:

An error has occurred during report processing. (rsProcessingAborted)
 Query execution failed for dataset 'DatabaseVersion'. (rsErrorExecutingCommand)
  The EXECUTE permission was denied on the object 'DbGetVersion2', database 'LcsLog', schema 'dbo'.

This is because they don't have access to run the DbGetVersion2 stored procedure.  You will need to perform the following additional steps to grant access:

In SQL Server Management Studio, add the user or group that contains the users you want to be able to run the Dashboard report in the Security > Users folder under the LcsLog database.  Right-click on the user and select Properties.  Make sure that the Securables page is displayed:

Click on the Search button:

Make sure that Specific objects is selected and click OK.  Click on the Object Types button:

Select Stored procedures and click OK.  Click on the Browse button:

Select [dbo].DbGetVersion2] and click OK:

Click OK:

Select the Grant check box for the Execute permission.  Click OK.

The reports are now ready to be used.

 

Using the Reports

Dashboard


Note: You may need to click on the image above in order to read the text.

The Dashboard report shows you an overview about information contained in the archiving database.  You can see information about the SQL Server that hosts the archiving database, as well as, information about the archiving database itself.  The number of instant messages and conferences are shown for the time period selected as well as top users for instant messages and conferences.

 

Search


Note: You may need to click on the image above in order to read the text.

The Search report is the main report that you will use.  As it's name implies, this is the report that you will use to generate your queries against the archiving database.  The report requires a couple pieces of data, namely the SIP addresses of the user's that you want to search against and the date range of the search.  If you want to search every user or if you only want to search for any communications to/from a single user, use the NULL option.  This essentially means any user.  You can also pick the SQL Server you want to run the query against.

The results are broken up into two sections, Instant Messages and Conferences.  Clicking on the link will drill down into more information for that conversation.

 

Conversation Details Report


Note: You may need to click on the image above in order to read the text.

Drilling down into an instant message conversation will display something similar to above.  You can see when and who sent the initial message, as well as the client versions of the users, and a transcript of the conversation.

 

Conference Details Report


Note: You may need to click on the image above in order to read the text.

Drilling down into a conference shows you a lot of information gathered by the Archiving Server role.  You can expand each section to take a look at the data that was captured.

 

Please leave any questions/comments/feedback in the comments section below.

 

Click here to download the latest version of the report.

 

Changelog

November 2013 Release

Search - 1.2

  • Increased width of "Data from" textbox
  • Changed End Date range to -1 day from the current date
  • Added support for SQL mirroring
  • Other formatting changes

Conversation Details Report.rdl - 1.1

  • Increased width of "Data from" textbox
  • Fixed conversations missing from federated contacts

Conference Details Report - 1.1

  • Increased width of "Data from" textbox
  • Fixed column formatting issues - changed to a landscape layout
  • Added Expand All/Collapse All toggle
  • Added additional sorting options
  • Other formatting changes

Dashboard - 1.0

  • Initial Release

 

Known Issues

  1. Meeting Data Location for Lync Server 2010 conferences is not a valid link.
  2. All times are displayed in UTC.  Trying to convert the times to local time zones automatically isn't possible.
  3. IM conversations that contain Unicode characters may not be displayed correctly.  SSRS doesn't natively have a way to parse RTF text.  If the conversation is stored in the archiving database in RTF, some non-Latin characters may be stored as Unicode.  This means that the report will not display those IM messages correctly.

 

 

Comments
  • Found a way around the missing federated conversations. In Report Builder in Conversation Details Report for the SessionDetails Query. Change the Client version join to a left outer join.

    Change the part below

    JOIN ClientVersions v1

    ON d.User1ClientVerId =  v1.VersionId

    JOIN ClientVersions v2

    ON d.User2ClientVerId = v2.VersionId

    To look like this, leaving the rest of the query as it is.

    LEFT OUTER JOIN ClientVersions v1

    ON d.User1ClientVerId =  v1.VersionId

    LEFT OUTER JOIN ClientVersions v2

    ON d.User2ClientVerId = v2.VersionId

    Hope that helps.

  • @Jim

    Thanks for the feedback!  I'll test it out and add it to the report.

  • Thank you Doug

    As we lync professionals know, this functionality to surf trough archiving logs is something that is requested a lot by our customers

    And i would like to salute you for taking the lead and providing this tool for the community!

  • @Darwin

    Thanks for the feedback!  I'm glad that people find the report useful!

  • Hello,

    The reports works like a charm but I live in a different time zone (GMT +4), would there be a way I could amend the actual UTC on the search report? If yes how

  • @Thierry

    That is something that we're looking at trying to allow, but with DST, it's a little more complicated than it seems, so I want to make sure that we implement something that works and is useful.  Thanks for the feedback!

  • @dodeitte

    Good Day, thank you very much for reports!!!!

    In one of the first posts Michael was asking about national (cyrillic simbols), have you some solution?

    when i am watching report of conversation i see something like this (instead of message):

     ff ede5 efeee4e4e0e2e0ebe0f1fc)))

    If i am looking in SQL LcsLog.Messages i see:

    {\rtf1\fbidis\ansi\ansicpg1251\deff0\nouicompat\deflang1049{\fonttbl{\f0\fnil\fcharset204 Segoe UI;}{\f1\fnil Segoe UI;}}

    {\colortbl ;\red0\green0\blue0;}

    {\*\generator Riched20 15.0.4481}{\*\mmathPr\mwrapIndent1440 }\viewkind4\uc1

    \pard\cf1\embo\f0\fs18\'ff\embo0  \embo\'ed\'e5\embo0  \embo\'ef\'ee\'e4\'e4\'e0\'e2\'e0\'eb\'e0\'f1\'fc)))\embo0\f1\par

    {\*\lyncflags<rtf=1>}}

    Is there some way to convert it in readable russian symbols?

  • Good work, gotta try in the LAB to explore it...

  • @Moto

    Currently the report handles RTF text by using a RegEx to strip all the formatting tags and just display the text.  However, in your case, and some others, the text is encoded in Unicode and just stripping the tags won't work.  SSRS currently has pretty limited support for handling RTF text, so I'm still working on options.

  • Great Report Doug, Making some customers very happy!

    One question regarding configuring the report with a Lync SQL Mirror.  The 2 instances running our SQL Mirror each have SSRS installed. I followed your instructions from the other article regarding setting the Failover Partner in SSRS for the Monitoring Report Pack (which works fine with  the mirror).  

    I followed the steps you have on the Primary Instance for the Archiving reports and they worked as well.  I added the reports to the mirror instance and wasn't sure what to put in for the servername .  I tried the name of the mirror then the name of the primary and neither worked.  For now, we are just going to plan on running the report off the Primary when the Primary is the principal partition in the pair but it would be nice to get the report to run when we are on the mirror.

    Thanks,

    --John

  • @QL

    Thanks for the feedback!  I've added it to the list of enhancements.  I hope to have something ready in the next release of the reports.

  • @QL

    Please try the latest version of the reports.  It should now handle SQL mirroring correctly.  Make sure to go through the setup instructions again, as some things have changed.

  • Hi everyone

    We've deployed Lync Reports on SQL Server 2012 Std (11.0.3000.0) w/Reporting Services

    The Search report works fine. We can see the data in the "Conversation Search Results" section.

    But when we try to see the details of conversations, the following error appears:

    "An error has occurred during report processing. (rsProcessingAborted)

    Cannot read the next data row for the dataset SessionMessages. (rsErrorReadingNextDataRow)

    The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value"

    Any ideas to solve this issue?

    Thanks in advance

  • I can confirm it's working fine on 2008 R2 Standard Edition (64-bit) 10.50.4000.0 SP2. Thank you for a great job!

  • First off, great job on this...very nice!

    I was able to get the Dashboard and Search report working but when trying to view Conversation Details I'm getting;

    The 'SessionIDTime' parameter is missing a value

    Any ideas? Thanks!

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment