This section contains sample queries for the QoE database.

To get jitter and packet loss average for all audio stream  

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

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 – communicator
inner join UserAgent uaCallee
   on s.CalleeUserAgent = uaCallee.UserAgentKey
	and uaCallee.UAType = 4 -- communicator

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