Sample Lync Server Archiving Report Available

Article
06/02/2013

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

Download the latest version of the Sample Lync Server Archiving Report
Extract the zip file
Open Report Manager – http(s)://<SRS Server>/Reports
Click on New Folder
Give the folder a name – i.e. LyncArchivingReport
Click OK
Click on the folder you just created
Click on Upload File
Browse to the location where you extracted the LyncArchivingReport zip file and select Search.rdl
Click OK to upload the report
Repeat Steps 9 and 10 for the rest of the reports
(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
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
In SQL Server Report Builder, in the Report Data section, expand Parameters and double-click on SqlConnectionString
In the Report Parameter Properties window, click on Available Values
Click on Specify values
Click on Add
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.
Repeat Steps 17 and 18 for all Lync Server archiving databases in your environment
(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
Click OK
Click Save in the Quick Access Toolbar to save the changes to the reporting server
Close SQL Server Report Builder
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

Meeting Data Location for Lync Server 2010 conferences is not a valid link.
All times are displayed in UTC. Trying to convert the times to local time zones automatically isn't possible.
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

Anonymous
January 01, 2003
Hi Doug. Just a quick comment. First of all, thank you for this. It is really useful and will now probably be part of my standard install, assuming the client wants archiving.The other thing is that the instructions could do with a small update. It took me a little time to get it all working because of a couple of missing steps. Step 12 is the optional step to hide the Conversation and Conference details reports. One thing I found is that I needed to add the SQL connection to these as well. Might seem obvious, but I'm not a DBA. So in Step 12 I hid these, then I moved on to steps 13 to 23 and added a SQL connection string to Search. Then step 24 to repeat for Dashboard. There was no mention to repeat for Conversation and Conference details. It was only when I drilled into a conversation and got an error relating to SQL connection that I decided to do the same for them as well.Obviously I got there in the end and know for next time. Perhaps you can change 24 to repeat for all reports and move step 12 to after that.Once again, thank you very much.Regards
Anonymous
January 01, 2003
Hi,I am getting running the dashboard. It tells me that it cannot find DbGetVersion2. I looked in SQL Management studio and it isn't there. How can I add that?
Anonymous
January 01, 2003
@JoshI can understand why you would get that if you're trying to go directly to the Conversation Details or Conference Details reports. Both of those require additional parameters to be supplied and you would get that error if you just clicked on those reports directly. The entry point to those reports is via the Search report. Are you using the latest version of all of the reports?
Anonymous
January 01, 2003
@Error running Conversation Details Report

You can't run the Conversation Details Report directly. You need to run the Search report and the links in that report will take you to the Conversation Details Report.
Anonymous
January 01, 2003
@Walter F. Someone else reported that error, but I'm not sure what's causing it.
Anonymous
January 01, 2003
@Fabio Souza Thanks for testing. The report must use some features of SSRS 2012 that aren't available in SSRS 2008 R2.
Anonymous
January 01, 2003
The comment has been removed
Anonymous
January 01, 2003
@IJWRIGHTYThat works. You can also just edit the SQL query in the count_TopUsers dataset in the Dashboard.rdl file.
Anonymous
January 01, 2003
@Chirag

Unfortunately the LcsLog database doesn't contain information on P2P file transfers.
Anonymous
January 01, 2003
The comment has been removed
Anonymous
January 01, 2003
@AnonymousThanks for the feedback! You shouldn't need to edit the Conversation or Conference Details Reports. The Search report will pass the SQL connection string to those reports.
Anonymous
January 01, 2003
@Chip Wolt

If you are trying to access those reports directly, you cannot. Those reports are called by the Search.rdl and passed the correct parameters.
Anonymous
January 01, 2003
Hi Dodeitte, which would be the parameter to access direclty to the Conference Details Report? I just instaled the reports and are working fine, but my clients will ask about it, thanks in advanced!
Anonymous
January 01, 2003
@Javier Mariani ReyesYou might want to check the MessageIdTime column in the Messages table and make sure that all of the data is consistent. The values should be in the following format:2013-10-26 16:54:26.430
Anonymous
January 01, 2003
@Darwin Thanks for the feedback! I'm glad that people find the report useful!
Anonymous
January 01, 2003
@JoshNot sure why you're getting that error. Is it happening for every conversation?
Anonymous
January 01, 2003
@Damien Thanks for the feedback! Trying to localize the time in the reports is something that's on my list to try and fix.
Anonymous
January 01, 2003
@Trent Is it just the dashboard that isn't working? Do the other reports show data?
Anonymous
January 01, 2003
It is from Lync 2010.
Anonymous
January 01, 2003
@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!
Anonymous
January 01, 2003
The comment has been removed
Anonymous
January 01, 2003
@Michael

I'm glad you found this report useful. As far as where to install reporting services, you can install it into the instance you're going to be using for the archiving database or you can install it into another instance or even the default instance. Most of the time I see it installed into the instance that's holding the archiving database.
Anonymous
January 01, 2003
@AGGHHH

If the LcsLog database is in the default instance on your SQL server, you don't need to specify an instance name in step 18 above. If that doesn't resolve the issue, if SSRS and the LcsLog database are on the same server, you can also try specifying the SQL Server FQDN as (local). Also have you tried connecting to SQL using SQL Studio Manager from a machine other than the SQL Server? This will tell you whether or not the SQL server is enabled for remote connections.
Anonymous
January 01, 2003
@JC No. Archiving to Exchange 2013 will store all archiving information in that user's mailbox and not the LcsLog database.
Anonymous
January 01, 2003
Thank you! This is wonderful and resolves so many problems I was facing.

Worked on Windows Server 2008R2 with Lync 2013 Standard, full SQL installed solely for archiving purposes as default instance on same server.
Anonymous
January 01, 2003
@AnonymousDid you follow the instructions above to grant access to the DbGetVersion2 stored procedure for the account running the report?
Anonymous
January 01, 2003
The comment has been removed
Anonymous
January 01, 2003
@AnonymousYou can't access those reports directly. That is why step 12 is in the instructions above...so that people don't accidentally try to click on those reports.
Anonymous
January 01, 2003
@Mr MeAre you using an account that has rights to read the data in the LcsLog database?
Anonymous
January 01, 2003
@Jamie

The date and time are stored in the LcsLog database in UTC. You would need to edit the reports to add the correct offset.
Anonymous
January 01, 2003
Hello. The old version of OCS/Lync reports showed all of the IMs including the actual messages on one page. This was actually useful when reports of IM conversation were requested for legal investigations or HR. That view allowed to export all IMs at once and provide the report to requesting party. Is there a way to do the same with current version of archiving report? if not, do you have any suggestions on how to change it to receive this report?
Anonymous
January 01, 2003
Hi @dodeitte ! Well thanks a lot for this article and the solution for consult achieving history. Well i see in old comments the correction about the time (UTC), i want know have some option to fix the difference of the time?
Anonymous
January 01, 2003
@Walter F. You'll need to edit the expression in any of the reports that show date/time. As an example, in the Search.rdl, the column Time (UTC) contains date/time. You'll need to edit that expression. If the server has the same time zone that you're looking for, you can change the expression to: =TimeZone.CurrentTimeZone.ToLocalTime(Fields!Time__UTC_.Value)
Anonymous
January 01, 2003
Yeah, it's happening for every conversation...the 'Conversation Details' report doesn't work at all. If I use the 'Search' one I can see that there are conversations but once I click on one I get the 'SessionIDTime' error?
Anonymous
January 01, 2003
The comment has been removed
Anonymous
January 01, 2003
@Leonardo Did you verify that the SQL FQDN and instance match what you've defined in Topology Builder for the LcsLog database?
Anonymous
January 01, 2003
@Phil NayebiIs your LcsLog database from Lync Server 2010 or Lync Server 2013?
Anonymous
January 01, 2003
@Fabio Souza 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.
Anonymous
January 01, 2003
@Jim Thanks for the feedback! I'll test it out and add it to the report.
Anonymous
January 01, 2003
@dodeitte The LcsLog database is from Lync Server 2010
Anonymous
January 01, 2003
@Walter F. If you want to convert the time from UTC, you'll need to edit the reports in Report Builder and manipulate the columns that have date/time to apply the offset you want.
Anonymous
January 01, 2003
The comment has been removed
Anonymous
January 01, 2003
@Phil Nayebi and @PlusGeekThe issue with the Dashboard report is that it calls a stored procedure that doesn't exist in the Lync Server 2010 LcsLog database. You can edit the Dashboard report and remove the Lync Database Information section and that should resolve the issue. I'm trying to find a way to keep that information and make the report work for both Lync Server 2010 and 2013.
Anonymous
January 01, 2003
To get around the UTC issue, I created another column and Created a Calculated Field to convert to estern
=DATEADD(DateInterval.Hour,-5,Fields!Time__UTC_.Value)
Anonymous
January 01, 2003
The 'The 'SqlConnectionString' parameter is missing a value ' when clicking on link from the Search report is due to overzealous configuration. you do not need to modify the Detail Reports.

In order to correct this error, delete the Search and Detail reports, then re-upload them from the ZIP file, and finally configure the SQL connection string on ONLY the Search reports. DO NOT MODIFY THE DETAIL REPORTS
Anonymous
June 04, 2013
Hi! 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? Thanks.
Anonymous
June 05, 2013
Hi! 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. Thanks.
Anonymous
June 05, 2013
Hi! 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? Thanks!
Anonymous
June 10, 2013
The comment has been removed
Anonymous
June 26, 2013
Hi Doug, 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! Damien
Anonymous
July 07, 2013
Hi, question. If you are archiving to Exchange 2013, will you still get results from the search? Thank you.
Anonymous
July 23, 2013
Hi, In SQL Reporting Services 2008 R2 does not work?
Anonymous
July 23, 2013
The comment has been removed
Anonymous
August 01, 2013
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.
Anonymous
August 01, 2013
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.
Anonymous
August 14, 2013
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!
Anonymous
September 30, 2013
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
Anonymous
October 09, 2013
The comment has been removed
Anonymous
October 14, 2013
Good work, gotta try in the LAB to explore it...
Anonymous
November 06, 2013
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
Anonymous
November 11, 2013
@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.
Anonymous
November 26, 2013
@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.
Anonymous
November 27, 2013
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
Anonymous
December 05, 2013
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!
Anonymous
December 10, 2013
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!
Anonymous
December 13, 2013
Is there a way of showing more than the top 5 for number of users? Great report BTW :-)
Anonymous
December 16, 2013
Managed to create a separate SQL report with the following query to produce a table:select UserUri , totals from (select t2.UserUri , COUNT(*) as totalsfrom LcsLog.dbo.Messages t1 inner join lcslog.dbo.Users t2 on t1.FromId = t2.UserId where convert(date,MessageIdTime,103) between convert(date,@StartDate,103) and convert(date,@EndDate,103) group by t2.UserUri) t1 order by totals desc
Anonymous
December 18, 2013
HelloThese reports look great, had the old ones working in 2010 now I am on 2013 and get the errorLogin Failed for user 'NT AUTHORITYANONYMOUS LOGIN'Anyone kind enough to think why I'd get this?thanks!
Anonymous
January 15, 2014
I was recently asked by a customer how they can provide different groups or departments read-only access
Anonymous
January 27, 2014
Hello. Configure step by step, to the report, but it gives the following error:Error al procesar el informe. (rsProcessingAborted)No se puede crear una conexión al origen de datos 'SQLServer'. (rsErrorOpeningConnection)A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)How can I fix this error?
Anonymous
January 30, 2014
It is showing 'No Data Available' - in the dashboard, but the SQL server info / database info and lync db info are all showing the correct information, any idea what I've missed?
Anonymous
January 31, 2014
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? I Have the same issue! Anyway, nice job on this one!
Anonymous
January 31, 2014
All times are displayed in UTC. Trying to convert the times to local time zones automatically isn't possible. Just read that... where can i change it manually?
Anonymous
February 05, 2014
@dodeitte thanks a lot for the reply! :)
Anonymous
February 05, 2014
@dodeitte, do you know where should i convert time? And for what data? "totall noob here", i saw here like Message_Time__UTC_ my time zone here is UTC/GMT -3 hours ?!
Anonymous
February 21, 2014
this is an awesome report tool that I have used in my lync 2010 environment since I first found it years ago. I have a question..... in my lync 2010 all my databases were in the default (non-named instance) because I was a newbie and never installed named instances. in my new lync 2013 I created two named instances and do not have a default un-named instance at all. I also did not install reporting services yet either. so..... my question is where do I install reporting services to?? a default un-named instance or do install it into one of the named instances (I would assume the instance that my lync archive is going to)??
Anonymous
March 05, 2014
The comment has been removed
Anonymous
March 05, 2014
Is there any way to get a report similar to the old Archiving Report for OCS. Specifically, so I can have a report that would list the actual content of the message for all users for a particular time frame instead of having to manually click on the date/time field in order to get the message contents.
Anonymous
March 06, 2014
При открытия отчета через MS SQL - Отчеты - Пользовательские отчеты, пишет ошибку
Error "Недопустимое имя объекта "Conferences"
Anonymous
March 06, 2014
Error - Conference Details Report:
An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the dataset MCUsused. (rsErrorReadingNextDataRow)
Error converting date or time from character string.
Anonymous
March 06, 2014
Error - Conference Details Report:
An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the dataset MCUsused. (rsErrorReadingNextDataRow)
Error converting date or time from character string.
Anonymous
March 11, 2014
When I try running Conversation Details Report, I get a parameter error: "SessionIDTime is missing a value"

What do I set this parameter as? I do not see it listed anywhere. The other reports run great!
Anonymous
March 11, 2014
^ - sorry. I read the answer on a different page. It is accessed from the search report.
Anonymous
March 13, 2014
after you push the date of the chat, there is an error:
Error:
The value provided for the report parameter 'SessionIdSeq' is not valid for its type. (rsReportParameterTypeMismatch)
Anonymous
March 17, 2014
Hi, I'm not able to drop down as in step 13 A. It is not showing any result.
Regards, Chetan
Anonymous
March 21, 2014
Hello I changed search to =TimeZone.CurrentTimeZone.ToLocalTime(Fields!Time__UTC_.Value) but now the date time field is not selectable I am unable to click and enter the convo
Anonymous
March 21, 2014
Never mind apparently I needed to check the actions tab. after I saved it started working
Anonymous
March 24, 2014
I really appreciate this post.

However, when I run 'Conversation Details Report' it throws an error, The 'SessionIdTime' parameter is missing a value.

I checked in the properties and found that there is no value set to SessionIdTime and also for SessionidSeq. What values should I put in?

Thanks in advance.
Anonymous
March 26, 2014
I get "The 'SessionIdTime' parameter is missing a value " error when I run either 'Conference Details Report' or 'Conversation Details Report'

Thank you in advance for your help
Anonymous
March 26, 2014
Thanks Dodeitte... Just checked your answer... Super work. Really appreciate it.

Wondering if there any way I can see details of peer to peer file transfer in any of the database such as LCSlog? I know archiving of peer to peer file transfer is not supported but it was also not supported in OCS 2007 R2 but still the database displayed the details of peer to peer file transfer in the dbo.messaging table in LCSlog database.

I can view the multiparty file transfer details as it is recorded as MCU, but wondering if you have seen details of peer to peer file transfer in any other table of any other database.

Thank you once again...
Anonymous
April 03, 2014
Hi!

Thanks a lot!

But I can`t get the full report of messages.

Search report provides a list of sessions. Clicking the link session return blank report w/o messages or errors.
Anonymous
April 09, 2014
Hi,

never underestimate the power of starting form the beginning of a process to overcome a problem. Walked through the steps again and found I was referencing the CDR and not the Log DB, DOH! I can confirm it works perfectly on SQL 2012 for Lync 2013, on 2012 R2.

Thank you sir, GREAT reporting tool.

Many Thanks

Chris
Anonymous
April 16, 2014
THANKS!! This is a great start. In my institution, there are cases where they want all conversation details from the user in 1 report. If I missing where this can occur I apologize.
Anonymous
May 05, 2014
I've been through this a dozen times, even being sure that I'm getting the database name correct - no dice. I'm having the same problem that Leonardo I believe was having, if I attempt to run the report from the SAME machine, I'm getting the same error from what I can tell. I'm at a loss. I've tried every possible way to type "default" including caps and not, MSSQLSERVER and lower case, nothing works. I ALWAYS get this error when I attempt to run the dashboard or the search after editing it according to the instructions:

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'SQLServer'. (rsErrorOpeningConnection)
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)
Anonymous
May 07, 2014
Thanks for all your hard work, this is great and makes data mining Lync much easier. Seems to be working fine. I just have the same questions / issues as others, such as how to change UTC time to EST (Gmt -5). Also is there a way to report on WHO is accessing this? Just need to keep watchful eye on rogue admins abusing their time reading other peoples conversations. Right now Domain Admins have access to this database but we have 5 domain admins, so we will want to audit access.
Thank you!
- Anonymous
  March 05, 2017
  Hİ kjstech I m live in Turkey(UTC+3). Did you find out how to change utc. Can you help me If you know how to fix it.
Anonymous
May 08, 2014
Yes, I've tried all of that - but it was late and I could've made mistakes. I'll give it another whirl right now and see what happens..
Anonymous
May 08, 2014
Ok, I remember what was going on now - it "works" but every time I use the dashboard, or the search function, the message is the same where there should be data. "No instant messages found for the given date range" even when I specify some time earlier this week, and when I attempt to use the dashboard, it returns the data about the Lcslog (1152 MB) but the quick stats all say 0.
Anonymous
May 29, 2014
Thanks for this post...works a treat :)
Anonymous
June 03, 2014
Seems the archiving was set up, but the option for archiving IM and meeting session details was not enabled. Definitely works great, thanks!
Anonymous
June 10, 2014
When attempting to run Search, I get - The item '/LyncReporting/Conversation Details Report' cannot be found. (rsItemNotFound)

Any ideas? Thanks!
Anonymous
June 10, 2014
Actually, I get that when clicking on one of the Time (UTC) results.
Anonymous
June 20, 2014
I am getting the following when trying to view conversation detail from the Search form, click on the convo and get: The 'SqlConnectionString' parameter is missing a value

Also if I open Conversation Detail Report directly, I get:
The 'SessionIdTime' parameter is missing a value
Anonymous
August 08, 2014
Hi,
I have just installed Lync Server 2013 enterprise edition.
My SQL Server 2012 are in the different server and SQL Server collation Latin1_General_CP1_CI_AS.
When i want to see archived message, message Character is wrong and absurd.

Why it is occurs?

when i check the Lcslog database collation that is Latin1_General_BIN . they are different and that is important?

What can i do now for correct?

Thank you.
Anonymous
August 26, 2014
dodeitte you did an excellent job with this report, it worked perfectly for me, I owe a lot of the success of the install to the SQL guys I work with as they helped me tweak it a bit to get it to work how I wanted it to work.

I have one problem that I am hoping you can fix for me when I click on the user that did a conference call the webpage displays all the information but I have to scroll across to view the whole page. For some reason it isn't displaying like how the IM conversation reports displays.

I have screenshots and would like to know how I can get them across to you.
Anonymous
September 11, 2014
@ Dodeitte I am getting the following when trying to view conversation detail report from the Search form, click on the convo and get: The 'SqlConnectionString' parameter is missing a value. Any ideas or suggestions?
Anonymous
September 24, 2014
Is it alright to use SSRS from SQL Server 2012 Express Edition with advanced services?
Is there any problem if we use SSRS 2012 with the database on a SQL Server 2008 Database Engine?
Anonymous
September 30, 2014
The date and time is wrong on the report. How do I fix that?
Anonymous
November 17, 2014
I am getting the following error for "Conference Details Report.rdl" and "Conversation Details Report.rdl":
"The report is missing a parameter value but prompting for it has been disabled"

"Dashboard.rdl" and "Search.rdl" has same configuration and works fine.

Could you advise, please?
Anonymous
November 18, 2014
@dodeitte

That makes sense. Thank you for quick reply.
Anonymous
November 24, 2014
Hey, it seems as though when I check the "Top 5 IM Users" it picks the top 5 alphabetically? So it's only showing top 5 users with SIP uri starting with the letter A.
Anonymous
December 08, 2014
The comment has been removed
Anonymous
January 21, 2015
Hi,
I already installed sample lync server archving sucessful. But I only search with null option, cannot search with speacify SIP name and the conntent show error with unicode font. thanks.
Anonymous
February 16, 2015
I am getting 'The 'SqlConnectionString' parameter is missing a value ' when trying to click on IM contents, my data string are set as per the below 'data source=server.domain;initial catalog=LcsLog' Search and Dashboard reports work fine. Any ideas?
Anonymous
February 24, 2015
I saw in the comments about 5 people requesting how to do the same thing I want to do, which is to show the full conversation details in the Search report, without having to click on each conversation. Nice report, but very tedious if you need to see the details. Any way to do this easily? I've not used SSRS much at all so hopefully I don't have to try to combine these reports myself somehow...
Anonymous
February 26, 2015
The comment has been removed
Anonymous
May 27, 2015
These reports has been tested using Skype for Business Server 2015 Standard? Do you know if they should supposed to work?
Anonymous
June 01, 2015
The comment has been removed
Anonymous
June 01, 2015
The comment has been removed
Anonymous
June 02, 2015
@Dodeitte

I'm using Lync 2010, Windows 2008 R2 and SQL 2008 R2.
I've edited Dashboard in SQL Report Builder,

1. Expand Datasets.
2. Right click DatabaseVersion
3. Click "Dataset Properties" then
4. Change "DbGetVersion2" to "DbGetVersion".

Dashboard now working but is it does not appear any value after "Archiving Database Version: ", it's just a blank.

Thanks,

kikigak
Anonymous
June 10, 2015
hi
my report is giving me this error :

An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the dataset DataSet6. (rsErrorReadingNextDataRow)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

im using SharePoint 2010
Anonymous
July 08, 2015
@Dodeitte, I'm using Lync 2013, Windows 2012 and SQL 2012
Search report provides a list of sessions. Clicking the link session return blank report with header and footer and w/o messages or errors.
What's the problem?
Anonymous
July 20, 2015
Hi all,
The 'SessionIdTime' and 'SessionIdSeq' parameter is missing a value in conversation and conference reports but dashboard and search reports work fine
how to resolve this problem?
thanks in advance
Anonymous
August 07, 2015
Has this been updated for Skype for Business or will it work as is???
Anonymous
August 19, 2015
@Michael

It hasn't been updated for Skype for Business, but it should work as is.
Anonymous
September 22, 2015
Nice report and clear instructions-just what I needed. Thanks!
Anonymous
September 24, 2015
The comment has been removed
Anonymous
September 24, 2015
Agradee7o = Agradeço
Anonymous
October 29, 2015
Doug...I recently did an inplace upgrade to S4B from Lync 2013. The search and dashboard databases run without any issue. Unfortunately the conference details and conversation details reports do not run due to the following error: The 'SessionIdTime' parameter is missing a value. Thanks for your help.
Anonymous
December 14, 2015
Will you have one for Skype for Business Server 2015?
Anonymous
May 14, 2016
Session I'd time parameter is missing a value, can somebody help me to fix this please. I'm really struggling to get this resolved
Anonymous
May 16, 2016
I also updated the chat history report date fields for EST. Using =DATEADD(DateInterval.Hour,-5,Fields!) Different sections of the report call the Time Field differently so you will have to change the function for it. Example: First(DATEADD(DateInterval.Hour,-4,Fields!Invite_Time.Value), "SessionDetails")
Anonymous
June 06, 2016
Very GOOD!
Anonymous
June 20, 2016
Will these work with Skype for Business 2015. I have set them with DBA, updated the graphics of S4B, but not pulling any data.Running on SRS 2014
- Anonymous
  June 26, 2016
  I haven't tested them against SSRS 2014 and Skype for Business Server 2015. The archiving database hasn't changed that much between versions, so even if there are changes that are needed, they should be pretty minimal.
Anonymous
July 14, 2016
The comment has been removed
Anonymous
September 28, 2016
Hi, I would like to know how export the search results along with conversation details? The export button when used only exporting date, from and to in to the export file. Thank you
Anonymous
October 25, 2016
The comment has been removed
- Anonymous
  October 25, 2016
  That ability isn't in the current report, but you can edit the reports and change the way the results are displayed to accomplish what you're looking for.
Anonymous
February 16, 2017
I'm having an error opening the conversation:"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"Can you help me?Thank's!
- Anonymous
  February 16, 2017
  Solved.I solved this by converting the datetime according to SQL Server query.In my case: replace (@SessionIdTime, '-', '')Thank you!
Anonymous
March 04, 2017
Hi. I live in Istanbul/Turkey and My time zone is UTC+3. I have to read hundreds of correspondence every day for my job and I need to share screenshots of some correspondence. But I am having problems because the correspondence time is 3 hours before I need it. How can i fixed it.
Anonymous
November 30, 2017
The comment has been removed
Anonymous
January 03, 2018
Great report! Im having an issue everytime i do a search, its only show the Messages from today?
Anonymous
January 09, 2018
HiI have successfully deployed dodeitte according to your instructions. If there is no problem with the English information, the Chinese message will be scrambled. Could you please tell me how to change the code? Thank you?
- Anonymous
  January 09, 2018
  hi Can you use this method in skype for business?
  - Anonymous
    January 10, 2018
    The comment has been removed

Share via

Sample Lync Server Archiving Report Available

Comments

Additional resources