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
All organizations that did not originate this moment have a pre-existing culture as well as legacy technology and processes that can be more or less amenable to DevOps implementation. That organizational culture is influenced by the personalities and management styles of Executive Management, the wider culture in which the organization is situated, and the personalities of key team members at all levels of the organization. This culture and entrenched interests usually throw a wrench in the work...
SYS-CON Events announced today that Outlyer, a monitoring service for DevOps and operations teams, has been named “Bronze Sponsor” of SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Outlyer is a monitoring service for DevOps and Operations teams running Cloud, SaaS, Microservices and IoT deployments. Designed for today's dynamic environments that need beyond cloud-scale monitoring, we make monitoring effortless so you ...
My team embarked on building a data lake for our sales and marketing data to better understand customer journeys. This required building a hybrid data pipeline to connect our cloud CRM with the new Hadoop Data Lake. One challenge is that IT was not in a position to provide support until we proved value and marketing did not have the experience, so we embarked on the journey ourselves within the product marketing team for our line of business within Progress. In his session at @BigDataExpo, Sum...
Virtualization over the past years has become a key strategy for IT to acquire multi-tenancy, increase utilization, develop elasticity and improve security. And virtual machines (VMs) are quickly becoming a main vehicle for developing and deploying applications. The introduction of containers seems to be bringing another and perhaps overlapped solution for achieving the same above-mentioned benefits. Are a container and a virtual machine fundamentally the same or different? And how? Is one techn...
Keeping pace with advancements in software delivery processes and tooling is taxing even for the most proficient organizations. Point tools, platforms, open source and the increasing adoption of private and public cloud services requires strong engineering rigor - all in the face of developer demands to use the tools of choice. As Agile has settled in as a mainstream practice, now DevOps has emerged as the next wave to improve software delivery speed and output. To make DevOps work, organization...
DevOps is often described as a combination of technology and culture. Without both, DevOps isn't complete. However, applying the culture to outdated technology is a recipe for disaster; as response times grow and connections between teams are delayed by technology, the culture will die. A Nutanix Enterprise Cloud has many benefits that provide the needed base for a true DevOps paradigm.
What sort of WebRTC based applications can we expect to see over the next year and beyond? One way to predict development trends is to see what sorts of applications startups are building. In his session at @ThingsExpo, Arin Sime, founder of WebRTC.ventures, will discuss the current and likely future trends in WebRTC application development based on real requests for custom applications from real customers, as well as other public sources of information,
China Unicom exhibit at the 19th International Cloud Expo, which took place at the Santa Clara Convention Center in Santa Clara, CA, in November 2016. China United Network Communications Group Co. Ltd ("China Unicom") was officially established in 2009 on the basis of the merger of former China Netcom and former China Unicom. China Unicom mainly operates a full range of telecommunications services including mobile broadband (GSM, WCDMA, LTE FDD, TD-LTE), fixed-line broadband, ICT, data communica...
With the introduction of IoT and Smart Living in every aspect of our lives, one question has become relevant: What are the security implications? To answer this, first we have to look and explore the security models of the technologies that IoT is founded upon. In his session at @ThingsExpo, Nevi Kaja, a Research Engineer at Ford Motor Company, will discuss some of the security challenges of the IoT infrastructure and relate how these aspects impact Smart Living. The material will be delivered i...
As software becomes more and more complex, we, as software developers, have been splitting up our code into smaller and smaller components. This is also true for the environment in which we run our code: going from bare metal, to VMs to the modern-day Cloud Native world of containers, schedulers and micro services. While we have figured out how to run containerized applications in the cloud using schedulers, we've yet to come up with a good solution to bridge the gap between getting your contain...
Apache Hadoop is emerging as a distributed platform for handling large and fast incoming streams of data. Predictive maintenance, supply chain optimization, and Internet-of-Things analysis are examples where Hadoop provides the scalable storage, processing, and analytics platform to gain meaningful insights from granular data that is typically only valuable from a large-scale, aggregate view. One architecture useful for capturing and analyzing streaming data is the Lambda Architecture, represent...
As organizations realize the scope of the Internet of Things, gaining key insights from Big Data, through the use of advanced analytics, becomes crucial. However, IoT also creates the need for petabyte scale storage of data from millions of devices. A new type of Storage is required which seamlessly integrates robust data analytics with massive scale. These storage systems will act as “smart systems” provide in-place analytics that speed discovery and enable businesses to quickly derive meaningf...
Your homes and cars can be automated and self-serviced. Why can't your storage? From simply asking questions to analyze and troubleshoot your infrastructure, to provisioning storage with snapshots, recovery and replication, your wildest sci-fi dream has come true. In his session at @DevOpsSummit at 20th Cloud Expo, Dan Florea, Director of Product Management at Tintri, will provide a ChatOps demo where you can talk to your storage and manage it from anywhere, through Slack and similar services ...
SYS-CON Events announced today that Ocean9will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Ocean9 provides cloud services for Backup, Disaster Recovery (DRaaS) and instant Innovation, and redefines enterprise infrastructure with its cloud native subscription offerings for mission critical SAP workloads.
Building a cross-cloud operational model can be a daunting task. Per-cloud silos are not the answer, but neither is a fully generic abstraction plane that strips out capabilities unique to a particular provider. In his session at 20th Cloud Expo, Chris Wolf, VP & Chief Technology Officer, Global Field & Industry at VMware, will discuss how successful organizations approach cloud operations and management, with insights into where operations should be centralized and when it’s best to decentraliz...