This document describes the publicly available portions of the Microsoft SQL Server database schema used by Citrix SmartAuditor. This allows third parties and customers to develop their own reporting and database scripts around the SmartAuditor database.
Some parts of the database schema remain private for Citrix internal use only and may change without notice. Most table definitions are public but stored procedures and functions should not be used. The SmartAuditor database schema must not be modified.
The following diagram illustrates the documented tables, column names, primary keys and foreign key relationships.
SmartAuditor Database
The main purpose of the SmartAuditor database is for cataloging recorded session files for the purpose of searching and locating files. The SmartAuditor Storage Manager component is responsible for inserting and updating records when new recorded data is captured, and the SmartAuditor Broker component retrieves data when necessary. Indexes exist on various columns to ensure fast record matching. All table definitions associated with the cataloging of file metadata are public.
Policy documents used for making policy decisions are also stored in the database. However, these are for Citrix internal use only and are not documented.
The name of the database created within SQL Server by the SmartAuditor Database installer is CitrixSmartAuditor. The schema is compatible with all editions of Microsoft SQL Server 2000 and 2005, including MSDE2000 release A and SQL Server 2005 Express Edition.
Tables
The following sections describe each table definition in alphabetical order. Each table's column is described by its name, data type and whether the field may contain null values. For more information regarding SQL Server data types, refer to Microsoft documentation.
Some general conventions are used across all tables:
- The primary key column in each table, if present, is always named ID.
- The SQL Server data type of the primary key ID field for tables containing non-static data is uniqueidentifier (that is, 128-bit globally unique identifier). This gives each record in the database absolute uniqueness even across different database instances.
- The SQL Server data type of the primary key ID field for tables containing static data is smallint. Each ID value has a specific well-known constant value.
- All columns that contain string data are defined as nvarchar(n) (as opposed to varchar), ensuring all string data is Unicode compatible.
- Columns used as foreign key references to other tables use the naming convention of the foreign table name appended with an ID suffix. For example, the foreign key column in the Client table to the ClientPlatform table is called ClientPlatformID.
- Records with foreign key references still present cannot be deleted. This ensures referential integrity.
AddressFamily
This table contains the static set of available network address family values. This table is preloaded at install-time with values for IP, IPX, NetBIOS, and Unknown.
| Column |
Type |
Null |
Description |
| ID |
smallint |
No |
Primary key row identifier. The following is the list of network address family IDs.
0 = Unknown
1 = IP
2 = IPX
3 = NetBIOS |
| Description |
nvarchar(50) |
No |
Textual description of address family. |
Application
This table contains the list of published application names of sessions that have been recorded to date. Each ICLFile table record references an entry in this table. When a session is recorded for a published application that was not recorded previously, a new Application record is created.
| Column |
Type |
Null |
Description |
| ID |
uniqueidentifier |
No |
Primary key row identifier. |
| Name |
nvarchar(256) |
No |
Name of the published application including application folder hierarchy separated by slashes. For example, Microsoft Office 2007/Excel 2007. |
Client
This table contains details about Presentation Server clients of sessions that have been recorded to date. Each ICLFile table record references an entry in this table. When a session is recorded for a client that was not recorded previously, a new Client record is created. This includes the unique combination of client name, address, network address family, and the type of platform on which the client is running. Note that for sessions established through Citrix Web Interface, the client details identify the Web Interface instance instead of the real end user client.
| Column |
Type |
Null |
Description |
| ID |
uniqueidentifier |
No |
Primary key row identifier. |
| Name |
nvarchar(128) |
No |
Name of the Presentation Server client or Web Interface instance. |
| Address |
nvarchar(128) |
No |
Network address of the Presentation Server client or Web Interface instance. |
| AddressFamilyID |
smallint |
No |
Foreign key reference to the AddressFamily table identifying the type of network address specified in the Address column. |
| ClientPlatformID |
uniqueidentifier |
No |
Foreign key reference to the ClientPlatform table identifying the platform as used by client. |
ClientPlatform
This table contains details about the Presentation Server client products of sessions that have been recorded to date. When a session is recorded for a client product that was not recorded previously, a new ClientPlatform record is created. This is the unique combination of client product ID and client product version. For example, a client product ID value of 1 identifies the Windows client and a version of 10.100.55836 identifies it as the latest 10.1 client (at the time of this writing.) See the description of the ClientProductID column for the mapping of IDs to product type.
| Column |
Type |
Null |
Description |
| ClientProductID |
int |
No |
Presentation Server client product ID. The following is the list of known client product ID mappings.
0 = Unknown
1 = Windows
10 = EPOC
11 = OS/2
14 = DOS
81 = UNIX/Linux
82 = Mac
100 = OEM
261 = Java
7945 = Windows CE |
| ClientProductVersion |
nvarchar(128) |
No |
Presentation Server client product version in the form [Major].[Minor].[Build]. If the product version could not be determined, as is the cause with some old clients, this value is set to 1.00.000. |
EndReason
This table contains the static set of possible reasons for session recordings ending. This table is preloaded at install-time with values for Incomplete, Logoff, Rollover, Trigger, and Unknown. See also the StartReason table that describes the reasons for a session recording starting.
| Column |
Type |
Null |
Description |
| ID |
smallint |
No |
Primary key row identifier. The following is the list of end reason IDs.
0 = Unknown
1 = Logoff
2 = Rollover
3 = Trigger (currently not used in SmartAuditor)
4 = Incomplete |
| Description |
nvarchar(50) |
No |
Textual description of the end reason. |
Event
This table contains all searchable events injected into the recorded session files using the SmartAuditor Event API. Each recorded event is associated with an ICLFile record with which the event is associated. Each event has an associated EventType record. Events recorded without the searchable flag setting are not stored in the database. This table does not have a primary key.
| Column |
Type |
Null |
Description |
| ICLFileID |
uniqueidentifier |
No |
Foreign key reference to the ICLFile containing the recorded searchable event. |
| EventTypeID |
uniqueidentifier |
No |
Foreign key reference to the EventType record identifying the type of this event. |
| EventText |
nvarchar(128) |
No |
The event text describing the event as passed to the Event API during session recording. |
| EventData |
varbinary(4096) |
Yes |
Optional binary data associated with this event. |
EventType
This table contains the type definitions for all searchable events injected into the recorded session files using the SmartAuditor Event API. Each recorded event type is associated with an Event record. The Event API forces event types to follow a three-part naming convention with each part separated with a dot; for example, MyBusiness.TradingSystem.TransactionNumber.
| Column |
Type |
Null |
Description |
| ID |
uniqueidentifier |
No |
Primary key row identifier. |
| TypeName |
nvarchar(128) |
No |
The three-part name of the event type in the form [Part1].[Part2].[Part3] |
GroupAccount
This table contains the Windows group accounts for all users who have had their sessions recorded to date. Groups are identified by their unique combination of group name and domain name (also known as the account authority name or AAName). The types of groups recorded are built-in groups such as BUILTIN\Administrators and domain security groups such as MyDomain\Finance.
| Column |
Type |
Null |
Description |
| ID |
uniqueidentifier |
No |
Primary key row identifier. |
| Name |
nvarchar(128) |
No |
Name of the group excluding the domain/account authority name. |
| AAName |
nvarchar(128) |
No |
Name of the domain (or account authority) to which the group belongs. For built-in groups, this is set to BUILTIN. |
GroupAccountSet
This table defines a set of group accounts for all users who have had their sessions recorded to date. A group account set is the unique list of group accounts. If two or more recorded users belong to the same combination of group accounts, all will reference the same group account set. If the set of groups differ, even slightly, a new group account set is created. The many-to-many relationship to the GroupAccount table is handled through the GroupAccountSetMember table. The single ID field of the GroupAccountSet table is generated by the SmartAuditor Storage Manager as the hash value of the string concatenation of all group account names in alphabetical order. This is an optimization allowing the Storage Manager to determine whether or not a set already exists without having to search the database for each group account record individually. Because the hash values generated are only 64-bit where the ID column is 128-bit in length, the high 64-bit of the ID is always set to 0.
| Column |
Type |
Null |
Description |
| ID |
uniqueidentifier |
No |
Primary key row identifier. This value is generated from hash value as described above. |
GroupAccountSetMember
This table is a junction table joining GroupAccountSet with GroupAccount defining their many-to-many relationship. A set contains many group accounts and each group account can belong to many sets. This table does not have a primary key but the composite of the two columns must be unique.
| Column |
Type |
Null |
Description |
| GroupAccountSetID |
| uniqueidentifier |
No |
Foreign key reference to the GroupAccountSet record identifying the set to which the group account belongs. |
| GroupAccountID |
uniqueidentifier |
No |
Foreign key reference to the GroupAccount record identifying the group account belonging to the set. |
ICLFile
This table contains an entry for each recorded session captured by the SmartAuditor Storage Manager or otherwise imported in the database. All metadata fields captured during recording are stored in this table and the tables that are referenced by this table. It also includes physical file information such as the path where the file is stored, its file size, and whether or not the file is archived. The file path is the link between the database record and physical location of the file on the file system. No actual recorded data is stored in the database.
Many fields in this table are allowed to be null because ICLFile records are created as soon as recording starts, when many of the metadata attributes about the sessions are not yet known. A new session recording has a start time and file path, but the logon user might not yet be known. A session with a logged on user has many details captured at logon, but has no end time or end reason if the recording is still live. Only a completed recording has values populated. Each column definition below indicates when the field is set.
ICLFile records identify a recording, not a session. A single session can be made up of many ICLFile recordings due to file rollover. The association of a recording to its previous or next recording is controlled through the recursive foreign key PrevICLFileID and NextICLFileID columns. A file rolled over into another file has its NextICLFileID referencing the later recording. Likewise, the later recording has its PrevICLFileID field referencing the earlier ICLFile record. For a single session, this creates a doubly linked list of recording files. The StartReasonID and EndReasonID columns can also be used to determine how the recording started or ended and whether or not it is associated with other recordings.
| Column |
Type |
Null |
Description |
| ID |
uniqueidentifier |
No |
Primary key row identifier and the file identifier GUID. The file identifier also appears as part of the name of the physical file created by the Storage Manager. |
| SessionID |
int |
Yes |
Presentation Server or Terminal Services session identifier. Inserted as part of session logon. |
| SessionName |
nvarchar(128) |
Yes |
Presentation Server session name, such as ICA-tcp#1. Inserted as part of session logon. |
| UserAccountID |
uniqueidentifier |
Yes |
Foreign key reference to the UserAccount record identifying the user who logged on to this session. Inserted as part of session logon. |
| GroupAccountSetID |
uniqueidentifier |
Yes |
Foreign key reference to the GroupAccountSet record identifying the set of groups that the logon user belongs to at the time the session is recorded. Inserted as part of session logon. |
| ClientID |
uniqueidentifier |
Yes |
Foreign key reference to the Client record identifying the Presentation Server client used to establish the recorded session. Inserted as part of session logon. |
| ServerID |
uniqueidentifier |
Yes |
Foreign key reference to the Server record identifying the Presentation Server instance where the session was hosted. Inserted as part of session logon. |
| ApplicationID |
uniqueidentifier |
Yes |
Foreign key reference to the Application record identifying the published application used to launch the session. If session sharing is used and multiple published applications are launched into the same recorded session, only the first published application is recorded. Inserted as part of session logon. |
| PrevICLFileID |
uniqueidentifier |
Yes |
Foreign key reference to another ICLFile record identifying the recording file previous to this recording for the same session. This is used only if this recording was started due to a rollover, otherwise the value is null. If rollover, this value is inserted when the ICLFile record is first created. |
| NextICLFileID |
uniqueidentifier |
Yes |
Foreign key reference to another ICLFile record identifying the recording file directly after this recording for the same session. This is used only if this recording ended due to a rollover, otherwise the value is null. If rolled over to a new file, this value is inserted when the recording completes. |
| StartTime |
datetime |
No |
The time when the recording started in local time of the server where the recorded session was hosted. For recordings created due to a new session, this is the time when the session was started. For rolled over recordings, this is the time when the rollover occurred. Inserted when the ICLFile record is first created. |
| StartTimeUtc |
datetime |
No |
The UTC/GMT time when the recording started. For recordings created due to a new session, this is the time when the session was started. For rolled over recordings, this is the time when rollover occurred. Inserted when the ICLFile record is first created. |
| StartReasonID |
smallint |
Yes |
Foreign key reference to the StartReason record describing how the recording was started. Inserted when ICLFile record is first created. |
| LoginTime |
datetime |
Yes |
The time when the user logged on to the session in the local time of the server where the recorded session was hosted. For rolled over recordings, this time will be earlier than the StartTime. Inserted as part of session logon. |
| LoginTimeUtc |
datetime |
Yes |
The UTC/GMT time when the user logged on to the session. For rolled over recordings, this time will be earlier than the StartTimeUtc. Inserted as part of session logon. |
| EndTime |
datetime |
Yes |
The time when the recording ended in local time of the server where the recorded session was hosted. For completed sessions, this is the time when the session ended. For rolled over recordings, this is time when rollover occurred. Inserted when the recording completes. |
| EndTimeUtc |
datetime |
Yes |
The UTC/GMT time when the recording ended. For completed sessions, this is the time when the session ended. For rolled over recordings, this is time when rollover occurred. Inserted when the recording completes. |
| EndReasonID |
smallint |
Yes |
Foreign key reference to the EndReason record describing how the recording ended. Inserted when the recording completes. |
| DaylightSavings |
tinyint |
No |
Flag indicating whether or not daylight savings was on when the recording started. A value of zero indicates daylight savings was off. A non-zero value indicates daylight savings was on. Inserted when the ICLFile record is first created. |
| ClientHRes |
int |
Yes |
The horizontal resolution of the client at the time when the recording started. Changes to client resolution made during the session are not recorded in the database. Inserted as part of session logon. |
| ClientVRes |
int |
Yes |
The vertical resolution of the client at the time when the recording started. Changes to client resolution made during the session are not recorded in the database. Inserted as part of session logon. |
| ClientColorDepth |
int |
Yes |
The color depth, measured in bits per pixel, of the client at the time when the recording started. Changes to client color depth made during the session are not recorded in the database. Inserted as part of session logon. |
| ClientEncryption |
nvarchar(128) |
Yes |
The description of the type of encryption used between the client and server. Inserted as part of session logon. |
| FilePath |
nvarchar(512) |
No |
The full path name to the recorded session file. Inserted when the ICLFile record is first created. |
| FileSize |
bigint |
Yes |
The size of the recorded session file measured in bytes. This value is set only when the recording of the file is complete. Live recordings have a null or zero value. |
| Duration |
int |
No |
This is a calculated field returning the difference between the start and end times of the recording measured in seconds. If end time is not yet stored, the duration is zero. |
| ArchiveTimeUtc |
datetime |
Yes |
If the recorded session file is archived, this field contains the UTC/GMT time when the file was archived. If the file is not archived, this field is null. |
| Notes |
nvarchar(512) |
Yes |
Optional notes field associated with the recording. For archived recordings, this might contain information where the file is stored. The SmartAuditor system does not do anything with this field and can be used by third parties for any purpose. |
PolicyDocument
This table contains the policy documents as used by the SmartAuditor system. PolicyDocument is for Citrix internal system use only and is not documented.
PolicyType
This table contains the types of policies as used by the SmartAuditor system. PolicyType is for Citrix internal system use only and is not documented.
SchemaVersion
This table contains a single record containing the current database schema version and is used by the SmartAuditor system to ensure database compatibility.
| Column |
Type |
Null |
Description |
| Major |
int |
No |
Major version number. |
| Minor |
int |
No |
Minor version number. |
| Build |
int |
No |
Build number. |
| Revision |
int |
No |
Revision number. |
Server
This table contains details about the computers running Presentation Server that hosted sessions recorded to date. Each ICLFile table record references an entry in this table. When a session is recorded for a server that was not recorded previously, a new Server record is created. This includes its machine name, zone, farm, and the type of platform the server was running on.
| Column |
Type |
Null |
Description |
| ID |
uniqueidentifier |
No |
Primary key row identifier. |
| Name |
nvarchar(128) |
No |
Name of the computer running Presentation Server. |
| ZoneName |
nvarchar(128) |
No |
Name of the zone to which the server belongs. |
| FarmName |
nvarchar(128) |
No |
Name of the farm to which the server belongs. |
| ServerPlatformID |
uniqueidentifier |
No |
Foreign key reference to the ServerPlatform table identifying the platform (that is, Presentation Server product version and operating system version) as used by the server. |
ServerPlatform
This table contains the combinations of Presentation Server products and operating systems that hosted recorded sessions to date. When a session is recorded for a server platform that was not recorded previously, a new ServerPlatform record is created.
| Column |
Type |
Null |
Description |
| ID |
uniqueidentifier |
No |
Primary key row identifier. |
| ServerProductName |
nvarchar(128) |
No |
Product name. |
| ServerServicePack |
nvarchar(128) |
Yes |
Service pack description. |
| ServerMajorVer |
int |
Yes |
Major version number. |
| ServerMinorVer |
int |
Yes |
Minor version number. |
| ServerBuild |
int |
Yes |
Build number. |
| OSName |
nvarchar(128) |
Yes |
Name of the operating system. |
| OSMajorVer |
int |
Yes |
Operating system major version number. |
| OSMinorVer |
int |
Yes |
Operating system minor version number. |
StartReason
This table contains the static set of possible reasons for session recordings starting. This table is preloaded at install-time with values for Logon, Rollover, Trigger, and Unknown. See also the EndReason table that describes the reason for a session recording ending.
| Column |
Type |
Null |
Description |
| ID |
smallint |
No |
Primary key row identifier. The following is the list of start reason IDs.
0 = Unknown
1 = Logon
2 = Rollover
3 = Trigger (currently not used in SmartAuditor) |
| Description |
nvarchar(50) |
No |
Textual description of the start reason. |
UserAccount
This table contains the Windows user accounts for all users who had their sessions recorded to date. Users are identified by their unique combination of logon name and domain name (also known as the account authority name or AAName).
| Column |
Type |
Null |
*Description* |
| ID |
uniqueidentifier |
No |
Primary key row identifier. |
| Name |
nvarchar(128) |
No |
Name of the user logon account excluding the domain/account authority name. |
| AAName |
nvarchar(128) |
No |
Name of the domain (or account authority) to which the user belongs. |
