How to Create Additional Lync Server 2010 Call Details Reports: Part 1
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 9, 2012
Product version: Lync Server 2010
Introduction
The Lync Server 2010 Monitoring Server Reports provide customers with a set of predesigned reports that provide the diagnostic and the summary details for audio/visual (A/V) calls that are managed in the Lync Server 2010 environment. Installing the Lync Server 2010 Monitoring Service adds the LcsLog (archiving) and the LcsCDR (call details) databases to the SQL Server instance that manages data for the Lync Server 2010 Monitoring Service. The predesigned Monitoring Server Reports may not provide all the information that is needed by an organization to completely assess the use of their networks Voice over Internet Protocol (VoIP)-enabled clients. Any need for additional A/V call detail reporting could require the design and implementation of new database objects for the LcsCDR database, a data source connection, and the reports that will display the additional A/V call detail information.
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 2 of this article will provide the step-by-step information for using the Microsoft SQL Server Report Builder to create the data connection to the new SQL Server stored procedure and the new Lync Server 2010 call details report that will display that stored procedures information.
Designing and creating the database objects
Whether you create a new Lync Server 2010 call details report is dependent on the availability of the data that the report will present. You can use Microsoft SQL Server Management Studio to display all of the LcsCDR database’s tables and their contents so you can inspect the information that may be used for a new report. Just expand Tables node under the LcsCDR database (see Figure 1).
Important SQL Server requires the delegation of its security model to the user account that will access the SQL Server, perform SQL Server configuration updates, read and write information that is stored in its databases and perform design updates to databases. For detailed information on managing SQL Server permissions, see the “Additional Information” section of this article.
Figure 1. Using SQL Server Management Studio to analyze information in the LcsCDR database
To view additional information stored in the Lync Server 2010 Monitoring Service LcsCDR database, you need to create the SQL Select queries that will be used as the basis for the report’s SQL Server stored procedure. The SQL Select query will allow the extraction of information from the tables in the LcsCDR database that contain information needed for the report. A SQL Select statement allows the use of SQL Join syntax to extract related information from multiple tables in a database. The simple SQL Select query example that is provided (see Figure 2) will use a SQL Select statement with a simple SQL Inner Join and SQL Where clause. The purpose of the Inner Join is to display the number of rows of information in a secondary table that contains a key value that matches the key value for one row in the primary table. In practice the secondary table will contain many matches for the key value that is listed only once in the primary table. This determines the range of data and the overall number of rows returned by the SQL Select query. The SQL Where clause is used to determine the type and amount of information that is returned by the SQL Select query based in the criteria defined by it.
Note: This article will not go into detail on the many uses of SQL syntax. For details about using basic SQL Select statements, see the SQL Server Search option from the Help menu of SQL Server Management Studio.
Creating a SQL query
The SQL Select query created in this example provides information that can be used to determine which Microsoft Lync 2010 users have used the enabled call forwarding feature to forward their incoming calls to the public switched telephone network (PSTN). This information will be displayed in a Lync Server 2010 Call Details Report that can be used to track the cost of this type of call to the PSTN (see Figure 2). The Select query will extract information from two tables that are part of the LcsCdr database. The primary table for the SQL Select query is named Users. The Users table contains a record for each Lync 2010 user that initiates an A/V call. The secondary table is named SessionDetails. The SessionDetails table contains a record of each A/V call that is managed by the Lync Server 2010 Voice over IP (VoIP) environment. The key fields in each table that will host the Inner Join are Users.UserId and SessionDetails.ReferredById. Each of these fields contains an integer value that represents the Lync A/V caller’s UserId. With the Inner Join defined in this manner, the SQL query will display the user’s SIP Uniform Resource Identifier (URI) that referred or forwarded the incoming A/V call from another local Lync 2010 user to the PSTN.
The following procedure shows how to create the SQL Select query statement for analyzing A/V calls that have been forwarded by a specific Lync 2010 user to the PSTN.
To create the SQL Select query statement
1. Open the SQL Server Management Studio console.
2. On the SQL Server Management Studio’s toolbar, click New Query to open a SQL Server query window.
3. Use the SQL Server query window to design and test the query that will be used as the data source for the new A/V call detail report.
4. To test and view the results of the new query, click Execute, as shown in Figure 2.
5. Save the contents of the SQL Server query window as a file with a .sql extension.
Figure 2. Creating and testing a SQL Select query
Create a SQL Server Stored Procedure
A SQL Server stored procedure is a database object that contains procedural instructions that are defined in SQL syntax. It’s useful because of its portability. In this example the SQL Server stored procedure is designed to contain the SQL Select query statement that we defined in the “Creating a SQL Query” section of this article. This SQL Server stored procedure allows the entry of the BeginDate and EndDate parameters to be passed to the SQL Query’s Where clause.
Creating a SQL Server stored procedure requires three steps:
- Create the SQL Server stored procedure template
- Add the SQL Select query’s code to the SQL Server stored procedure template
- Test the SQL Server stored procedure
To create the SQL Server stored procedure template
1. Use the SQL Server Management Studio object browser to locate the LcsCDR database that is listed under the user database objects.
2. Expand the LcsCDR database, and then expand the Programmability node.
3. Right-click the Stored Procedures node, and then click New Stored Procedure.
4. The SQL Server query window will now display the SQL Server Create Stored procedure template, as shown in Figure 3.
Figure 3. The SQL Server Create Stored procedure template
The next step in the stored procedure creation process is to add the SQL Select query’s code (see Figure 2) to the SQL Server stored procedure template.
To add the SQL Select query’s code
1. Add the name for the stored procedure to the right of the Create Procedure clause of the SQL Server stored procedure template, as shown in Figure 3.
2. Copy and paste the SQL query @BeginDate and @EndDate parameters from the SQL query that is described in the “Creating a SQL Query” section of this article into the parameters section of the SQL Server stored procedure template, as shown in Figure 3.
3. Copy and paste the SQL query Select statement from the SQL query that is described in the “Creating a SQL Query” section of this article into the “Insert statements for procedure here” section of the SQL Server stored procedure template, as shown in Figure 3.
4. To create the new SQL Server stored procedure, click Execute, as shown in Figure 4.
Figure 4. Adding the SQL Select query syntax for the new SQL Server stored procedure
The last step in the creating database objects procedure is to test the new SQL Server stored procedure to make sure that it works as expected.
To test the SQL Server stored procedure
1. Open the SQL Server Management Studio console.
2. From the toolbar, click New Query to open a SQL Server query window.
3. Select the LcsCDR database from the database selector on the toolbar.
4. Use the SQL Server query windows to type the following (as shown in Figure 5):
Execute <name of stored procedure> <paramerter1>, <parameter2>…
5. To test and view the results of the new query, click Execute.
Figure 5. Running the new SQL Server stored procedure
Summary
You need only a limited amount of knowledge of SQL Server Management Studio tools to use the A/V conferencing information that is stored in the LcsCDR database to create custom call details reports. Using SQL Select queries to analyze A/V call detail information provides insight into how specific information can be extracted from the LcsCDR database as presentation material for a call details report. The use of the SQL Server stored procedure database object provides a convenient way to manage access to the data that will be presented by the call details report. The second part of this article will provide a step-by-step process on how to access the output of SQL Server stored procedures from a SQL Server Report Builder report.
Additional Information
For more details about creating additional Lync Server 2010 Monitoring Server Reports, see chapter 2 of the Lync Server 2010 Resource Kit:
For more details on managing SQL Server permissions: