Standard reports included in Lync Server 2013 cover information on system usage, peer-to-peer sessions, conferencing sessions, and call quality. We will talk about some of them in the following paragraphs. However, in addition to the previously mentioned reports, we can also create custom reports based on the reporting data we are already gathering. Customizing reports is an important step to obtain information that fits better with our needs. We will see how to create a custom report that contains information on the time when users joined and left a conference.
- Lync reports are based on SQL Server Reporting Services (SSRS). To define customized reports, we have to use built-in tools in the SQL server. We can start by opening Reporting Services Configuration Manager and selecting the instance that we used to install the default reports. The next step is to open the Report Manager URL in a browser window. Then, we have to select Report Builder, as shown in the following screenshot:
Note that we could receive a request to download the report builder.
- We must select New Report and then Table or Matrix Wizard. See the following screenshot:
- Now, we have to select Create a Dataset and click on Next.
- In Data Source Connections, we must select Browse and go to the
Reports_Contentfolder to access the available Lync data. The selected data source screen is displayed in the following screenshot:
- Select the CDRDB database and click on Next. You need to insert the credentials to access the SQL database, as we can see here:
- Select a source (Tables, Views, or Stored Procedures) and then select Next. We are able to select CDRReportsMcuJoinsAndLeavesBaseView, as shown in the next screenshot:
- Click on Next.
- The next phase is to arrange the fields and information, as shown in the following screenshot. We can use UserId in Column groups, SessionIdTime in Row groups, and UserJoinTime and UserLeaveTime in Values. For Values, it is required that you select a specific kind of valorization. We have to use First, as shown here:
- In the Choose the layout screen window, remove the Show subtotals and grand totals flag, as shown in the next screenshot:
- On the Choose a style screen, pick your the favorite color scheme in the example; we will use Ocean.
- Now, we will have the Reports builder interface. Here, we can modify labels, columns, rows, and values. Every time we want to test our report, we can use the Run button, as shown in this screenshot:
- In our example, the result will be the one we can see in the picture. When we match UserId and SessionId, we will get a unique identification for every conference, and in the report, we will get the join and leave time for each user.
A good starting point to write custom reports is the TechNet documentation that outlines the call detail recording (CDR) database schema (http://technet.microsoft.com/en-us/library/gg398570.aspx) and Quality of Experience (QoE) database schema in Lync Server 2013 (http://technet.microsoft.com/en-us/library/gg398687.aspx). Some of the most important views of the CDR database (suggested in the Lync Conference presentation that is already mentioned) are Conferences, ConferenceSessionDetails, Registration, SessionDetails, and VoIPDetails, while for the QoE database, we have AudioStreamDetail, MediaLine, QoEReportsCallDetail, Session, and VideoStreamDetail.
Wesley Backelant has published a post in which he explores the use of PowerPivot as a tool to expand Lync monitoring. His work, Extending your Lync monitoring data using PowerPivot and Power View, is published at http://blogs.msdn.com/b/wesleyb/archive/2012/03/09/extending-your-lync-monitoring-data-using-powerpivot-and-power-view.aspx.
Iain Smith, in his blog, has dedicated an interesting post to custom reports in Lync Server 2013 (Lync 2013 – Creating Custom Lync Reports within Monitoring at http://northernlync.wordpress.com/2014/03/10/lync-2013-creating-custom-lync-reports-within-monitoring/).
Doug Deitterick made available a sample report dedicated to archiving (Sample Lync Server Archiving Report Available at http://blogs.technet.com/b/dodeitte/archive/2013/06/02/sample-lync-server-archiving-report-available.aspx).