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.
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
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:
Select the Grant check box for the Execute permission. Click OK.
The reports are now ready to be used.
Using the Reports
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.
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
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
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.
November 2013 Release
Search - 1.2
Conversation Details Report.rdl - 1.1
Conference Details Report - 1.1
Dashboard - 1.0
I can not get the full report of messages.
Search report provides a list of sessions. Clicking the link session return blank report with header and footer and w/o messages or errors.
P.S. I checked the SessionIdTime transmitted to the report conversation . What's the problem?
In the continuation of the post above.
The report has earned only after the new variable was added to sql query, and it was set to SessionIdTime, converted into a Date format.
It seems that the report gave an empty request that the parameter SessionIdTime had an incorrect format.
Some messages are saved in RTF? format and thus unreadable in text field. The problem is known. Is there a trick to get around the problem?
Yes, some of the IMs are stored in the database in RTF. When you say they are unreadable in the text field, what are you seeing? The report uses some regex expressions to strip out the RTF tags and just leaves the text. In fact the screenshot above of the Conversation Details Report has messages in RTF and you can see that it is displayed correctly. Is the report displaying something different for you? If so, what version of Lync are you using, as well as what version of the clients are being used in the conversation that isn't displaying correctly?
Thanks for the answer!
Yes, regexp cuts special characters, but national symbols in the database are stored in encrypted form (if "lyncflags rtf=1") and are not readable and look like "ebe8e3eaee"..((
Is there a solution?
Great report, makes it easy for customers to find what they need in their archiving database. One query, how do I change the time format from UTC to local time? I'm in Australia (+10 Melbourne), have looked at the report builder but (as someone that does not customise SQL Reports regularly) am unable to figure out the correct syntax. Could you help?
Thanks for the feedback! Trying to localize the time in the reports is something that's on my list to try and fix.
thx for the nice report
for example - www.picamatic.com/.../9400364_704x334.jpg
first line and last line is good, but others ... ;(
lync 2013 cu1, sql 2012, russian letters.
Hi, question. If you are archiving to Exchange 2013, will you still get results from the search?
No. Archiving to Exchange 2013 will store all archiving information in that user's mailbox and not the LcsLog database.
In SQL Reporting Services 2008 R2 does not work?
I didn't test it with SSRS 2008 R2. It may very well work just fine, but since I didn't test it, I can't say for sure that it will work without issue.
I test in SQL Reporting Services 2008 R2, but I am unable to upload the report file, it is showing the below error:
The report definition is not valid. Details: The report definition
has an invalid target namespace
which cannot be upgraded. (rsInvalidReportDefinition) Get Online Help
Thanks for testing. The report must use some features of SSRS 2012 that aren't available in SSRS 2008 R2.
Great set of reports Doug.
I installed on RS2008R2 with one minor issue, conversations that included a federated contact had no data at all in conversation history. Looked in to it and found that the client version stored in the database for the federated version was NULL. Taking the Join on the client version in the conversation history report allows all to be visible with the exception of the version of course.