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

公衆交換電話網 (PSTN) から統合コミュニケーション (UC) への通話の合計数を調べるには、次の例を使用します。

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