SQL Server Storage Database
The following diagram shows the tables that will be created in the SQL Server database with the standard SQL Server plugin. An explanation of each of the tables is detailed in the following pages.
Session Table
This table is the root of all information inserted into the database. Each row represents a BeyondTrust session.
id | An auto-incrementing number that uniquely identifies this field in the database. |
lsid | An alphanumeric identification which uniquely identifies this session. |
type | The type of session. Currently, support is the only value supported. |
start_time | The time at which the session began either by the customer’s running the customer client or by the representative’s initiating a Jump session. Date is returned in UTC format. |
end_time | The time at which the session ended by the representative’s closing the session. Date is returned in UTC format. This field will be empty for sessions which are still in progress when the data was extracted or which closed abnormally. |
duration | Session length in HH:MM:SS format. |
file_transfers | The number of file transfers which occurred during the session. |
host_name | The hostname of the BeyondTrust support site through which the session occurred. |
external_key | An arbitrary string that can link this session to an identifier on an external system, such as a help desk ticket ID. This can be input from within the representative console or defined programmatically. |
public_site_id | The identification of the site. This defaults to 1. |
public_site_name | The name of the BeyondTrust site. Unless set, this contains the value Default. |
lseq |
An incrementing number used to represent support sessions in a non-string format. The LSEQ element is not guaranteed to be unique or strictly sequential. |
Jump_group_id | This is the Jump Group's unique ID for its type. Jump Groups of different types can have the same ID. For Personal Jump Groups, this is the unique ID of the user who owns the Jump Group. Each user can only have a single Personal Jump Group. |
Jump_group_name | The element's content is the name of the Jump Group. For Personal Jump Groups, the name of the Jump Group is the Private Display Name of the representative who owns the Jump Group. |
Jump_group_type | This is the Jump Group's type, which can be "shared" or "personal". |
Session_Event Table
This table links to the session table via the session_id field. Each row in this table represents a session event that took place during this session. Sessions can have multiple session events.
id | An auto-incrementing number that uniquely identifies this field in the database. | ||||||||||||||||||||||||||||||||||||||||
session_id | The ID of the session in which this event occurred. This field links a session_event row to a session row. | ||||||||||||||||||||||||||||||||||||||||
type |
The type of event that occurred. Event types include:
|
||||||||||||||||||||||||||||||||||||||||
event_time | The time at which the event occurred. The time is returned in UTC format. | ||||||||||||||||||||||||||||||||||||||||
performed_by | The name of the entity that performed the action. | ||||||||||||||||||||||||||||||||||||||||
performed_by_type | The type of entity that performed the action, indicating whether this action was performed by the System, a Customer, or a Representative. | ||||||||||||||||||||||||||||||||||||||||
destination | The name of the entity to whom this action was directed. | ||||||||||||||||||||||||||||||||||||||||
destination_type | The entity to which the event was directed, indicating whether this action was directed to the System, a Customer, or a Representative. |
Session_Event_Data Table
This table links to the session_event table via the session_event_id field. Each row in this table represents a single key-value pair associated with a particular session event. Session events can have multiple key-value pairs.
id | An auto-incrementing number that uniquely identifies this field in the database. |
session_event_id | The ID of the session event to which this key-value pair belongs. This field links a session_event_data row to a session_event row. |
name | The key of this field. |
value | The value of this field. |
System_Information Table
This table links to the session_event table via the session_event_id field. Each row in this table represents multiple categories of system information collected per a session_event.
System information is logged only when pulled automatically at the beginning of the session and not when specifically requested by the representative. This is to prevent overload with the large amount of dynamic data that can be retrieved from the remote system.
id | An auto-incrementing number that uniquely identifies this field in the database. |
session_event_id | The ID of the session event to which this system information belongs. |
category | The type of system information. Types include categories, such as the following: General, Memory, Drives, Processes, Events, Network, and Programs. There are additional categories available. The category is based on the remote operating system. |
information | Contains multiple <field> elements, each of which contains a descriptor for the specific data field. For example, the Drives category would have <field> elements Drive, Type, Percent Used, etc. |
Representative Table
This table links to the session table via the session_id field. Each row in this table represents a representative who participated in this session. Sessions can have multiple representative fields.
id | An auto-incrementing number that uniquely identifies this field in the database. |
gs_number | Uniquely identifies the representative regarding their current connection to the BeyondTrust Appliance B Series. A gsnumber is assigned on a per-connection basis, so if a representative leaves a session and then rejoins without logging out of the B Series Appliance, their gsnumber will remain the same. However, if the representative’s connection is terminated for any reason, when that representative logs back into the B Series Appliance, they will be assigned a new gsnumber. A gsnumber may be recycled, so while two people connected at the same time will never have the same gsnumber, one person may have a gsnumber that was assigned to another person in the past. |
session_id | The ID of the session in which this representative participated. This field links a representative row to a session row. |
user_id | The unique ID assigned to the representative. |
user_name | The username assigned to the representative. |
display_name | The private display name assigned to the representative. Note that this field contains the private display name’s value at the time of the session, which may not match the current value if the private display name has subsequently been changed. |
public_display_name | The public display name assigned to the representative. Note that this field contains the public display name's value at the time of the session, which may not match the current value if the public display name has subsequently been changed. |
display_number | The display number assigned to the representative. This is the display number at the time of the session and may not match the current value. |
public_ip | The representative’s public IP address. |
private_ip | The representative’s private IP address. |
is_owner | Integer value (1 or 0) indicating whether the representative was an actual owner of the session or was merely a conference member. |
is_primary | Integer value (1 or 0) indicating if the representative was the final representative to own the session. |
duration | Integer value indicating the number of seconds the representative was involved in this support session. |
host_name | The hostname of the representative's computer. |
operating_system | The operating system of the representative's computer. |
Customer Table
This table links to the session table via the session_id field. Each row in this table represents a customer who participated in this session. In the current BeyondTrust version, there will always be one customer field per session.
id | An auto-incrementing number that uniquely identifies this field in the database. |
session_id | The ID of the session in which this customer participated. This field links a customer row to a session row. |
gs_number | Uniquely identifies the customer regarding their current connection to the BeyondTrust Appliance B Series. A gsnumber may be recycled, so while two people connected at the same time will never have the same gsnumber, one person may have a gsnumber that was assigned to another person in the past. Can be used to correlate a <customer> element with a <primary_cust> or with an event’s <performed_by> or <destination> element. |
issue | The numeric ID of the issue or the representative which the customer selected from the dropdown of the issue submission form or which was designated programmatically. |
issue_description | The description of the problem as entered by the customer in the Describe Your Issue text area field of the issue submission form or as programmatically assigned. |
display_name | The display name is the name the customer provided in the issue submission form. If no name was provided, then this is populated with the name associated with the user logged into the customer's computer. |
company_name | The company name which the customer entered in the Company field on the issue submission form or which was programmatically assigned. |
company_code | The code which the customer entered in the Company Code field on the issue submission form or which was programmatically assigned. |
public_ip | The customer’s public IP address. |
private_ip | The customer’s private IP address. |
rpat_support | Integer value (1 or 0) indicating whether the customer session is provided via RPAT. |
host_name | The hostname of the customer's computer. |
operating_system | The operating system of the customer's computer. |
Team Table
This table links to the session table via the session_id field. Each row in this table represents a support team queue to which this session was assigned. Sessions can have multiple team fields.
id | An auto-incrementing number that uniquely identifies this field in the database. |
session_id | The ID of the session of which this team was an owner. This field links a team row to a session row. |
team_id | The unique ID assigned to this support team. |
name | The display name of the support team. Note that this field contains the team name as it currently appears, which may not match the value at the time of the session if the team name has been subsequently changed. |
is_primary | Integer value (1 or 0) indicating if this support team was the last team to which the session was transferred. |
Custom_Attribute Table
This table links to the session table via the session_id field. Each row in this table represents a custom attribute which was assigned to this session. Sessions can have multiple custom attribute fields.
id | An auto-incrementing number that uniquely identifies this field in the database. |
session_id | The ID of the session to which this custom attribute was assigned. This field links a custom_attribute row to a session row. |
display_name | The display name of the custom attribute. |
code_name | The code name of the custom attribute. |
value | The value of the custom attribute. This may have been provided by the customer or assigned programmatically. |