Diagnosing network health issues using QoE data

In this I discussed how to view network health trends using QoE data. In this post I'll describe some queries you can use to help you diagnose where you might have network health issues in your Lync deployment.

The first query is the Streams Statistics query. The query will tell you the maximum, standard deviation and average for PacketLossRate and PacketLossRateMax across all your audio streams. This will give you an idea where the health is compared to the poor streams definition of packet loss rate over 1 percent and a maximum packet loss rate over 5 percent. An example of the output of the query is shown in Table 1.

NumberOf Streams	Numberof PoorStreams	Numberof ClassifiedPoorCalls	Max PacketLossRate	Std PacketLossRate	Avg PacketLossRate	Max PacketLossRateMax	Std PacketLossRateMax	Avg PacketLossRateMax
134	52	8	0.3421	0,050488	0.027311	0.9988	0,340357	0.236500

Table 1. Sample output from Streams Statistics query

Next you'll probably want to dive into the different media paths to investigate where the poor streams are coming from. I'll use the media path between conferencing and mediation servers in the following examples.

To understand more about the streams between conferencing and mediation servers you can use the Poor Streams between Conferencing and Mediation Servers query. The query will tell you per day the number of streams, number of poor streams and the poor streams ratio for all streams between conferencing and mediation servers. An example of the output of the query is shown in Table 2.

ReportDate	AVMCU	MS	AllStreams	PoorStreams	PoorStreamsRatio
08-05-2013	FE1	FE1	234	22	9.4
09-05-2013	FE2	FE3	386	158	40.9
10-05-2013	FE5	MS3	428	80	18.7
11-05-2013	FE4	MS2	784	56	7.1

Table 2. Sample output from Poor Streams between Conferencing and Mediation Servers query.

Having seen the different pairs of servers you might want to dive into the individual streams between two specific conferencing and mediation server, i.e. between FE2 and FE3 in the example on Table 2. To do that you can use the Individual Poor Streams between Two Servers query. The query will list a number of media related values from all streams between the two servers you specify in the query.

Conference DateTime	Duration InSeconds	Caller Server	Callee Server	PacketLoss Rate	PacketLoss RateMax	JitterInter Arrival	JitterInter ArrivalMax	Round Trip	Round TripMax	Degradation Avg	RatioConcealed SamplesAvg	Packet Utilization	Burst Density	Burst Duration	IsPoor Stream	Classified PoorCall
17-05-2013	12	FE2	FE3	0.0538	0.6453	3	5	1	19	0.07	0.00	187	0.0000	0	1	0
17-05-2013	4	FE3	FE2	0.0040	0.0045	1	3	1	17	NULL	NULL	53	NULL	NULL	0	0

Table 3. Sample output from Individual Poor Streams between Two Servers

I hope that you based on these sample queries can develop your own diagnostics queries to gain further understanding of potential causes of poor network health.

Running the queries

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

Each query is in 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. For the query between two servers you need to replace the placeholder server name with the real names from your deployment in capital letters.

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.

Notes

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

Queries are developed using the Lync 2013 QoE schema.

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

DiagnosticsQueries.zip

Comments

• Anonymous
  February 13, 2014
  Thanks, this is very useful. As you seem to be very familiar with the inner workings of the QoE data, can you tell me if there is any API available for importing Lync-to-other stream data? For instance, our Lync mediation server routes calls to both Call Manager and our SBC. Particularly in the case of the SBCs, they have a wealth of call quality data about the over-the-internet leg (to carriers). I would like to be able to import some of that into Lync QoE so that our support staff can see even more of the call quality info in one report. Is there an avenue for this? Jens>Thanks. The QoE protocol is defined here http://msdn.microsoft.com/en-us/library/cc431512(v=office.12).aspx. That might be an option to take, but it see this as being done by the SBC vendor as part of their product. An alternative would be to create your own database with tables and dump the information there. You can then reference those tables from custom SQL queries against QoE data.