CDR Database Schema

Dernière rubrique modifiée : 2009-03-11

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 SessionDetails table for call start/end times and response code. Remarque : 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 SessionDetails table.

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 SessionIDTime to 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 Users table.
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 SessionIDSeq to uniquely identify a session. Referenced from the Conferences table.
SessionIdSeq	int	Primary, Foreign	ID number to identify the session. Used in conjunction with SessionIDTime to uniquely identify a session. Referenced from the Conferences table.
UserJoinTime	datetime
UserLeaveTime	datetime
ClientVerId	int	Foreign	Version of the user’s client software, referenced from the ClientVersions table.

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 Users table.
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 Mcus table.
SessionIdTime	datetime	Primary, Foreign	Time of session request; used in conjunction with SessionIDSeq to uniquely identify a session. Referenced from the Conferences table.
SessionIdSeq	int	Primary, Foreign	ID number to identify the session. Used in conjunction with SessionIDTime to uniquely identify a session. Referenced from the Conferences table.
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 SessionIDSeq to uniquely identify a session. Referenced from the Conferences table.
SessionIdSeq	int	Primary, Foreign	ID number to identify the session. Used in conjunction with SessionIDTime to uniquely identify a session. Referenced from the Conferences table.
UserId	int	Primary, Foreign	Unique number identifying this user, referenced from the Users table.
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 Media table. 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 SessionIDSeq to uniquely identify a session.
SessionIdSeq	int	Primary	ID number to identify the session. Used in conjunction with SessionIDTime to uniquely identify a session. *
DialogId	Int	Foreign	SIP dialog ID, referenced from the Dialogs table.
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 Users table.
User2Id	int	Foreign	Id of the other user in the session, referenced from the Users table.
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 Users table.
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 ClientVersions table.
User2ClientVerId	int	Foreign	Client version used by User2, referenced from the ClientVersions table.
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 SessionIDSeq to uniquely identify a session.
SessionIdSeq	int	Primary, Foreign	ID number to identify the session. Used in conjunction with SessionIDTime to 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 SessionIDSeq to uniquely identify a session.
SessionIdSeq	int	Primary, Foreign	ID number to identify the session. Used in conjunction with SessionIDTime to uniquely identify a session.
MediaId	tinyint	Primary, Foreign	Unique number identifying this media type, referenced from the MediaList table.
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 SessionIDSeq to uniquely identify a session.
SessionIdSeq	int	Primary, Foreign	ID number to identify the session. Used in conjunction with SessionIDTime to uniquely identify a session.
FromNumberId	int	Foreign	PhoneId of the caller, referenced from the Phones table. If NULL, the caller was a PSTN user.
ConnectedNumberId	int	Foreign	PhoneId of the call receiver, referenced from the Phones table. If NULL, the receiver was a PSTN user.
FromGatewayId	int	Foreign	Mediation Server the call is coming from, referenced from the Gateways table.
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 Users table.
DisconnectedbyPhoneId	int	Foreign	ID of the phone that disconnected the call if the call was disconnected from a phone. Referenced from the Phones table.

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 ErrorDef table.
FromUserId	int	Foreign	User who originated the request that caused the error. Referenced from the Users table.
ToUserId	int	Foreign	Destination user for the request that caused the error. Referenced from the Users table.
DialogId	int	Foreign	Referenced from the Dialogs table.
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 ErrorDef table.
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 Application table.
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:  

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:

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:

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

Voir aussi

Autres ressources

Archiving and Monitoring Drilldown