Introduction

In the newly published Networking Guide the authors, in Appendix B, discuss using custom queries against QoE data to manage audio call quality. They introduce the concepts of server-server reports, which present the network health of network paths between various types of Lync media servers, and subnet reports, which present the network health of network paths between Lync desktop clients and various types of Lync media servers.

The network health is expressed by the number of poor audio streams, where a poor stream is defined as having a packet loss rate over 1 percent and a maximum packet loss rate over 5 percent. For a discussion on why to use these values (and not the ClassifiedPoorCall values) please see the section B.4.4 in the Networking Guide.

In this blog post I'm reusing the reports (or queries) they introduce, but have modified them a bit. I wanted to have queries, which would allow me to show how the network health is trending day-over-day for media paths between additional types of end-points. The idea is to run these queries regularly while any network health problems are remediated and then hopefully see the resulting downward trend for poor streams.

Media paths

The media paths from the Network Guide are:

• Between conferencing servers and mediation servers
• Between mediation servers and IP PSTN gateways
• Between desktop clients and conferencing & mediation servers

I wanted to include these additional media paths:

• Between Lync clients
• Between Lync clients and other Lync server end-points
• Between conferencing and mediation servers and other Lync server end-points
• Between other Lync server end-points
• Between Lync clients and 3^rd party end-points

Having described which media paths are interesting let us look at the queries themselves.

Query output

The queries are built in the same way as in the Networking Guide, i.e. with INNER JOIN's, temporary views and audio streams from Caller and Callee.

The queries all display the same type of information: date, number of streams, the number of poor streams and the ratio of poor streams to total streams. An example is shown in Table 1 below.

Table 1. Sample output

Query structure

The elements that varies from query to query is the type of media end-point, how the end-point is connected to the network and the location of these end-points. I'm varying over 3 variables:

• UAType. The UAType specifies the type of end-point in the media path (represented by the UserAgent table column UAType). Examples are Conferencing Server (AVMCU), Mediation Server (MS) and Lync desktop client. By looking at the media streams between different UATypes you can get information about the audio streams used in the different scenarios, i.e. person-to-person calls or persons calling into an audio conference. The list of UATypes used in the queries are shown in Table 3
• Network Connection Type. This specifies the type of network connection used by the end-point, either wired or wireless (represented by the MediaLine table columns CallerNetworkConnectionType and CalleeNetworkConnectionType)
• Location. This specifies the location of the end-point, either Inside or Outside (represented by the MediaLine table columns CallerInside and CalleeInside). Outside means connected via the Edge

As an example let us look at the scenario where users are dialing in to audio conferences from PSTN or the audio conference dials out to PSTN users. This scenario involves conferencing servers talking to mediation servers. How can that be expressed using the 3 variables above?

The UAType for conferencing servers is 2 and for mediation servers it is 1. I assume that all conferencing and mediation servers are connected to the network using a wired connection, and that they are all placed inside on the corporate network. This gives us a query using the 3 variables which would look the following way in pseudo-code

• Get me all audio streams from (Caller with UAType 2, network connection type = wired and Inside) to (Callee with UAType 1, network connection type = wired and Inside)
• Get me all audio streams from (Caller with UAType 1, network connection type = wired and Inside) to (Callee with UAType 2, network connection type = wired and Inside)

The queries

Having described the different media paths, the UAType, network connection type and location it is now time to combine these into queries. When I created the queries I wanted to separate inside and outside streams, since typically outside or external streams would be using networks outside the control of the company. I also wanted to separate wired from wireless streams, because typically wireless streams provides worse quality than wired streams. I wanted to separate person-to-person calls, but have combined several of the streams to other server type media end-points into the same query.

Table 2 shows the different queries and the media path, end-point, network connection and location they cover for both Caller and Callee.

Table 2. Query definitions

The UAType groups I am using above are defined in Table 3 below.

Table 3. UAType groups

Running the queries

The queries are located in the attached TrendQueries.Zip file. The queries are sample queries, comes "as-is" and I'm not providing any support for them.

Each query is its own text file. You run one of the queries by copying all the text from the query text file into a New Query window inside SQL Management Studio connected to your QoE database. You then change the @beginTime and @endTime to suit your requirements and execute the query.

Please consider the impact running these queries might have in your environment depending on your amount of QoE data and the date range you select.

Summary

I hope you can use the queries to get an understanding of your network health expressed by the packet loss rate and max packet loss rate experienced during Lync audio calls.

Notes

As can be seen from Table 2 above the queries all utilize the location and connection type. For some audio streams these columns can contain NULL values. In order to make sure that the Total query is the actual sum of the other queries, it only reports streams, where these columns have the values used by the other queries. That means streams with NULL values for location and connection type are not surfaced by the queries.

The queries are not handling streams over VPN separately.

It is not possible to run multiple of the queries in the same Query window in SQL Management Studio.

The queries are developed using the Lync 2013 QoE Schema.

(Updated Sep 11, 2013 to be in line with http://blogs.technet.com/b/jenstr/archive/2013/09/11/how-to-determine-network-connection-type-in-qoe-queries.aspx)