Key Tips to Get Started with Lync Monitoring & Reporting

Update – August 25, 2012: the June 2012 Lync Server 2010 updates describe how to update the Lync Monitoring Database and re-dploying the reports after applying the update. Until you do this, you could have problems with some of the reports such as the User Acivity report after applying the June Updates. See the following KB article for more information: http://support.microsoft.com/kb/2703324.

This blog post contains tips and information useful during the deployment of the Monitoring role and the native Lync monitoring reports. This supplements the Microsoft installation guide: Microsoft Lync Server 2010 – Deploying Monitoring (this same guide can be downloaded at http://www.microsoft.com/download/en/details.aspx?id=8207).

If you are in the planning stage of deploying Lync monitoring, read the Microsoft Lync Server 2010 Planning for Monitoring Guide (http://technet.microsoft.com/en-us/library/gg412952.aspx) before embarking on your deployment.

One frequently asked question during the planning stage is whether you can collocate the monitoring database with a Lync back-end database. The answer is yes, but the back-end database needs to reside in a separate SQL instance.

Installing Lync Monitoring Without the Lync Monitoring Reports

If you do not want to deploy the native Lync Monitoring Reports (Quality of Experience and Call Detail Record reports), you can install the Monitoring role without it. This is not obvious because the deployment guide integrates the steps for deploying the reports in the guide. In a nutshell you can just skip the following 2 steps in the deployment guide:

• Installing SQL Server with Reporting Services
• Deploying Monitoring Server Reports

This saves you some work – most notably having to install and configure SQL Reporting Services (SRSS). I recommend installing these native reports if you can however – they are valuable for diagnosing quality problems and tracking system usage.

Not sure Whether SQL Reporting Services (SRSS) is Installed Already?

You can either try navigating to the Report Server URL in a web browser (located at: /ReportServer”>/ReportServer”>/ReportServer”>/ReportServer”>/ReportServer”>/ReportServer”>/ReportServer”>http://<ReportServerHostName>/ReportServer), or if you are on the host where it should be installed, you can run the Reporting Services Configuration Manager (Programs | Microsoft SQL Server 2008 R2 | Configuration Tools | Reporting Services Configuration Manager). The latter will show you to find all the SQL instances that have SRS installed.

Note: you can install SRSS on the same SQL server as the monitoring database.  This is not explicitly stated anywhere, but in my opinion implied, and I have seen it work with issue in many deployments.

Monitoring Role and Reports Installed Without Errors, but the Reports all have no Data…

The Lync monitoring does not explicitly identify the need to have Message Queuing (with AD integration) feature installed no the Front-End server(s). If you installed the Lync Front-Ends and added the monitoring role later, you could have easily missed this. If this is the case, you will see an Lync server event log error as follows:

Just install MSMQ with AD integration on the Lync Front-End to resolve this issue.

If the Lync reports are still empty and MSMQ is installed on the Front-End and Monitoring role, the credentials configured to allow SQL Reporting Services (SRSS) to access the Lync database might not have the ‘Log on Locally’ right on the Monitoring server (see below for more information).

Installing Windows 2008 R2 SQL Reporting Services for use with the Lync Reports

Adding SRSS to an existing SQL Server

If you need to ADD the Windows 2008 R2 SQL Reporting Services (SRSS) feature to an existing installation of SQL Server 2008 R2, you can do this by:

1. Running the SQL Server installation Center (from Programs | Microsoft SQL Server 2008 R2 | Configuration Tools)
2. Select Installation | New installation or add features to an existing installation (you’ll have to supply a path the your SQL 2008 R2 Installation Media)
3. For Installation Type choose to “Add features to an existing instance of SQL Server 2008” and use the drop-down list to select the default instance of SQL Server (to update).
4. If your installation media is on a network share, this installation can take a long time (e.g. +1 hour).
5. If your installation does fail, check-out this TechNet article to help you troubleshoot it: How to: Troubleshoot a Reporting Services Installation Problem.

This is not obvious given the complexity of the SQL installation process.

Selecting a Service Account

You can use the Local Service Account for the SQL Reporting Services Windows Service Account (e.g. NT AUTHORITY\LOCAL SERVICE). More information about what account you should use here is given in http://technet.microsoft.com/en-us/library/ms143736.aspx#selectingserviceaccount

It is recommended that you use a least-privilege domain user account with network connection permissions. If possible, specify an account that is used exclusively by the report server so that you can audit login activity for this account.

 

Later, if you want to modify the Report Server service account or update the password, use the Reporting Services Configuration tool to make your changes. For more information about account recommendations or updating service account settings

Selecting an Installation Mode Option

You will want to choose the “Install the native mode default configuration” option, but this is only available if you are installing a local Database Engine instance and a Reporting Services instance at the same time.

If you are installing SRSS after the database engine has been installed, you will need to choose the Install but do not configure the server. You will need to run the Reporting Services Configuration tool (Programs | Microsoft SQL Server 2008 R2 | Configuration Tools | Reporting Services Configuration Manager) after installation to configure it.

The Report Server Database

I recommend creating a new database for the Report Server. This database will house all the reports (the contents, etc…). For my installations:

1. I use the default “ReportServer” name for the database.
2. The NT AUTHORITY\LOCALSERVICE account for the credentials (used by the report server to connect to the database).

Execution Account

I have not had to specify an Execution Account (which is used for other data sources or connecting to remote servers) to use the Lync Monitoring Reports.

More Information on Installing SQL Reporting Services

• Microsoft TechNet SQL Server – Report Server Installation Options
• Microsoft TechNet SQL Server – Considerations for Installing Reporting Services

Installing the Local Lync Server Components

This step is straightforward. It installs 2 Lync Windows Services and 2 associated databases:

1. The Lync Server Call Detail Reporting service which uses the LcsCDR database.
2. The Lync Server QoE Monitoring service which uses the QoEMetrics database.

Deploying the Monitoring Server Reports

Note: Disabling the Federal Information Processing Standard (FIPS) Compliant Algorithm Policy.

The deployment guide contains the following note during this step:

Note: In order to publish reports, you need to either disable the Federal Information Processing Standard (FIPS) compliant algorithm policy on the Reporting Services Server or apply the workaround described in Microsoft Knowledge Base article 911722….

In two deployments of the Monitoring Role and associated Reports I never had to take any action here.

Supplying Credentials for SRSS to Access the Lync Monitoring Reports.

This is one of the more tricky steps when deploying the reports. You will need to provide credentials for the SQL Reporting Services (SRSS) to access the Lync Monitoring server database as shown here:

You cannot use the Local Service account here – you need to specify an explicit user account. The Monitoring deployment guide states that this account needs SQL sysadmin rights to the Monitoring Server database. Microsoft recommends you create a user account for this purpose.

If the domain account has Lync Server administrator rights, it will likely already have SQL sysadmin rights to the Monitoring database.

To see which credentials has access to the Lync CDR and QoE Databases you can use the Microsoft SQL Server Management Studio. Simply connect to the SQL server instance that contains these two databases, and right-click and select Properties | Permissions. This will list the users that have permissions and what levels of permissions (roles) on the database.

There are two other requirements for this account.

1. It needs the “Allow log on locally” right. If the account does not have the log on locally right, the reports will come up empty.
2. You must specify this account in the NetBIOS format – i.e. domain\username. If you use the FQDN format, you will get an error “Exception calling “Create” with “0″ argument(s)” during the installation of the reports.

Jason Shave describes this nicely in his blog here: http://jasonshave.blogspot.com/2011/03/resolved-lync-monitoring-reports-are.html.

Also, Terence Luk does a good job explaining what happens behind the scenes with the credentials that you enter here: http://terenceluk.blogspot.com/2011/02/what-credentials-am-i-supposed-to-enter.html. And I just discovered a blog post of his that goes into a lot of details on the Microsoft Lync Server 2010 Enterprise Pool SQL Database Permissions.

To change these credentials (without having to go through the Lync Deployment Wizard).

1. Navigate to the Lync Reports on the SRSS site (e.g. at http://<SRSS host>/Reports).
2. Drill into the LyncServerReports folder.
3. Drill into the Reports_Content folder.
4. Click into the CDRDB data source (this is for the LcsCDR database access). This should bring up the properties of this data source as shown in the figure below.
5. Here you can change the credentials.
6. Click into the QMSDB data source (this is for the QoEMetrics database access) and change the credentials here also.

This step in the Lync Monitoring deployment guide is available here: http://technet.microsoft.com/en-us/library/gg398673.aspx

You can see this account in the Internet Information Services (IIS) Manager management console on a connection to the SRS Report Server and port where the Lync Monitoring Server reports are deployed.

Using the Integrated Windows Credentials

I have successfully used the “Windows integrated security” setting. The windows credentials I was subsequently running the Lync reports under had the ‘Log on Locally’ right. This setting might not work in all cases, but I have had preliminary success with it, and it keeps you from needing separate credentials.

How to Grant Read Access to the Lync Monitoring Reports

If at some point after installing the Lync Monitoring Reports you want to grant a user read-only access to the reports, you need to do two things:

1. Add the user account to a Local security group (i.e. a local group on the computer running SQL Server Reporting Services).
2. Assign the ‘Monitoring Server Reports Browser‘ role to the local group on the LyncServerReports folder in SQL Reporting Services (usually at the URL http://<hostname>/Reports/Pages/Folder.aspx?ViewMode=List).

This is called-out in the Deploying Monitoring Server Reports Step in the Lync Server Monitoring Role Deployment Guide.

(Optional) In Specify Read-Only group page, specify the domain group that you want to grant read-only access to the Monitoring reports.

Tip - if the user account had the right permissions on the LyncServerReports folder, but on the CDRDB and QMSDB datasources, the user will receive the following error in the Web Browser:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source ‘CDRDB’. (rsErrorOpeningConnection)

QoE and CDR Purge Settings

To set the retention period (in the database) for the QoE and CDR data, navigate to the “Monitoring and Archiving” settings in the Lync control panel.

These settings are personal preference. For most installations I start off with 60 days for the QoE data, and 90 days for the CDR data. The QoE data is more applicable to diagnosing current problems, so you can usually have a much lower retention than the CDR data unless you are trending this information.

Additional Information

Mike Adkins has a good two part series on creating custom Lync Server Call Detail Reports:

• Part 1 – How to Create Additional Lync Server 2010 Call Details Reports
• Part 2 – How to Create Additional Lync Server 2010 Call Details Reports

Be Sociable, Share!

•