Topic Last Modified: 2010-11-09

This section contains sample queries for the Quality of Excellence (QoE) database.

Use the following example to get the jitter and packet loss average for all audio streams.

Copy Code 
select avg(cast(JitterInterArrival as bigint)) as JitterAvg, avg(PacketLossRate) as PacketLossRateAvg from AudioStream

Use the following example to find the total numbers of conferences that used Meeting Console.

Copy Code 
select avg(ConversationalMOS)
from Session s
inner join MediaLine m
on s.ConferenceDateTime = m.ConferenceDateTime
   and s.SessionSeq = m.SessionSeq
   and m.MediaLineLabel = 0 -- audio media line
inner join UserAgent uaCaller
   on s.CallerUserAgent = uaCaller.UserAgentKey
	and uaCaller.UAType = 4 - Lync
inner join UserAgent uaCallee
   on s.CalleeUserAgent = uaCallee.UserAgentKey
	and uaCallee.UAType = 4 -- Lync

Use the following example to get ConversstionalMOS, SendingMOS and ListendingMOS per capture device.

Copy Code 
select t.DeviceName as Device, count(*) as SampleNum, avg(ConversationalMOS) as ConversationalMOS, avg(SendListenMOS) SendingMOS, avg(RecvListenMOS) as ListendingMOS
from
(
   select d.DeviceName, m.ConferenceDateTime, m.SessionSeq, a.StreamID, m.ConversationalMOS,a.SendListenMOS, a.RecvListenMOS
   from MediaLine m
   inner join AudioStream a
   on m.ConferenceDateTime = a.ConferenceDateTime
	and m.SessionSeq = a.SessionSeq
	and m.MediaLineLabel = 0
   inner join Device d
	on m.CallerCaptureDev = d.DeviceKey
		 and d.DeviceType = 1
   union
   select d.DeviceName, m.ConferenceDateTime, m.SessionSeq, a.StreamID, m.ConversationalMOS,a.SendListenMOS, a.RecvListenMOS
   from MediaLine m
   inner join AudioStream a
   on m.ConferenceDateTime = a.ConferenceDateTime
	and m.SessionSeq = a.SessionSeq
	and m.MediaLineLabel = 0
   inner join Device d
	on m.CalleeCaptureDev = d.DeviceKey
		 and d.DeviceType = 1
)as t
group by t.DeviceName
order by SampleNum desc