示例 CDR 数据库查询

上一次修改主题： 2010-12-14

本节包含呼叫详细信息记录 (CDR) 数据库的示例查询。

使用以下示例可获得给定用户组织的特定时间范围内的会议参与者。

declare @Organizer nvarchar(256)
declare @StartTime datetime
declare @EndTime datetime
 
set @Organizer = 'luka@contoso.com'
set @StartTime = DATEADD(d, -1, getdate())
set @EndTime = GETDATE()
 
--user join/leave information for signalling
select cu.ConferenceUri, u.UserUri, cv.Version, fjl.UserJoinTime, fjl.UserLeaveTime, r.Role, fjl.IsUserInternal from FocusJoinsAndLeaves fjl 
inner join Conferences c on fjl.SessionIdTime = c.SessionIdTime and fjl.SessionIdSeq = c.SessionIdSeq
inner join ConferenceUris cu on c.ConferenceUriId = cu.ConferenceUriId
inner join Users u on u.UserId = fjl.UserId
left join Users org on org.UserId = c.OrganizerId
left join ClientVersions cv on cv.VersionId = fjl.ClientVerId
left join Roles r on r.RoleId = fjl.UserRole
where org.UserUri = @Organizer and fjl.SessionIdTime between @StartTime and @EndTime
 
--User join/leave information for different conference modalities 
select cu.ConferenceUri, ut.UriType, u.UserUri, mjl.UserJoinTime, mjl.UserLeaveTime, mjl.IsFromPstn
from McuJoinsAndLeaves mjl 
inner join Conferences c on mjl.SessionIdTime = c.SessionIdTime and mjl.SessionIdSeq = c.SessionIdSeq
inner join ConferenceUris cu on c.ConferenceUriId = cu.ConferenceUriId
inner join Users u on u.UserId = mjl.UserId
left join Users org on org.UserId = c.OrganizerId
left join Mcus m on m.McuId = mjl.McuId

使用以下示例可查找用于统一通信 (UC) 呼叫的公用电话交换网 (PSTN) 的总数：

Select Count(*) as 'Number of PSTN to UC Calls' 
From VoipDetails as voipd 
Join SessionDetails as sd on (voipd.SessionIdTime = sd.SessionIdTime and voipd.SessionIdSeq = sd.SessionIdSeq and sd.User1Id is null)  
and FromNumberId in (SELECT PhoneId from Phones) 
and FromGatewayId  is not null

使用以下示例可查找使用会议控制台的会议的总数：

select COUNT(*) as 'Live Meeting Count' from
(
SELECT distinct c.SessionIdTime, c.SessionIdSeq
from McuJoinsAndLeaves as mj 
inner join Mcus m on m.McuId = mj.McuId
inner join Conferences c on c.SessionIdTime = mj.SessionIdTime and c.SessionIdSeq = mj.SessionIdSeq
inner join UriTypes ut on ut.UriTypeId = m.McuTypeId
where ut.UriType= 'conf:data-conf'
) tmp

使用以下示例可查找重定向呼叫的总数：

select COUNT(*) as 'Number of Redirected Calls' from SessionDetails
where ReferredById is not null