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.
The media paths from the Network Guide are:
I wanted to include these additional media paths:
Having described which media paths are interesting let us look at the queries themselves.
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.
Trending Poor Streams between AVMCU and Mediation Server
Table 1. Sample output
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:
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
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.
Between conferencing servers and mediation servers
Trending Poor Streams between Mediation Server and Gateway
Between mediation servers and IP PSTN gateways
Trending Poor Streams External
External users talking to internal or external end-points
External Client UA Types
Trending Poor Streams Wired Subnets
Between internal Lync desktop users and conferencing and mediation servers using a wired connection
Wired Client UA Types
1 (MS), 2 (AVMCU)
Trending Poor Streams Wireless Subnets
Between internal users using a wireless connection talking to conferencing and mediation servers
Wireless Client UA Types
Trending Poor Streams Wired P2P
Internal users talking to each other both using a wired connection
Trending Poor Streams Wireless P2P
Internal users talking to each other both using a wireless connection
Trending Poor Streams Wired Other
Wired users talking to wireless users + other wired streams
Other Server UA Types
2 (AVMCU), 1 (MS)
NULL UA Types
Trending Poor Streams Wireless Other
Wireless users talking to wired users + other wireless streams
Wired or Wireless
Inside or Outside
Table 2. Query definitions
The UAType groups I am using above are defined in Table 3 below.
OC, OCPHONE, LMC, Mac Messenger, ATTENDANT, LYNCIMM, LWA, UCWA
4, 8, 16, 64, 128, 16403, 16405, 16411
OC, LMC, Mac Messenger, ATTENDANT, WPLync, iPhoneLync, AndroidLync, iPadLync, NokiaLync, LYNCIMM, LWA, UCWA
4, 16, 64, 128, 16398, 16399, 16400, 16401, 16402, 16403, 16405, 16411
OC, OCPHONE, LMC, Mac Messenger, ATTENDANT, WPLync, iPhoneLync, AndroidLync, iPadLync, NokiaLync, LYNCIMM,LWA, UCWA
4, 8, 16, 64, 128, 16398, 16399, 16400, 16401, 16402, 16403, 16405, 16411
VPN Client UA Types
OC, LMC, ATTENDANT, LYNCIMM, LWA, UCWA
4, 16, 128, 16403, 16405, 16411
CAS, CAA, RGS, CPS, RGS AS, ExUM
256, 512, 1024, 1032, 1040, 16393
NULL UA Types (examples seen at customer sites)
LifeSize Room, Polycom HDX, Polycom RMX, MCX Service, ATS, CAS/CAA Non English, WLM, Lync Meeting Room
Table 3. UAType groups
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.
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.
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)
what about the media paths between desktop clients & and IP PSTN gateways (Mediabypass) ?
Jens>Great suggestion! You can add those streams by adding 32769 to the CallerUAType and CalleeUAType statements in the subnet queries, i.e. they will look like below for the wired query.
CallerUAType in (SELECT UAType FROM @wiredClientUATypes) and CalleeUAType in (1,2,32769)
CallerUAType in (1,2,32769) and CalleeUAType in (SELECT UAType FROM @wiredClientUATypes)