Sample Lync Server Archiving Report Available


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 (135)

  1. Anonymous says:

    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

  2. Anonymous says:

    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?

  3. dodeitte says:

    @Josh

    I 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?

  4. dodeitte says:

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

  5. dodeitte says:

    @Walter F. Someone else reported that error, but I’m not sure what’s causing it.

  6. Anonymous says:

    @Fabio Souza

    Thanks for testing.  The report must use some features of SSRS 2012 that aren't available in SSRS 2008 R2.

  7. PlusGeek says:

    @dodeitte I tested the reports for Lync Server 2013 successfully on one deployment, but on the other with Lync Server 2010 and SQL Server 2008 SP3 deployed, I got an error regarding the definition of the report. The error was fixed by changing all reports sources according to this article: http://beatheadagainstwall.blogspot.com/2011/03/invalid-target-namespace-when-deploying.html The problem is that these changes did not work for Dashboard.rdl (Search report is working fine!) Could you please help?

  8. dodeitte says:

    @IJWRIGHTY

    That works. You can also just edit the SQL query in the count_TopUsers dataset in the Dashboard.rdl file.

  9. dodeitte says:

    @Chirag

    Unfortunately the LcsLog database doesn’t contain information on P2P file transfers.

  10. Anonymous says:

    hello,

    thx for the nice report

    for example – http://www.picamatic.com/…/9400364_704x334.jpg

    first line and last line is good, but others … ;(

    lync 2013 cu1, sql 2012, russian letters.

  11. dodeitte says:

    @Anonymous

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

  12. dodeitte says:

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

  13. Anonymous says:

    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!

  14. dodeitte says:

    @Javier Mariani Reyes

    You 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

  15. Anonymous says:

    @Darwin

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

  16. dodeitte says:

    @Josh

    Not sure why you’re getting that error. Is it happening for every conversation?

  17. Anonymous says:

    @Damien

    Thanks for the feedback!  Trying to localize the time in the reports is something that's on my list to try and fix.

  18. dodeitte says:

    @Trent Is it just the dashboard that isn’t working? Do the other reports show data?

  19. Phil Nayebi says:

    It is from Lync 2010.

  20. Anonymous says:

    @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!

  21. Anonymous says:

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

  22. dodeitte says:

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

  23. dodeitte says:

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

  24. Anonymous says:

    @JC

    No.  Archiving to Exchange 2013 will store all archiving information in that user's mailbox and not the LcsLog database.

  25. IronVarmint says:

    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.

  26. dodeitte says:

    @Anonymous

    Did you follow the instructions above to grant access to the DbGetVersion2 stored procedure for the account running the report?

  27. Phil Nayebi says:

    Hi Dodeitte,
    I did try to follow the instructions to grant access to DbGetVersion2. However, it is not there. That stored procedure is missing from my LcsLog database. My specific error from the report is:

    An error has occurred during report processing. (rsProcessingAborted)
    Query execution failed for dataset ‘DatabaseVersion’. (rsErrorExecutingCommand)
    Could not find stored procedure ‘DbGetVersion2’.

    Thanks for the help!

  28. dodeitte says:

    @Anonymous

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

  29. dodeitte says:

    @Mr Me

    Are you using an account that has rights to read the data in the LcsLog database?

  30. dodeitte says:

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

  31. MarinaB says:

    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?

  32. Diego M Amaral says:

    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?

  33. dodeitte says:

    @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)

  34. Anonymous says:

    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?

  35. Anonymous says:

    @Michael

    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?

  36. dodeitte says:

    @Leonardo Did you verify that the SQL FQDN and instance match what you’ve defined in Topology Builder for the LcsLog database?

  37. dodeitte says:

    @Phil Nayebi

    Is your LcsLog database from Lync Server 2010 or Lync Server 2013?

  38. Anonymous says:

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

  39. Anonymous says:

    @Jim

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

  40. Phil Nayebi says:

    @dodeitte The LcsLog database is from Lync Server 2010

  41. dodeitte says:

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

  42. dodeitte says:

    @kjstech

    Report auditing would be a feature of SSRS. You might want to look into Report Execution logging in SSRS to see if that meets your needs.

  43. dodeitte says:

    @Phil Nayebi and @PlusGeek

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

  44. Anonymous says:

    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)

  45. Anonymous says:

    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

  46. Michael says:

    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.

  47. Michael says:

    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.

  48. Michael says:

    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!

  49. Michael says:

    Thanks for the answer!

    Lync 2013.

    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?

    Thanks!

  50. Damien says:

    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

  51. JC says:

    Hi, question. If you are archiving to Exchange 2013, will you still get results from the search?

    Thank you.

  52. Fabio Souza says:

    Hi,

    In SQL Reporting Services 2008 R2 does not work?

  53. Fabio Souza says:

    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

    ‘_schemas.microsoft.com/…/reportdefinition&

    which cannot be upgraded. (rsInvalidReportDefinition) Get Online Help

  54. Jim says:

    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.

  55. Jim says:

    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.

  56. Darwin says:

    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!

  57. Thierry says:

    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

  58. Moto says:

    @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:

    {rtf1fbidisansiansicpg1251deff0nouicompatdeflang1049{fonttbl{f0fnilfcharset204 Segoe UI;}{f1fnil Segoe UI;}}

    {colortbl ;red0green0blue0;}

    {*generator Riched20 15.0.4481}{*mmathPrmwrapIndent1440 }viewkind4uc1

    pardcf1embof0fs18'ffembo0  embo'ed'e5embo0  embo'ef'ee'e4'e4'e0'e2'e0'eb'e0'f1'fc)))embo0f1par

    {*lyncflags<rtf=1>}}

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

  59. Gulab says:

    Good work, gotta try in the LAB to explore it…

  60. QL says:

    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

  61. dodeitte says:

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

  62. dodeitte says:

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

  63. Javier Mariani Reyes says:

    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

  64. Dmitry says:

    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!

  65. Josh says:

    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!

  66. IJWRIGHTY says:

    Is there a way of showing more than the top 5 for number of users?

    Great report BTW 🙂

  67. IJWRIGHTY says:

    Managed to create a separate SQL report with the following query to produce a table:

    select UserUri
    , totals
    from
    (select t2.UserUri
    , COUNT(*) as totals

    from 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

  68. Mr Me says:

    Hello
    These reports look great, had the old ones working in 2010 now I am on 2013 and get the error
    Login Failed for user ‘NT AUTHORITYANONYMOUS LOGIN’
    Anyone kind enough to think why I’d get this?
    thanks!

  69. Anonymous says:

    I was recently asked by a customer how they can provide different groups or departments read-only access

  70. Leonardo says:

    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?

  71. Trent says:

    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?

  72. Walter F. says:

    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!

  73. Walter F. says:

    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?

  74. Walter F. says:

    @dodeitte thanks a lot for the reply! 🙂

  75. Walter F. says:

    @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 ?!

  76. Michael says:

    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)??

  77. Chris M says:

    Hi,

    this looks like a great set of reports, if only I could get them to work! I’m not a DBA so am struggling to get things up and running. The search works with no issues however each time I try to dig into a message or a conference I get and error, similar to the below. Also the Dashboard does not work for me at all each time I try to execute it I get:

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

    I know messages are there in the DB Tables because I can see them. I even tried entering credentials explicitly so that it used the sa account in case it was security related but no joy again. As I said I’m no DBA so am looking for some direction.

    I’m running SQL 2012 for Lync 2013, all servers are 2012 R2.

    Thanks in advance.

  78. BS says:

    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.

  79. Ivan says:

    При открытия отчета через MS SQL – Отчеты – Пользовательские отчеты, пишет ошибку
    Error “Недопустимое имя объекта “Conferences”

  80. Ivan says:

    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.

  81. Ivan says:

    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.

  82. Mike says:

    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!

  83. Mike says:

    ^ – sorry. I read the answer on a different page. It is accessed from the search report.

  84. Ivan says:

    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)

  85. Chetan says:

    Hi, I’m not able to drop down as in step 13 A. It is not showing any result.
    Regards, Chetan

  86. Ezzi says:

    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

  87. Ezzi says:

    Never mind apparently I needed to check the actions tab. after I saved it started working

  88. Error running Conversation Details Report says:

    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.

  89. Chirag says:

    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

  90. Chirag says:

    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…

  91. Nick says:

    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.

  92. Chris M says:

    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

  93. CD says:

    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.

  94. AGGHHH says:

    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)

  95. kjstech says:

    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!

  96. AGGHHH says:

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

  97. AGGHHH says:

    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.

  98. Andrew says:

    Thanks for this post…works a treat 🙂

  99. AGGHHH says:

    Seems the archiving was set up, but the option for archiving IM and meeting session details was not enabled. Definitely works great, thanks!

  100. RoosterDude says:

    When attempting to run Search, I get – The item ‘/LyncReporting/Conversation Details Report’ cannot be found. (rsItemNotFound)

    Any ideas? Thanks!

  101. RoosterDude says:

    Actually, I get that when clicking on one of the Time (UTC) results.

  102. Gordon says:

    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

  103. ozan says:

    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.

  104. Lance Jones says:

    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.

  105. leo says:

    @ 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?

  106. Vladimir Michel Bacurau Magalhães says:

    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?

  107. Jamie says:

    The date and time is wrong on the report. How do I fix that?

  108. Chip Wolt says:

    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?

  109. Chip Wolt says:

    @dodeitte

    That makes sense. Thank you for quick reply.

  110. Moo says:

    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.

  111. Scott Eastman says:

    Hi, I am having the issue – The EXECUTE permission was denied on the object ‘DbGetVersion2’, database ‘LcsLog’, schema ‘dbo’. However when i follow the above details i do not have a stored procedure [dbo].[DbGetVersion2]

  112. adnduyanh says:

    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.

  113. Harry says:

    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?

  114. Will says:

    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…

  115. sal says:

    thanks for this doug, cant believe i just found it after setting up our archive 6 months ago. did you ever find a fix for "Could not find stored procedure ‘DbGetVersion2’ issue for lync 2010?

  116. Will Marc says:

    These reports has been tested using Skype for Business Server 2015 Standard? Do you know if they should supposed to work?

  117. kikigak says:

    An error has occurred during report processing. (rsProcessingAborted)
    Query execution failed for dataset ‘DatabaseVersion’. (rsErrorExecutingCommand)
    Could not find stored procedure ‘DbGetVersion2’.

  118. kikigak says:

    Got error on Dashboard.

    An error has occurred during report processing. (rsProcessingAborted)
    Query execution failed for dataset ‘DatabaseVersion’. (rsErrorExecutingCommand)
    Could not find stored procedure ‘DbGetVersion2’.

    Could anyone help me why there is no DbGetVersion2 on my SQL 2008 R2?

    Thanks in advance,

    kikigak

  119. kikigak says:

    @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

  120. erin says:

    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

  121. Vlad says:

    @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?

  122. NK says:

    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

  123. Michael says:

    Has this been updated for Skype for Business or will it work as is???

  124. dodeitte says:

    @Michael

    It hasn’t been updated for Skype for Business, but it should work as is.

  125. Rich says:

    Nice report and clear instructions-just what I needed. Thanks!

  126. Rogério Galvão says:

    Hi,

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

    example:

    Ne3o = Não
    Agradee7o = Agraço

  127. Rogério Galvão says:

    Agradee7o = Agradeço

  128. Dave says:

    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.

  129. Zeeshan Malik says:

    Will you have one for Skype for Business Server 2015?

  130. neil says:

    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

  131. Jeff says:

    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”)

  132. Jack Motley says:

    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

    1. dodeitte says:

      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.

  133. Hi there, would like to check if this can be used in SFB environment ?