Archiving Database Schema
Dernière rubrique modifiée : 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.
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: |
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 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.
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 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. |
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 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. |
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. |
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.
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 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.