Welcome!

Blog Feed Post

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.

 
Pre-Requisites

·         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 http://gallery.technet.microsoft.com/Sample-Persistent-Chat-8ff37d5e. 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 Source=FABSQL1.fabrikam.com;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.

Read the original blog entry...

More Stories By Richard Schwendiman

My name is Richard Schwendiman and I am currently working for Microsoft as a (PFE) Premier Field Engineer specializing in both Exchange and Lync. I have been working as an IT Consultant for 13+ years focusing on a wide array of Infrastructure technologies. These technologies include Messaging, UC, Networking, Platforms, Active Directory, Virtualization, etc... I am currently certified as an MCSE (Microsoft Certified Systems Engineer), MCSE Messaging 2013, MCSE Communications 2013, MCSA 2012, MCITP Enterprise Messaging, MCTS-Lync, CCNA (Cisco Certified Network Associate), Commvault, CCNP (Cisco Certified Network Professional), and JNCIA-ER (Juniper Enterprise Routing). I am hoping that through this blog I can bring knowledge from the field and keep everyone informed about our ever changing Industry. Please feel free to email me any questions, comments, or concerns pertaining to this blog or any technology related things. Thanks and look forward to providing some good content. http://blogs.technet.com/b/rischwen/

Latest Stories
Without a clear strategy for cost control and an architecture designed with cloud services in mind, costs and operational performance can quickly get out of control. To avoid multiple architectural redesigns requires extensive thought and planning. Boundary (now part of BMC) launched a new public-facing multi-tenant high resolution monitoring service on Amazon AWS two years ago, facing challenges and learning best practices in the early days of the new service. In his session at 19th Cloud Exp...
There is growing need for data-driven applications and the need for digital platforms to build these apps. In his session at 19th Cloud Expo, Muddu Sudhakar, VP and GM of Security & IoT at Splunk, will cover different PaaS solutions and Big Data platforms that are available to build applications. In addition, AI and machine learning are creating new requirements that developers need in the building of next-gen apps. The next-generation digital platforms have some of the past platform needs a...
SYS-CON Events announced today that Bsquare has been named “Silver Sponsor” of SYS-CON's @ThingsExpo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. For more than two decades, Bsquare has helped its customers extract business value from a broad array of physical assets by making them intelligent, connecting them, and using the data they generate to optimize business processes.
SYS-CON Events announced today that ReadyTalk, a leading provider of online conferencing and webinar services, has been named Vendor Presentation Sponsor at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. ReadyTalk delivers audio and web conferencing services that inspire collaboration and enable the Future of Work for today’s increasingly digital and mobile workforce. By combining intuitive, innovative tec...
Cognitive Computing is becoming the foundation for a new generation of solutions that have the potential to transform business. Unlike traditional approaches to building solutions, a cognitive computing approach allows the data to help determine the way applications are designed. This contrasts with conventional software development that begins with defining logic based on the current way a business operates. In her session at 18th Cloud Expo, Judith S. Hurwitz, President and CEO of Hurwitz & ...
Fact is, enterprises have significant legacy voice infrastructure that’s costly to replace with pure IP solutions. How can we bring this analog infrastructure into our shiny new cloud applications? There are proven methods to bind both legacy voice applications and traditional PSTN audio into cloud-based applications and services at a carrier scale. Some of the most successful implementations leverage WebRTC, WebSockets, SIP and other open source technologies. In his session at @ThingsExpo, Da...
While DevOps promises a better and tighter integration among an organization’s development and operation teams and transforms an application life cycle into a continual deployment, Chef and Azure together provides a speedy, cost-effective and highly scalable vehicle for realizing the business values of this transformation. In his session at @DevOpsSummit at 19th Cloud Expo, Yung Chou, a Technology Evangelist at Microsoft, will present a unique opportunity to witness how Chef and Azure work tog...
Almost two-thirds of companies either have or soon will have IoT as the backbone of their business in 2016. However, IoT is far more complex than most firms expected. How can you not get trapped in the pitfalls? In his session at @ThingsExpo, Tony Shan, a renowned visionary and thought leader, will introduce a holistic method of IoTification, which is the process of IoTifying the existing technology and business models to adopt and leverage IoT. He will drill down to the components in this fra...
The Internet of Things can drive efficiency for airlines and airports. In their session at @ThingsExpo, Shyam Varan Nath, Principal Architect with GE, and Sudip Majumder, senior director of development at Oracle, will discuss the technical details of the connected airline baggage and related social media solutions. These IoT applications will enhance travelers' journey experience and drive efficiency for the airlines and the airports. The session will include a working demo and a technical d...
I'm a lonely sensor. I spend all day telling the world how I'm feeling, but none of the other sensors seem to care. I want to be connected. I want to build relationships with other sensors to be more useful for my human. I want my human to understand that when my friends next door are too hot for a while, I'll soon be flaming. And when all my friends go outside without me, I may be left behind. Don't just log my data; use the relationship graph. In his session at @ThingsExpo, Ryan Boyd, Engi...
SYS-CON Events announced today that Pulzze Systems will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Pulzze Systems, Inc. provides infrastructure products for the Internet of Things to enable any connected device and system to carry out matched operations without programming. For more information, visit http://www.pulzzesystems.com.
SYS-CON Events announced today that Numerex Corp, a leading provider of managed enterprise solutions enabling the Internet of Things (IoT), will exhibit at the 19th International Cloud Expo | @ThingsExpo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Numerex Corp. (NASDAQ:NMRX) is a leading provider of managed enterprise solutions enabling the Internet of Things (IoT). The Company's solutions produce new revenue streams or create operating...
If you’re responsible for an application that depends on the data or functionality of various IoT endpoints – either sensors or devices – your brand reputation depends on the security, reliability, and compliance of its many integrated parts. If your application fails to deliver the expected business results, your customers and partners won't care if that failure stems from the code you developed or from a component that you integrated. What can you do to ensure that the endpoints work as expect...
WebRTC adoption has generated a wave of creative uses of communications and collaboration through websites, sales apps, customer care and business applications. As WebRTC has become more mainstream it has evolved to use cases beyond the original peer-to-peer case, which has led to a repeating requirement for interoperability with existing infrastructures. In his session at @ThingsExpo, Graham Holt, Executive Vice President of Daitan Group, will cover implementation examples that have enabled ea...
The Transparent Cloud-computing Consortium (abbreviation: T-Cloud Consortium) will conduct research activities into changes in the computing model as a result of collaboration between "device" and "cloud" and the creation of new value and markets through organic data processing High speed and high quality networks, and dramatic improvements in computer processing capabilities, have greatly changed the nature of applications and made the storing and processing of data on the network commonplace.