Sample Lync Persistent Chat Archive Report

After receiving multiple requests for a way to report on Lync Persistent Chat Archive Data (from the mgccomp Compliance database) I decided to create this sample report. One problem with querying for this data directly from the mgccomp database is that the Persistent Chat rooms aren't listed by name and the Messages are in RTF format. This sample report will take care of all the data conversion and put that data into a presentable report. Currently this sample report will only work against Lync 2013 Persistent Chat Archiving data in the mgcomp database.

Current Features
·         Gather Persistent Chat Archive data from the mgccomp compliance database and create a presentable report

·         Ability to filter data based on Start and End dates, UserURI, or Persistent Chat Room

·         Export this report into CSV, PDF, and Word formats

Future Features

·         Currently when a file has been uploaded to the Persistent Chat room the report will show a blank space in the Messages field. This will hopefully be resolved in the next version. The plan is to include a link to where the file is stored on the Persistent Chat file store.

·         SQL Mirroring support. Currently you would need to change that data source if the primary SQL server for the Persistent Chat databases fails over to mirror. Follow Step 5 from the Report Installation section to change this value.

·         PDF Export doesn’t display the data correctly.


·         SQL Reporting Services Server (SRSS) – if you have deployed Lync Monitoring Reports then you should be able to use that same SRSS instance

·         Permissions – in order to run this report you must have permissions to query both the mgccomp (Persistent Chat Compliance) and mgc (Persistent Chat Data) databases.

Report Installation

1.       Download and Unzip the Reports from Copy the Persistent Chat Archive Report.rdl file to the SRSS server.

2.       Open Report Manager on the SRSS server via https://<SRSS Server Name>/Reports

3.       Click on "New Folder", give it a name e.g. "Persistent Chat Archive", and click OK

4.       Click on the newly created folder and select "Upload File". Click Browse and navigate to the location you copied the zip file from Step 1. Click on the "Persistent Chat Archive Report.rdl" file and select Open. Click OK which will upload the report.

5.       Configure the report to utilize your SQL servers.

a.       Click on the drop-down button for the "Persistent Chat Archive Report" and select "Edit in Report Builder".

b.      Expand the Data Sources folder, right click "mgccomp", and select Data Source Properties.

c.       On the General tab look under the section name Connection string. Change this value to Data Source=<SQL Server FQDN\Instance>;Initial Catalog=mgccomp.

                                                               i.      E.g. Data;Initial Catalog=mgccomp

d.      Click OK

e.      Click Save on the toolbar or press Ctrl + S

6.       Close Report Builder

Report Usage

1.       Open Report Server on the SRSS server https://<SRSS Server Name>/ReportServer

2.       Click on the directory created in step 3 during Report Installation (e.g. "Persistent Chat Archive")

3.       Click on the "Persistent Chat Archive Report" link which will take you into the report

4.       Default Values

a.       StartDate (required)– this will pull the current date of the SRSS server and subtract 1 day

b.      EndDate (required)– this will pull the current date of the SRSS server and add 1 day

c.       UserURI – (NULL)

d.      Persistent Chat Room – (NULL)

5.       Filtering Data – you can filter data based on any of the 4 parameters above. The filters are cumulative so if you select to filter on UserURI and Persistent Chat Room the results will only show IMs by the specified user in the specified chat room.

a.       UserURI – users SIP address.

b.      Persistent Chat Room – exact match on the room name including spaces.

6.       Results – by default the report is configured to display the results of Date Created as UTC time. You can change this to the local time zone of the report server if needed.

a.       Open the report in Report Builder, right click on the field under "Date Created (UTC)", and select "Expression"

b.      Under "Set expression for: Value" paste this =TimeZone.CurrentTimeZone.ToLocalTime(Fields!entryDate.Value)

c.       Click OK and then Save or press Ctrl + S

d.      Rerun the report

                                                               i.      Prior to change

                                                             ii.      Post Change


7.       Exporting Data to Word, XML, Excel, or CSV

a.       After generating the report click on the save icon on the taskbar and choose your format.

If there are any questions or comments feel free to leave them in the comments section below.
Code Versions – download most current versions here:
V1.0 – Initial Release.
Comments (10)
  1. Ed (DareDevil57) says:


  2. ZAch says:

    I dont have NodeName as a table to pull from in my persistant archive database. I get this error:

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

    I can edit the report to make it work but I get lots of garbage for node and compliance text.

  3. Zach says:

    I think I see my problem. I have the MGC database and compliacne DB on different servers

  4. Rob Mann says:

    I have the same error as Zach. But I can’t find any mgccomp database. I don’t even remember setting it up. All I have is an mgc database. Persistent Chat works though. Any ideas?

  5. xiaozhengmmm says:–

  6. Amit says:

    This is awesome, thank you.

  7. Danie5 says:

    I am not a scripting fundi, thus this is a request.
    Is it possible to do this same query in a vbscript routine?

  8. Danie5 says:

    Here is mine:
    objConnection.ConnectionString = "Provider=SQLOLEDB; Server=serverLYNCPChat;Database=mgccomp;DataTypeCompatibility=80;Integrated Security=SSPI"


    SQL_Command_String = "use mgccomp "&_
    "SELECT UPPER(RIGHT(c.cmplChannelUri, 36)), n.nodeGuid, n.nodeName, entryDate, cmplUserUri, cmplChatID, cmplType, "&_
    "SUBSTRING(cmplMessage, 2, CHARINDEX(‘]’, cmplMessage) – 2) AS cmplMessage "&_
    "FROM tblComplianceData c INNER JOIN mgc.dbo.tblNode n ON UPPER(RIGHT(c.cmplChannelUri, 36)) = CAST(n.nodeGuid AS VARCHAR(255))"&_
    "WHERE (cmplChatID IS NOT NULL) "&_
    "ORDER BY nodeName ASC"

    set data=objConnection.execute(SQL_Command_String)
    wscript.echo data.RecordCount


    The error I am getting is:
    chatReport.vbs(65, 1) ADODB.Recordset: Operation is not allowed when the object is closed.

  9. Danie5 says:

    OK, seems that the "use mgccomp " should not be in the statement but now I am getting:
    ‘-1’ on the ‘wscript.echo data.RecordCount’ which should be >1 as I have some records displaying when I use the SQL Management Studio to run the query.

Comments are closed.

Skip to main content