Sample Database Queries

Microsoft Office Communications Server 2007 and Microsoft Office Communications Server 2007 R2 will reach end of support on January 9, 2018. To stay supported, you will need to upgrade. For more information, see Resources to help you upgrade your Office 2007 servers and clients.

This section contains sample queries for the QoE database.

To get jitter and packet loss average for all audio stream  

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

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

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