[This is pre-release documentation and subject to change in future releases. This topic's current status is: Milestone-Ready]

Topic Last Modified: 2010-03-26

This section contains sample queries for the QoE database.

Use the following example to get 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 – communicator
inner join UserAgent uaCallee
   on s.CalleeUserAgent = uaCallee.UserAgentKey
	and uaCallee.UAType = 4 -- communicator

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