How to Create Additional Lync Server 2010 Call Details Reports: Part 2

[アーティクル]
01/12/2012

Microsoft Lync Server 2010 Monitoring Server Reports provide a collection of system usage and call diagnostic reports. This collection of reports meets the needs of most Lync Server 2010 audio/visual (A/V) call scenarios. However, administrators that want to add to the extensibility of the Lync Server 2010 Monitoring Server Reports can easily do so by using tools that are provided with Microsoft SQL Server and the Monitoring Server Reports to add user-defined call details reports that are based on user-defined data sources. This article, in two parts, describes how to create these additional call details reports.

Author: Mike Adkins

Publication date: January 12, 2012

Product version: Lync Server 2010

Introduction

Any Lync Server 2010 infrastructure benefits from installing Lync Server 2010 Monitoring Service Reports. The variety of summary and diagnostics reports that are provided by the Monitoring Service Reports usually meet the needs of the Lync Server 2010 Voice over Internet Protocol (VoIP) network administrator. Some organizations may require the addition of proprietary reports that display additional information from the Lync Server 2010 Monitoring Service LcsCDR database. The prerequisite installation of the Microsoft SQL Server Reporting Services includes the SQL Server Report Builder tool, which provides a convenient way to access application or user-defined data sources that are hosted by Microsoft SQL Server databases. This makes the SQL Server Report Builder tool an excellent choice for creating additional Lync Server 2010 call details reports that meet the requirements of an organization's VoIP reporting services. This article is divided into two parts that explain the steps needed to create a Lync Server 2010 call details report based on specific information that is stored in the LcsCDR database. Part 1 of this article provides the step-by-step information that is needed to use the SQL Server Management Studio tools to design and create a SQL Server stored procedure that can be used as a data set for a custom Lync Server 2010 Monitoring Service Report. In this, part 2 of the article, I describe stepping through the SQL Server Report Builder Table or Matrix Wizard to create a data source, access data from database objects, design a report that includes summary information and user-defined functionality, and then to save that report.

Creating a custom call details report

The first step to creating a custom call details report for use with the pre-existing Lync Server 2010 Monitoring Service Reports is to establish a secure connection to the SQL Server and LcsCdr database objects that will be used as data sources for any new reports. Next, access will be provided to the user-defined database objects that reside in the LcsCDR database. Once the data is accessible, then it is time to present it by using the SQL Server Report tool. The SQL Server Report tool allows data to be presented as a chart, map, or as a table or matrix as shown in this article. Once the report is completed, it can be saved to the LyncServerReports folder for convenient access.

Accessing the LcsCdr database objects

Use SQL Server Report Builder tool in the following steps to access the LcsCdr database and the user-defined referredToPSTNFrom SQL Server stored procedure that will be used as the source of information for the new Lync Server 2010 call details report. This new report will be used to determine which Lync 2010 users have used the call forwarding feature to forward their incoming calls to the public switched telephone network (PSTN).

1. From a browser, open the SQL Server Reporting Services home page by entering its URL in the browser’s address line. For example: <server01/Reports>.

2. Click the Report Builder link to access the SQL Server Report Builder wizard (see figure 1).

Figure 1. Opening SQL Server Reporting Services

3. Click New Report, and then click Table or Matrix Wizard (see figure 2).

Figure 2. Starting the SQL Report Builder Wizard

4. Select the Create a new dataset option on the Choose a dataset page, and then click Next (see figure 3).

Figure 3. Selecting a dataset from the LcsCDR database

5. On the Choose a connection to a data source page, click Browse to locate the preexisting CDRDB data connection object.

6. In the Select data source dialog box, select the CDRDB data connection object, and then click Open.

7. On the Choose a connection to a data source page, click Next .

8. In the Database view window on the Design a query page, expand the stored procedures node and check the stored procedure that will be used for the new report. Click Next (see figure 4).

Figure 4. Select the data source for the report

Designing and previewing the report

The next section of this article will walk through the design phases of the report, previewing the report, and saving the report to the SQL Server Reporting Service LyncServerReports folder.

1. Use the Arrange fields page of the New Table or Matrix wizard to arrange the fields for the report in column and row fashion. Row grouping places similar information together in the report. Column groups are displayed from left to right across the report. Summary values such as count, sum, average, maximum, and minimum are used to calculate values contained in the Row groupings (see figure 5).

Figure 5. Arranging the fields for the report

2. To design the report’s layout, on the Choose the layout page, make sure the defaults are checked, and then click Next.

3. Use the Choose the style page to apply one of the predefined layouts to the report, and then click Next (see figure 6).

Figure 6. Selecting the style for the report

4. Use the SQL Server Reporting Service report designer to add finishing touches to the report. You can add a title for the report, adjust column widths, add built-in fields if you want, and add any other user-defined touches to enhance the report (see figure 7).

Figure 7. Working with the report in design view

5. To preview the report, on the toolbar, click Run, or press F5 on the keyboard. Remove the checks from the default parameter Null checkboxes and add the needed parameter values, for example the beginning and ending dates, and then click View Report to preview the report.

6. With the report in full view you can make derminations on how the report should be formatted for optimal viewing. Also, notice the “+” in the column headings. This feature allows the viewer to expand that column to view more detailed information (see figure 8).
Figure 8 – Previewing the report

7. Once you have formatted the report, click File, click Save, and then save it to the following location: LyncServerReports\Reports_Content folder. Now the report can be accessed from the SQL Server Reporting Services LyncServerReports page by clicking its link (see figure 9) .
Figure 9. Saving the report to LyncServerReports folder

Summary

The installation of the Lync Server 2010 Monitoring Server Reports provides a set of predefined reports that are used for Lync Server 2010 VoIP call analysis. This is useful when additional Lync Server 2010 call details reporting is needed for more detailed analysis of VoIP call traffic on a network. The default installation of the SQL Server Report Builder tool that is included with the Monitoring Server Reports installation can be used for easy access to any additional information in the LcsCDR database. This additional information can be utilized for reporting purposes. The SQL Server Report Builder wizard will allow the creation of maps, charts, and table or matrix reports that can display this additional Lync Server 2010 VoIP call information. Once created, these reporting objects can be saved to and then accessed from the LyncServerReports folder on the SQL Server Reporting Services home page.

Additional Information

Lync Server Resources

We Want to Hear from You

Comments

Anonymous
January 01, 2003
Here's a free basic solution to Lync Call Accounting which I hope will become a report server report in time. The solution can figure out who should be billed for the call, then looks up that users name, department and company up in Active Directory - http://www.lync.geek.nz/2014/03/lync-call-accounting-for-user-or.html. Please leave feedback so I can improve it.
Anonymous
January 01, 2003
Outstanding post.
Anonymous
January 01, 2003
I'm following this Link to build a report that generates detailed reports about conferences Time, start, end ..etc In the report builder there's a stored procedure called " CDR Conference List " when I add this I have to define the Row/column and value. I'm not sure which fits in where and how to do this, I tried several ways but when I run the query there's nothing in there! Any one knows how to do this ? Thanks
Anonymous
January 01, 2003
Great post.
Anonymous
February 10, 2012
Thank you for great idea, but how to setup an additional report page where I can see the client versions? And also to go deeper to find who is using "strange" client version?
Anonymous
June 04, 2012
i need to get the reports for a company in lync reporting tool. can u tell me the procedure
Anonymous
July 16, 2012
I have the same requirement as that of Petri - to create a custom report that reports client versions. yet to try this custom report to see if it can meet the requirement.
Anonymous
August 30, 2012
Hi, How do I track all the call volume of a particular location. The users are EV enabled only OR EV and PBX enabled.
Anonymous
September 17, 2012
I hope this answers all of your questions: Accessing the client version information ov Lync 2010 clients and devices for all the Lync Server 2010 modalities has to be done by accessing this data from the rtc and rtcdyn Lync Server 2010 databases. Please review the following Microsoft TechNet blogpost for information on how to do this. List the Users and Client Endpoint Versions Connected to a Registrar Pool: Remote Connection blogs.technet.com/.../list_2d00_users_2d00_and_2d00_endpoints_2d00_remote.aspx
Anonymous
October 02, 2012
I need a report that will display the called number. In fact, we are using a 10 digit client matter code for call accounting that i'd like to parse if possible. Is the called number stored anywhere?
Anonymous
October 02, 2012
Luke, You may find the information that you are looking for in the FromNumberId and ConnectedNumberId fields of the VoipDetails table and the PhoneId, PhoneUri fields of the Phones table. Both of these tables are located in the LcsCdr database. The LcsCdr database was not designed for the use of ad hoc reporting, but for efficiency. So designing select queries to pull relative data from it can be difficult. I would recommend getting a database developer involved with youre project to ensure that you reporting will provide accurate results. Thanks

次の方法で共有