This section outlines the schema for the CDR database.

List of Tables

The database schema consists of the following tables.

Static Tables

Table Description

MediaList

Stores the list of media types that can generate entries in the database (for example, IM, audio, video, and file transfer).

Roles

Stores the list of possible conference roles (for example, attendee and presenter).

UserAuthTypes

Stores the list of possible user authentication type (for example, enterprise, federated, PIC and anonymous).

Supporting Tables

Table Description

ClientVersions

Stores the clients (both client type and version number) of each client involved in a call with information captured in this database.

Computers

Stores the name of each computer that hosts a Front End Server.

Dialogs

Stores information about the DialogID for each peer-to-peer session in the database.

Gateways

Stores a list of Mediation Servers that are used for VoIP calls.

Pools

Stores the names of pool on which IM messages are captured.

Phones

Stores all the phone numbers used in VoIP calls that were archived or whose call details were recorded.

Mcus

Stores information about the various conferencing servers and their URIs.

Users

Stores the user URIs of users who have participated in sessions recorded or archived in this database.

Tables Specific to Conference CDR Records

Table Description

Conferences

Stores information about all conferences that were archived or whose details were recorded, including ConferenceURI, and start and end time.

FocusJoinsAndLeaves

Stores information about conference joins and leaves, including users’ role and client version.

McuJoinsAndLeaves

Stores information about conferencing servers that are involved in a conference, and the user join and leave times.

Tables for Messages in IM Conferences

Table Description

ConferenceMessageCount

For each IM conference, stores the number of messages that were sent by each user.

Tables for Peer-to-Peer Sessions

Table Description

SessionDetails

Stores information about every peer-to-peer session, including start and end time, user ID, response code, and message count for each user.

FileTransfers

Stores information about file transfer sessions, including file name and result (accepted, rejected, or cancelled).

Media

Stores information about the different media types involved in peer-to-peer sessions.

Table for VoIP Call Details

Table Description

VoIPDetails

For each two-party VoIP/PSTN call, stores information about the call (for example, the phone ID of VoIP phone, gateway used, and which party disconnected). Refers to the SessionDetailstable for call start/end times and response code.

Note:
If one party on a call is a VoIP user or if a Mediation Server was involved in the call, a record will be created in this table. Information about VoIP/VoIP calls not involving a PSTN phone is captured in the SessionDetailstable.

Tables for Troubleshooting

Table Description

Application

Stores information about various processes within Office Communications Server that are involved in routing and connections.

ErrorDef

Stores information about types of errors and their definitions.

ErrorReport

Stores information about errors that have occurred.

ProgressReport

Stores information about the progress reports of various steps involved in Office Communications Server processes.

The tables in the following list are used internally by Office Communications Server; their details are not described in this document.

Tables for Internal Use by Office Communications Server

Table Description

DbConfigDateTime

For internal use only.

DbConfigInt

For internal use only.

DbErrorMessage

For internal use only.

Table Details

This section details the columns in each of the CDR database schema tables.

MediaList Table

The MediaList table is a static table that stores the list of various media types.

Column MediaId Media

Data Type

tinyint

nvarchar(256)

Key/Index

Primary

 

Static Values

1

IM

 

2

File Transfer

 

3

Remote Assistance

 

4

Application Sharing

 

5

Audio

 

6

Video

 

7

App Invite

 

8

Meeting

 

9

Phone

Roles Table

The Roles table is a static table that stores the list of possible conference roles, such as attendee and presenter.

Column RoleId Role

Data Type

tinyint

nvarchar(256)

Key/Index

Primary

 

Static Values

0

Unknown

 

1

Presenter

 

2

Attendee

UserAuthTypes Table

The UserAuthTypes table is a static table that stores the list of possible user authentication types, such as enterprise, federated, Public IM Connectivity (PIC), and anonymous.

Column AuthTypeId AuthType

Data Type

int

nvarchar(256)

Key/Index

Primary

 

Static Values

0

Unknown

 

1

Enterprise

 

2

Federated

 

3

Anonymous

 

4

Public IM Connectivity

ClientVersions Table

The ClientVersions table is a supporting table that stores a list of the various client types and versions that have participated in sessions recorded in the database. Each record in the table represents one client version.

Column Data Type Key/Index Details

VersionId

int

Primary

Unique number identifying this client type and version.

Version

nvarchar(256)

 

Version name.

Computers Table

The Computers table is a supporting table that stores information about the various Front End Servers. Each record in the table represents one Front End Server.

Column Data Type Key/Index Details

ComputerId

int

Primary

Unique number identifying this Front End Server.

Computer

nvarchar(16)

 

Front End Server host name.

Pools Table

The Pools table is a supporting table that stores information about the various Pools. Each record in the table represents one Pool

Column Data Type Key/Index Details

PoolId

int

Primary

Unique number identifying this Pool.

PoolFQDN

nvarchar(256)

 

Pool FQDN.

Dialogs Table

The Dialogs table is a supporting table that stores the information about DialogIds for peer-to-peer sessions.

Column Data Type Key/Index Details

DialogId

int

Primary

Unique number identifying this SIP dialog instance.

ExternalChecksum

Int

 

Checksum of the ExternalId. This field is used to increase the speed of database searches.

ExternalId

varbinary(775)

 

SIP dialog Id, stored as a binary. The format of the binary is:

dialog;from-tag;to-tag

This data can be converted to text format by using: cast(cast(ExternalId as varbinary(max)) as varchar(max))

Gateways Table

The Gateways table is a supporting table. Each record stores information about one Mediation Server that is involved in calls that have records in the database.

Column Data Type Key/Index Details

GatewayId

int

Primary

Unique number identifying this Mediation Server.

Gateway

nvarchar(256)

 

Mediation Server name.

Mcus Table

The Mcus table is a supporting table; each record stores the information about one conferencing server. These can include the IM Conferencing Server and the Telephony Conferencing Server (which run as processes on Front End Servers), and the Web Conferencing Server and A/V Conferencing Server.

Column Data Type Key/Index Details

McuId

int

Primary

Unique number identifying this MCU server.

McuUri

nvarchar(450)

 

 

McuType

nvarchar(256)

 

MCU type (for example, chat(for IMs) or audio-video).

Users Table

The Users table is a supporting table; each record in the table stores information about one user involved in calls or sessions that have records in the database.

Column Data Type Key/Index Details

UserId

int

Primary

Unique number identifying this user.

UserUri

nvarchar(450)

 

 

AuthTypeId

Int

Foreign

Unique number identifying this user’s authentication type. Reference to UserAuthTypes table.

Phones Table

The Phones table is a supporting table; each record in the table stores information about one phone number involved in VoIP calls that have records in the database.

Column Data Type Key/Index Details

PhoneId

int

Primary

Unique number identifying this phone.

PhoneUri

nvarchar(450)

 

 

Conferences Table

Each record in this table contains call details about one conference.

Column Data Type Key/Index Details

ConferenceUri

nvarchar(450)

 

 

Checksum

Int

 

Checksum of ConferenceURi; used to increases the speed of database searches.

ConfInstance

Int

 

Useful for recurring conferences; each instance of a recurring conference has the same ConferenceUri, but will have a different ConfInstance.

SessionIdTime

datetime

Primary

Time that the conference request was captured by the CDR agent. Used only as a primary key to uniquely identify a session.

SessionIdSeq

int

Primary

ID number to identify the session. Used in conjunction with SessionIDTimeto uniquely identify a session. *

ConferenceStartTime

datetime

 

 

ConferenceEndTime

datetime

 

 

PoolId

Int

Foreign

ID number to identify the pool in which the conference was captured. Reference to Pools table.

OrganizerId

Int

Foreign

ID number to identify the organizer URI of this conference. Reference to Users table.

* For most sessions, SessionIdSeq will have the value of 1. If two sessions start at exactly the same time, the SessionIdSeq for one will be 1, and for the other will be 2, and so on.

FocusJoinsAndLeaves Table

Each record in this table contains the CDR information about one user’s join and leave information for one conference. Each conference is represented in this table by one record for each time a user joins and leaves the conference.

Column Data Type Key/Index Details

UserId

int

Primary, Foreign

Unique number identifying this user, referenced from the Userstable.

UserInstance

Int

Primary

If a user is logged on at multiple computers or devices at once, UserInstance is used to uniquely identify the user/device combination.

IsUserInternal

Bit

 

Whether the user logged on from internal or not.

UserRole

Int

 

This user’s role in the conference.

SessionIdTime

datetime

Primary, Foreign

Time of session request; used in conjunction with SessionIDSeqto uniquely identify a session. Referenced from the Conferencestable.

SessionIdSeq

int

Primary, Foreign

ID number to identify the session. Used in conjunction with SessionIDTimeto uniquely identify a session. Referenced from the Conferencestable.

UserJoinTime

datetime

 

 

UserLeaveTime

datetime

 

 

ClientVerId

int

Foreign

Version of the user’s client software, referenced from the ClientVersionstable.

McuJoinsAndLeaves Table

Each record in this table contains call details about one combination of a user join or leave and MCU device. For example, if a user joins a conference that includes Web conferencing and audio/video elements, one record would be created for that user’s Web conferencing join, and another record would be created for the user’s audio/video join.

Column Data Type Key/Index Details

UserId

int

Primary, Foreign

Unique number identifying this user, referenced from the Userstable.

UserInstance

Int

Primary

If a user is logged on at multiple computers or devices at once, UserInstance uniquely identifies the user/device combination.

IsFromPstn

Bit

 

Whether the user is joining from PSTN or not.

McuId

Int

Primary, Foreign

Unique number identifying this MCU device, referenced from the Mcustable.

SessionIdTime

datetime

Primary, Foreign

Time of session request; used in conjunction with SessionIDSeqto uniquely identify a session. Referenced from the Conferencestable.

SessionIdSeq

int

Primary, Foreign

ID number to identify the session. Used in conjunction with SessionIDTimeto uniquely identify a session. Referenced from the Conferencestable.

UserJoinTime

datetime

 

 

UserLeaveTime

datetime

 

 

ConferenceMessageCount Table

Each record in this table represents one user in one IM conference and includes the number of messages sent by that user. Each conference is represented by multiple records in this table; one record for each user.

Column Data Type Key/Index Details

SessionIdTime

datetime

Primary, Foreign

Time of session request; used in conjunction with SessionIDSeqto uniquely identify a session. Referenced from the Conferencestable.

SessionIdSeq

int

Primary, Foreign

ID number to identify the session. Used in conjunction with SessionIDTimeto uniquely identify a session. Referenced from the Conferencestable.

UserId

int

Primary, Foreign

Unique number identifying this user, referenced from the Userstable.

MessageCount

smallint

 

The number of messages sent by this user during this conference.

SessionDetails Table

Each record represents one peer-to-peer session, which could be a VoIP-VoIP phone call, 2-party IM session, or other type of session. To find the modalities used during a session, you must do a table join with the Mediatable. Session type is not stored in the SessionDetails table.

Column Data Type Key/Index Details

SessionIdTime

datetime

Primary

Time of session request; used in conjunction with SessionIDSeqto uniquely identify a session.

SessionIdSeq

int

Primary

ID number to identify the session. Used in conjunction with SessionIDTimeto uniquely identify a session. *

DialogId

Int

Foreign

SIP dialog ID, referenced from the Dialogstable.

CorrelationId

uniqueidentifier

 

A GUID to correlate multiple sessions.

ReplaceDialogId

Int

Foreign

ID number to identify the dialog which was replaced by current session. Reference to Dialogs table

User1Id

Int

Foreign

Id of one user in the session, referenced from the Userstable.

User2Id

int

Foreign

Id of the other user in the session, referenced from the Userstable.

TargetUserId

Int

 

The original To user URI in SIP request. Reference to Users table.

SessionStartedById

int

Foreign

Id of the user who started the session, referenced from the Userstable.

OnBehalfOfId

Int

 

Indicate the Id of the user of who the caller is on behalf. Reference Users table.

ReferredById

Int

Foreign

Id of the user by who the call is referred.

ComputerId

Int

Foreign

Id of the Front End Server used for this session.

PoolId

 

Foreign

Id of the Pool in which the session was captured.

User1ClientVerId

Int

Foreign

Client version used by User1, referenced from the ClientVersionstable.

User2ClientVerId

int

Foreign

Client version used by User2, referenced from the ClientVersionstable.

IsUser1Internal

Bit

 

Whether user1 is logged on from internal or not.

IsUser2Internal

Bit

 

Whether user2 is logged on from internal or not.

InviteTime

datetime

 

 

ResponseTime

datetime

 

 

ResponseCode

Int

 

SIP response code to the session invitation.

DianosticId

Int

 

Diagnostic Id captured from SIP header.

User1MessageCount

Int

 

Number of messages sent by User1 during the session.

User2MessageCount

Int

 

Number of messages sent by User2 during the session.

SessionEndTime

datetime

 

 

* For most sessions, SessionIdSeq will have the value of 1. If multiple sessions start at exactly the same time, the SessionIdSeq for one will be 1, for another will be 2, and so on.

FileTransfers Table

Each record represents one file transfer session.

Column Data Type Key/Index Details

SessionIdTime

datetime

Primary, Foreign

Time of session request; used in conjunction with SessionIDSeqto uniquely identify a session.

SessionIdSeq

int

Primary, Foreign

ID number to identify the session. Used in conjunction with SessionIDTimeto uniquely identify a session.

FileName

nvarchar(256)

 

 

Cookie

int

Primary

Random number between 1 and 4,294,967,295 (2^32 - 1)). Used to identify every follow-up message as being associated with this one.

Accept

bit

 

Can be TRUE or NULL. If TRUE, then Reject and Cancel will be NULL.

Reject

bit

 

Can be TRUE or NULL. If TRUE, then Accept and Cancel will be NULL.

Cancel

bit

 

Can be TRUE or NULL. If TRUE, then Accept and Reject will be NULL.

bit

Media Table

Each record represents one media type used in a peer-to-peer session. One session would be represented by multiple records in the table, if more than one media type is used.

Column Data Type Key/Index Details

SessionIdTime

datetime

Primary, Foreign

Time of session request; used in conjunction with SessionIDSeqto uniquely identify a session.

SessionIdSeq

int

Primary, Foreign

ID number to identify the session. Used in conjunction with SessionIDTimeto uniquely identify a session.

MediaId

tinyint

Primary, Foreign

Unique number identifying this media type, referenced from the MediaListtable.

StartTime

datetime

Primary

 

EndTime

datetime

 

 

VoipDetails Table

Each record represents one two-party call in which at least one user is a VoIP user.

Column Data Type Key/Index Details

SessionIdTime

datetime

Primary, Foreign

Time of session request; used in conjunction with SessionIDSeqto uniquely identify a session.

SessionIdSeq

int

Primary, Foreign

ID number to identify the session. Used in conjunction with SessionIDTimeto uniquely identify a session.

FromNumberId

int

Foreign

PhoneIdof the caller, referenced from the Phonestable. If NULL, the caller was a PSTN user.

ConnectedNumberId

int

Foreign

PhoneIdof the call receiver, referenced from the Phonestable. If NULL, the receiver was a PSTN user.

FromGatewayId

int

Foreign

Mediation Server the call is coming from, referenced from the Gatewaystable.

ToGatewayId

Int

Foreign

Mediation Server called is going to, reference to Gateways table.

DisconnectedbyURIId

int

Foreign

URI of the user who disconnected the call, if the user has a URI. Referenced from the Userstable.

DisconnectedbyPhoneId

int

Foreign

ID of the phone that disconnected the call if the call was disconnected from a phone. Referenced from the Phonestable.

Application Table

This table stores information about the various processes within Office Communications Server involved in routing and connections.

Column Data Type Key/Index Details

ApplicationId

int

Primary

Unique number identifying this application.

Name

nvarchar(257)

 

 

ErrorDef Table

This table stores information about each type of error that may occur. Each record is one type of error.

ErrorDef Table

Column Data Type Key/Index Details

ErrorId

int

Primary

Unique ID number identifying this type of error.

ResponseCode

int

 

Standard SIP response code associated with this error.

MsDiagId

int

 

Microsoft Diagnostic ID.

RequestType

varbinary(33)

 

Type of request that failed.

This data can be converted to text format by using: cast(cast(RequestType as varbinary(max)) as varchar(max))

ContentType

varbinary(257)

 

Content type of the request that failed.

This data can be converted to text format using: cast(cast(ContentType as varbinary(max)) as varchar(max))

ErrorReport Table

This table stores information about errors that have occurred. Each record is one error occurrence.

Column Data Type Key/Index Details

ErrorTime

datetime

Primary

Date and time the error occurred.

ErrorId

int

Primary, Foreign

Unique ID of the error type, referenced from the ErrorDeftable.

FromUserId

int

Foreign

User who originated the request that caused the error. Referenced from the Userstable.

ToUserId

int

Foreign

Destination user for the request that caused the error. Referenced from the Userstable.

DialogId

int

Foreign

Referenced from the Dialogstable.

MsDiagHeader

image

 

More information about the error.

This data can be converted to text format using: cast(cast(Detail as varbinary(max)) as varchar(max))

ProgressReport Table

Progress reports are based on data uploaded by the client to the database after a call or session is completed. Progress reports will be written only for calls and sessions that Office Communications Server determines might be useful for diagnostic purposes.

The ErrorTime and ErrorId fields do not necessarily refer to errors but to messages that indicate the status of calls or messages.

Column Data Type Key/Index Details

ErrorTime

datetime

Primary, Foreign

Date and time of the progress report.

ErrorId

int

Primary, Foreign

Unique ID of the error type, referenced from the ErrorDeftable.

ProgressReportSeq

int

Primary

ID number to identify the progress report. Used in conjunction with ErrorTime to uniquely identify a session.

ApplicationId

int

Foreign

The Office Communications Server process that the report is about. Referenced from the Applicationtable.

Detail

image

 

Progress report details, stored in binary format to save space.

This data can be converted to text format using: cast(cast(Detail as varbinary(max)) as varchar(max))

bit

Sample Database Queries

This section contains sample queries for the CDR database. The CDR Reporter tool in the Office Communications Server 2007 Resource Kit has more.

To find the total number of PSTN to UC Calls:  

Copy Code
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 GatewayId is not null

To find the total numbers of conferences that used Meeting Console:

Copy Code
SELECT count(distinct(c.ConferenceUri)) as DataMCU Conferences from
 as mj inner join as m on (m.McuId = mj.McuId) inner join  as c on
(c.SessionIdTime = mj.SessionIdTime and c.SessionIdSeq =
mj.SessionIdSeq) where m.McuType=meeting
RequestType = PKCS10  

To find the total number of redirected calls:

Copy Code
SELECT count(*) as Number of Redirected Calls from VoipDetails
where ReferredById is not null  

See Also