How to determine network connection type in QoE queries?

In many scenarios you are interested in knowing how a given caller or callee was connected to the network, i.e. was it a wired or wireless connection. The information is contained in the columns CallerNetworkConnectionType and CalleeNetworkConnectionType in the MediaLine table. It is then easy to use those columns in your SQL queries. However you need to understand what values you can see in those columns and what they mean. To make it a bit more complex there are differences between the Lync 2010 and Lync 2013 QoE schemas on this point.

The Lync 2010 QoE Schema use NetworkConnectionType directly (I'll use this term to refer to both CallerNetworkConnectionType and CalleeNetworkConnectionType) and it can have the integer values 0 for wired and 1 for wireless. You can use those values directly in your SQL queries. For instance if you want to see all audio streams, where both caller and callee were connected via a wired connection, you can use a query like below:

SELECT

        *

FROM [Session] s WITH (NOLOCK)

        INNER JOIN [MediaLine] AS m WITH (NOLOCK) ON

            m.ConferenceDateTime = s.ConferenceDateTime

            AND m.SessionSeq = s.SessionSeq            

        INNER JOIN [AudioStream] AS a WITH (NOLOCK) ON

            a.MediaLineLabel = m.MediaLineLabel

            and a.ConferenceDateTime = m.ConferenceDateTime

            and a.SessionSeq = m.SessionSeq            

WHERE

        (m.CallerNetworkConnectionType = 0 and m.CalleeNetworkConnectionType = 0)

The Lync 2013 QoE Schema introduces a new table call NetworkConnectionDetail to store information about network connection type and the MediaLine columns for NetworkConnectionType are now indexes into this table. The actual network connection type values are stored as strings in the NetworkConnectionDetail column in the new table. Values you'll most likely see are 'wired', 'wifi' and 'Ethernet'. The 'Ethernet' string was introduced with Lync 2013. If you want to get the same list of wired audio streams in Lync 2013, you can use a query like below:

SELECT

        *

FROM [Session] s WITH (NOLOCK)

        INNER JOIN [MediaLine] AS m WITH (NOLOCK) ON

            m.ConferenceDateTime = s.ConferenceDateTime

            AND m.SessionSeq = s.SessionSeq            

        INNER JOIN [AudioStream] AS a WITH (NOLOCK) ON

            a.MediaLineLabel = m.MediaLineLabel

            and a.ConferenceDateTime = m.ConferenceDateTime

            and a.SessionSeq = m.SessionSeq    

        INNER JOIN [NetworkConnectionDetail] AS CallerNcd WITH (NOLOCK) ON

            CallerNcd.NetworkConnectionDetailKey = m.CallerNetworkConnectionType

        INNER JOIN [NetworkConnectionDetail] AS CalleeNcd WITH (NOLOCK) ON

            CalleeNcd.NetworkConnectionDetailKey = m.CalleeNetworkConnectionType

WHERE

        CallerNcd.NetworkConnectionDetail in ('wired','Ethernet')

        and CalleeNcd.NetworkConnectionDetail in ('wired','Ethernet')

So now alarm bells should be going off, when you read the above, because you'll ask yourself: will all my existing queries using the filter directly on the MediaLine columns (as above) have to be changed? Yes and No is the answer J

No, because if you are only testing on 0 and 1, the NetworkConnectionDetail table is pre-populated with two rows: index 0 = 'wired' and index 1 = 'wifi'. This is done for backwards compability reasons.

Yes, you'll have to change your queries, because the 'Ethernet' strings was introduced in Lync 2013 and many components use that to specify a wired connection. For portability between deployments it is also more correct to use the string comparison filter than filtering directly on index values.