Export (0) Print
Expand All

Archiving Database Schema

Communications Server 2007 R2

Topic Last Modified: 2009-03-11

This section describes the Archiving Server database schema for Office Communications Server 2007 R2. The schema is subject to change in future releases.

The database schema consists of the following 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.

ContentTypes

Stores the IM content types used in sessions captured in this database.

Dialogs

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

Pools

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

Users

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

Table Description

Conferences

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

ConferenceMessageRecipientList

For each message sent in a conference, stores a list of recipients.

ConferenceMessages

Archives the content of all the messages sent in a conference.

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.

Messages

Archives the content of all the messages sent in one-on-one IM sessions.

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

Table Description

DbConfigDateTime

For internal use only.

DbConfigInt

For internal use only.

DbErrorMessage

For internal use only.

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

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.

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.

The ContentTypes table is a supporting table that stores information about the various types of IM content.

Column Data Type Key/Index Details

ContentTypeId

int

Primary

Unique number identifying this IM content type.

ContentType

nvarchar(256)

 

Content type name (for example, text/plain, text/rtf) or a MIME type.

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))

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

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)

 

 

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 Archiving 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

Unique number indentifying the pool on which the message is captured, reference to Pools 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.

Each record in this table represents one combination of IM conference message and recipient. A message that is sent to multiple recipients generates one record for each recipient.

Column Data Type Key/Index Details

MessageId

int

Primary

Unique number identifying this message in an IM conference.

SessionIdTime

datetime

Primary, Foreign

Time that the conference request was captured by the Archiving agent.

SessionIdSeq

int

Primary, Foreign

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

UserId

Int

Primary, Foreign

Unique number identifying this user, referenced from the Users table.

Date

datetime

 

Message captured time.

This table archives all messages sent in IM Conferences. Each record represents one message.

Column Data Type Key/Index Details

MessageId

uniqueidentifier

Primary

GUID identifying this message.

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.

Date

datetime

 

 

FromId

int

Foreign

UserId of the message sender, referenced from the Users table.

ContentTypeId

Int

Foreign

IM content type of this message. Referenced from the ContentTypes table.

ComputerId

int

Foreign

Id of the Front End Server used for this message. Referenced from the Computers table.

Body

ntext

 

Content of the message body.

Reserved1

tinyint

 

Reserved for Microsoft use.

Reserved2

tinyint

 

Reserved for Microsoft use.

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.

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.

SessionStartedById

int

Foreign

Id of the user who started the session, referenced from the Users table.

ComputerId

Int

Foreign

Id of the Front End Server used for this session.

PoolId

Int

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.

InviteTime

datetime

 

 

ResponseTime

datetime

 

 

ResponseCode

Int

 

SIP response code to the session invitation.

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.

This table archives all messages sent during one-to-one IM sessions. Each record represents one message.

Column Data Type Key/Index Details

MessageIdTime

datetime

Primary

Time the message was sent.

MessageIdSeq

int

Primary

ID number to identify the message. Used in conjunction with MessageIdTime to uniquely identify a message. *

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.

FromId

int

Foreign

UserId of the user sending the message, referenced from the Users table.

Told

int

Foreign

UserId of the user receiving the message, referenced from the Users table.

ContentTypeId

int

Foreign

Unique number identifying this IM content type, referenced from the ContentTypes table.

ComputerId

int

Foreign

Id of the Front End Server used for this message, referenced from the Computers table.

Body

ntext

 

Content of the message body.

Toast

bit

 

TRUE is this message was a toast message.

ContextNote

Bit

 

TRUE is this message was a context note.

Reserved1

tinyint

 

Reserved for Microsoft use.

Reserved2

tinyint

 

Reserved for Microsoft use.

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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft