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.
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.
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.
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.
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.
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 http://blogs.technet.com/b/jenstr/archive/2013/09/11/how-to-determine-network-connection-type-in-qoe-queries.aspx)
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.