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

List of Tables

The database schema consists of the following tables.

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.

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.

Tables for Messages in IM Conferences

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.

Tables for Peer-to-Peer IM Archiving

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

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 Archiving database schema tables.

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.

ContentTypes Table

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.

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

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

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)

 

 

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 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 SessionIDTimeto 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.

ConferenceMessageRecipientList Table

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 SessionIDTimeto uniquely identify a session.

UserId

Int

Primary, Foreign

Unique number identifying this user, referenced from the Userstable.

Date

datetime

 

Message captured time.

ConferenceMessages Table

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

Date

datetime

 

 

FromId

int

Foreign

UserId of the message sender, referenced from the Userstable.

ContentTypeId

Int

Foreign

IM content type of this message. Referenced from the ContentTypestable.

ComputerId

int

Foreign

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

Body

ntext

 

Content of the message body.

Reserved1

tinyint

 

Reserved for Microsoft use.

Reserved2

tinyint

 

Reserved for Microsoft use.

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.

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.

SessionStartedById

int

Foreign

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

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 ClientVersionstable.

User2ClientVerId

int

Foreign

Client version used by User2, referenced from the ClientVersionstable.

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.

Messages Table

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 MessageIdTimeto uniquely identify a message. *

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.

FromId

int

Foreign

UserId of the user sending the message, referenced from the Userstable.

Told

int

Foreign

UserId of the user receiving the message, referenced from the Userstable.

ContentTypeId

int

Foreign

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

ComputerId

int

Foreign

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

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.

See Also