실시간 모임 참석자 정보 쿼리

use rtcdyn

 

-- 모임 참석자 리스트 쿼리
select FE.Fqdn, P.ConfId, P.UserAtHost, RC.ProvisionTime, P.JoinTime, RC.ExpiryTime from Participant P with(nolock)
Join ParticipantSignalingSession PS with(nolock) ON PS.ConfId = P.ConfId and P.PartId = PS.PartId
Join [rtc].[dbo].[Conference] RC with(nolock) ON RC.ConfId = P.ConfId
Join FrontEnd FE with(nolock) ON FE.FrontEndId = PS.FrontEndId
Order by RC.ProvisionTime, P.ConfId, FE.Fqdn

-- 모임 F/E서버 및 모임방ID별 참석자 수
select FE.Fqdn, P.ConfId, Count(*) as Join_Cnt, RC.ExpiryTime from Participant P with(nolock)
Join ParticipantSignalingSession PS with(nolock) ON PS.ConfId = P.ConfId and P.PartId = PS.PartId
Join [rtc].[dbo].[Conference] RC with(nolock) ON RC.ConfId = P.ConfId
Join FrontEnd FE with(nolock) ON FE.FrontEndId = PS.FrontEndId
Group By FE.Fqdn, P.ConfId, RC.ExpiryTime

 

select
--ActiveConference
[ActiveConference].[ConfId]
,[Title]
,[LastEnterprisePartLeaveTime]
,'|' as '|'
--ActiveMCU
,[LastActivityTime] MCULastActivityTime
,'|' as '|'
--Conference
,[ProvisionTime]
,[ExpiryTime]
,[LastUpdateTime]
,[LastActivateTime]
,'|' as '|'
--Participant
,[UserAtHost]
,[EnterpriseId]
,[Privilege]
,[JoinTime]
FROM [rtcdyn].[dbo].[ActiveConference]
left outer join [rtcdyn].[dbo].[Participant]
on [Participant].ConfId = [ActiveConference].ConfId
left outer join [rtcdyn].[dbo].[ActiveMcu]
on ActiveMcu.ConfId = ActiveConference.ConfId
and UserAtHost not like 'CAS%'
and MediaId = 4
left outer join [rtc].[dbo].[Conference]
on Conference.ConfId = ActiveConference.ConfId

--where ActiveConference.ConfId in (select ConfId from [rtcdyn].[dbo].[Participant] where UserAtHost like '%dgardiner.test%') --Name of the application/OwnerURI scheduling the conference