SysAid Database Guide

 
Author
Message
SysAid Technical Writer
580
 

SysAid Database Guide

 

Guide Reference

 

    Introduction
    Data Types
    Guide Conventions
    Recurring Tables
    Contact Us

 

Database Tables

 

    Service Records
    Assets
    Users
    Projects and Tasks
    CMDB
    Monitoring
    SLA/SLM
    Chat
    Knowledge Base
    Miscellaneous
    Omitted Tables

 

 

 

Introduction

 

As a SysAid administrator, there may be times when you need to access the SysAid database directly. An example of this could be to run reports using a 3rd party reporting tool. Another example could be to run a complex query not supported by the SysAid UI.

 

This guide includes a list of the different tables in the SysAid database and the fields contained therein. Each field is displayed with the field name, data type, field description, and whether or not the field is a primary key for the table.

 

Please note that some tables are omitted. If a table is not included in this guide, that typically means that there is no need to access that table. A list of omitted tables can be found here. Also, if a DB field appears in your database but does not appear in this guide, it means that that field is no longer in use. If you have any questions, you may contact SysAid Support.

 

Warning: Do not change the value of the "account_id" field in any table. Changing the value of the "account_id" field could corrupt your database!

 

 

 

Data Types

 

Following is a description of the different data types used by the various fields in the database. (Descriptions are from the websites of Apache, Microsoft, and Oracle, respectively.)

 

Important: The Database Guide lists only one data type for each field. This is the data type used in SQL.

To view the data types for fields in Oracle, view the file ..\SysAidServer\root\WEB-INF\conf\init_db_oracle.sql.

 

Data Type Description Database
Numerical Data
Int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes.

All
Bigint Integer (whole number) data from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). Storage size is 8 bytes. SQL
Tinyint Integer data from 0 through 255. Storage size is 1 byte. SQL
Number Stores integers (negative, positive, floating) of up to 38 digits of precision. The Number data type can store numbers in the range of 1.0E-130 to 1.0E126. Oracle
Float A floating point number data with the following valid values: - 1.79E + 308 through -2.23E - 308, 0 and 2.23E -308 through 1.79E + 308. All

String/Character Data

Varchar/Varchar2

Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. The storage size is the actual length of the data entered + 2 bytes.

All
NVarchar Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. The storage size, in bytes, is two times the actual length of data entered + 2 bytes. SQL
Char Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. Oracle
NChar Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000. The storage size is two times n bytes. SQL
CLOB

Used to store unicode character-based data, such as large documents in any character set. A CLOB can be up to 2,147,483,647 characters long.

Oracle
Ntext

Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes. Storage size, in bytes, is two times the string length that is entered.

SQL
Date + Time Data
DateTime Store values that are both dates and times or only dates. A DateTime value is stored in eight bytes — two four-byte integers. SQL
Date The Date data type stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight). Oracle
Binary Data
Image Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes. SQL
Long Raw Used for storing binary data of variable length up to 2 Gigabytes in length. Note that a table can only have one Long Raw column. Oracle

 

 

 

Guide Conventions

 

This guide uses several conventions so that it's easier to understand. Following is an explanation of these conventions:

 

Convention for Explanation
Table primary keys All fields that are primary keys for a table are marked by the key icon .
Linked fields Many fields in SysAid store a value that's linked to another table. For example, the "request_user" field in the "service_req" table is linked to the "user_name" field in the "sysaid_user" table. If the value in a linked field is changed to a value that doesn't exist in the corresponding table, SysAid will encounter an error. All linked fields are marked by the linked icon , and the description of these fields includes the text "Corresponds to the 'field_name' field in the 'table_name' table".
Tables names and field names Any time a table name or field name is mentioned, it is surrounded by quotation marks (""). For example, the "value_key" field in the "cust_values" table.
Field values Any time a field value is written, it is surrounded by parentheses (). For example, "Type of SRs included in the measurement: Incident (1), Problem (6), Change (4), Request (10)". In this example, 1, 6, 4, and 10 are potential values for the given field, corresponding to the different SR types.
Lists within SysAid Lists in SysAid are saved as integers. These integer correspond to text descriptions of each entry in the list. For example, When SysAid saves the priority of a service record with priority High, it is saved as (3), which is the ID# ("value_key") of priority High in the priority list. Each list field in this guide indicates which table to look in to see a description of the list.
Long lists There are certain cases where a list contains many entries, and this guide does not list all entries. In such cases, a reference to a particular .jsp is given. By opening this .jsp in your web browser and viewing the source code, you can easily see the exact database value for all entries in the list. For example, if you open http://yoursysaidserver:8080/CustomLists.jsp in your browser and view source, you can see the DB value needed to reference any list in the "cust_values" table. (E.g. (subTabComplexity) for the Complexity list.)

 

 

 

Recurring Tables

 

There are several tables in SysAid that reoccur for multiple entities. They are:

  • table_history
  • table_files
  • table_links
  • monitor_day_data, monitor_week_data, monitor_month data, monitor_year_data

 

History tables

Each history table corresponds exactly to a regular table, with the addition of two fields: "change_time" and "changed_by". Each time a table is saved, all data is copied into the history table with a unique version number, the user who made the change, and a timestamp. In that way, every single iteration of the table is saved to the table history, while the table itself can be overwritten with only the current data.

 

In this guide, history tables are listed along with the regular tables they correspond to. Note that not all tables have history tables.

 

Attachments tables

Each entity that supports attachments includes a "_files" table to store the attachments. The layout of the "_files" tables is the same for all entities, and is as follows:

 

Field Type Description
id int ID number of the entity with the attachment, e.g. SR #23, Task# 5, etc. Corresponds to the "id" field of the table for the appropriate entity
account_id nvarchar(32) SysAid account ID
file_id nvarchar(64) Database name of the file, including full path
file_name nvarchar(255) Filename of the file
file_content image The file itself
chat_session_id int For SRs only: attached chat ID#, for attachments that are attached to both an SR and a chat
file_date datetime Timestamp that file was attached

 

Links tables

Each entity that supports external hyperlinks includes a "_links" table to store the hyperlinks. The layout of the "_links" tables is the same for all entities, and is as follows:

 

Field Type Description
id int ID number of the entity with the attachment, e.g. SR #23, Task# 5, etc.
account_id nvarchar(32) SysAid account ID
file_id nvarchar(64) Database name of the link, including full path
link nvarchar(255) The hyperlink itself
file_name nvarchar(255) Description of the link
file_date datetime Timestamp that the link was added

 

Monitor tables

Each type of monitoring rule stores data in a daily table, a weekly table, a monthly table, and a yearly table. The structures of these tables are the same for all monitoring rules.

 

In this guide, only the structures for the services monitoring test tables (i.e. "services_day_data", "services_week_data", etc.) are detailed. The tables for the other monitoring tests are listed by name, followed by a short description.

 

 

Database Tables

 

 

Service Records

 

problem_type

 

Contains categories used by the Service Desk, Knowledge Base, and CMDB.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
problem_type nvarchar(64) Category of the SR
problem_sub_type nvarchar(64) Subcategory of the SR
route nvarchar(64) For internal use
desc_template ntext Contains the text of the description template for this category/sub-category/third-level category combination
third_level_category nvarchar(64) Third level category of the SR
module_relevance int Determines which category lists in SysAid display this category: End-User Portal (1), Incidents (2), Changes (4), Problems (8), Requests (16), CMDB (32). Values for all chosen lists are added together, such that a category that appears in all places would have value (63)
incident_template int Specifies which incident template to load when the category is selected when submitting an incident. Corresponds to the "id" field in the "service_req" table. Use (0) or NULL to use the default incident template
request_template int Specifies which request template to load when the category is selected when submitting a request. Corresponds to the "id" field in the "service_req" table. Use (0) or NULL to use the default request template
change_template int Specifies which change template to load when the category is selected when submitting a change. Corresponds to the "id" field in the "service_req" table. Use (0) or NULL to use the default change template
problem_template int Specifies which problem template to load when the category is selected when submitting a problem. Corresponds to the "id" field in the "service_req" table. Use (0) or NULL to use the default problem template
admin_groups nvarchar(64)  

 

 

service_req, service_req_history

 

Contains all information regarding service records, excluding action items. Incidents, Requests, Changes, and Problems are all stored in this table using the same fields. These SR types are differentiated by the value in the "sr_type" field.

 

Field Type Description
id int The ID of the service record
account_id nvarchar(32) SysAid account ID
computer_id nvarchar(64) SR's main asset. Corresponds to the "computer_id" field in the "computer" table
ci_id int SR's main CI. Corresponds to the "id" field in the "ci_attributes" table
problem_type nvarchar(64) Category of the SR. Corresponds to the "problem_type" field in the "problem_type" table
problem_sub_type nvarchar(64) Sub-category of the SR. Corresponds to the "problem_sub_type" field in the "problem_type" table
title nvarchar(255) Title of the SR
description ntext Description of the SR
workaround ntext Workaround for a problem
known_error nchar(1) Known error list: No (N), Production (P), and Development (D)
status int Status of the SR. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "status"
contact ntext Obsolete
responsibility nvarchar(64) Assigned to administrator. Corresponds to the "user_name" field in the "sysaid_user" table
urgency int Urgency of the SR. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "urgency"
priority int Priority of the SR. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "priority"
notes ntext Notes field
resolution ntext Resolution field
solution ntext Solution field
insert_time datetime SR request time
update_time datetime SR modify time
close_time datetime SR close time
update_user nvarchar(64) User who last modified the SR. Corresponds to the "user_name" field in the "sysaid_user" table
version int Version of the SR. Updated each time the SR is saved. Used by the SR history
knowledge_base Int  
submit_user nvarchar(64) User who submitted the SR. Corresponds to the "user_name" field in the "sysaid_user" table
submit_user_type tinyint Deprecated
request_user nvarchar(64) Request user of the SR. Corresponds to the "user_name" field in the "sysaid_user" table
request_user_type tinyint A number signifying the type of the user who requested the SR.  1 = A computer not associated with an end user or administrator (i.e. submitted by the Agent) 2 = An admin 3 = An end user
responsible_manager nvarchar(64) SR's responsible admin. Corresponds to the "user_name" field in the "sysaid_user" table
email_account nvarchar(64) The email account from which the SR originated. Blank if the SR was not submitted by email
due_date datetime SR's due date
location int SR's location. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "location"
parent_link int ID# of the SR's parent SR. Corresponds to the "id" field in the "service_req" table
escalation int The escalation level of the SR. (0) is not escalated, otherwise the escalation level appears here
third_level_category nvarchar(64) Third level category of the SR. Corresponds to the "third_level_category" field in the "problem_type" table
assigned_group nvarchar(64) SR's assigned admin group. Corresponds to the "group_name" field in the "user_groups" table
timers_update_time datetime The last time the timers associated with the SR were updated
timer1 - timer10 bigint Accumulated timer value for the corresponding timer. For example, the default for Timer 1 is Time to Repair
cust_list1 int SR custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "srList1"
cust_list2 int SR custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "srList2"
cust_text1 nvarchar(255) SR custom text 1
cust_text2 nvarchar(255) SR custom text 2
cust_notes ntext SR custom notes
cust_int1 int SR custom int 1
cust_int2 int SR custom int 2
cc nvarchar(255) CC field on SR. Contains a list of usernames separated by commas. Usernames correspond to the "user_name" field in the "sysaid_user" table
project_id int ID of the project associated with the SR. Corresponds to the "id" field in the "project" table
task_id int ID of the task associated with the SR. Corresponds to the "id" field in the "task" table
sr_type int Type of the SR: Incident (1), Incident Template (2), New Incident Template (3), Change (4), Change Template (5), Problem (6), Problem Template (7), Phone Call (8), Request (10), Request Template (11)
full_name nvarchar(255) Reserved
cust_date1 datetime SR custom date 1
cust_date2 datetime SR custom date 2
source int Source of the SR: Admin Portal (1), Mobile Admin Portal (2), Phone Call (3), End-User Portal (4), Agent (5), Email Integration (6), Monitoring (7), Task (8), Chat (9), External Agent (10), Reminder (11), Manually from Chat (12), Password Services (13)
sr_sub_type int Sub type of the SR. Corresponds to the sub types in the table "sr_sub_type"
followup_planned_date datetime SR follow up planned date
followup_actual_date datetime SR follow up actual date
followup_user nvarchar(64) User marked for follow up. Corresponds to the "user_name" field in the "sysaid_user" table
followup_text ntext Follow up text
success_rating int Success rating field
reopen_counter int Number of times the SR has been changed from a closed status class to an open one
assign_counter int Number of times the assigned to admin has changed
max_support_level int Highest support level the SR has reached
current_support_level int Current support level for the SR
agreement int Agreement attached to the SR. Corresponds to the "id" field in the "agreement" table
survey_status int Status of the survey: has not been sent (0), has been sent (1), has been answered (2)
impact int Impact of the SR. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "impact"
change_category int Change classification for changes. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "changeCategory"
archive int Indicates whether an SR is archived (1) or not (0)
closure_information int Closure information for a closed SR. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "closureInformation"
visible_to_eu nchar(1) Whether the incident/request template is visible to end users from the End-User Portal (Y) or not (N)
sr_class nvarchar(255) Class of the incident/request template
sr_weight Int Weight of the SR.

 

service_req_log

 

Contains a summary of all changes to a service record.

 

Field Type Description
log_id int Log ID #
account_id nvarchar(32) SysAid account ID
service_req_id int SR the log reports on. Corresponds to the "id" field in the "service_req" table
log_time datetime Time that the SR changed
log_type nvarchar(64) Type of change to the SR, such as incident changed or service record changed
log_description ntext A description of the change, such as New Incident or Incident has been assigned to…
ext_reference int Entry number of the log for a given service record
user_name nvarchar(64) User who made the change. Corresponds to the "user_name" field in the "sysaid_user" table

 

service_req_data

 

Stores the screen capture for incidents submitted from the End-User Portal.

 

Field Type Description
id int SR#
account_id nvarchar(32) SysAid account ID
screen_capture image The screenshot attached to the SR during submission from the End-User Portal

 

service_req_msg

 

Stores the message log for SRs (including incoming and outgoing emails).

 

Field Type Description
id int SR# the message is attached to. Corresponds to the "id" field in the "service_req" table
account_id nvarchar(32) SysAid account ID
msg_time datetime Time the message was sent/received
from_user nvarchar(64) Message sent from
to_user nvarchar(255) Message sent to
cc_user nvarchar(255) Users CCd on the message
method nvarchar(64) Type of message: Email (email), Automatic notification (auto), Action item notification (ITIL), SMS (sms), Instant Message (im)
subject ntext Message subject
msgid nvarchar(64) ID of the message. The timestamp + GMT offset (i.e. time zone) is used as the ID
msg_body ntext Body of the message
email_html_source ntext Original HTML body of the email (if the email has an HTML body)

 

service_req_files

 

Holds SR attachments. Click here to view the structure of the attachments table.

 

service_req_links

 

Stores SR hyperlinks. Click here to view the structure of the links table.

 

service_req_video

 

Field Type Description

id

int(11)

 

video_file

varchar(255)

 

 

service_req_template_ext

 

Field Type Description

id

int(11)

 

template_name

varchar(255)

 

 

work_report

 

Contains a log of all SR activities.

 

Field Type Description
id int Activity#
service_req_id int ID# of the SR the activity is attached to. Corresponds to the "id" field in the "service_req" table
account_id nvarchar(32) SysAid account ID
user_name nvarchar(64) Username of the user who left the activity. Corresponds to the "user_name" field in the "sysaid_user" table
from_time datetime Activity start time
to_time datetime Activity end time
description ntext The description of the activity performed
cust_list1 int Activity custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "srActList1"
cust_list2 int Activity custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "srActList2"
cust_text1 nvarchar(255) Activity custom text 1
cust_text2 nvarchar(255) Activity custom text 2
cust_notes ntext Activity custom notes
cust_int1 int Activity custom int 1
cust_int2 int Activity custom int 2
cust_int3 int Activity custom int 3
cust_int4 int Activity custom int 4
cust_date1 datetime Activity custom date 1
cust_date2 datetime Activity custom date 2
ci_id int ID# of the CI attached to the activity. Corresponds to the "id" field in the "ci_attributes" table

 

sr_sub_type

 

Records all SR sub types. Each sub type corresponds to a specific SR type specified in the "sr_type" field.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
sr_type int Type of the SR:  Incident (1), Request (10), Problem (6), Change (4), Phone call (8)
sr_sub_type int ID# of the SR sub type
sub_type_name nvarchar(255) Name of the sub type
sub_type_form_view ntext Lists the fields that are displayed on the form for that sub type
end_user_view ntext Lists the fields that are displayed on the form for that sub type when viewed by end users

 

sr_sub_tab, sr_sub_tab_history

 

Contains all information regarding action items. There is no differentiation on the DB level between action items for Requests, Changes, and Problems.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
sr_id int SR ID#. Corresponds to the "id" field in the "service_req" table
tab_name nvarchar(64) Number of the workflow tab in order from left-to-right as it appears in the sub type. The first workflow tab is 2
sub_tab_id int ID# of the action item
sub_tab_order int Number of the action item in order from left-to-right as it appears on the workflow tab. The action item used for the upper part of the screen is always (0), and the action items on the bottom part of the screen begin at (1)
assigned_to nvarchar(64) Assigned to user for the action item. Corresponds to the "user_name" field in the "sysaid_user" table
submit_user nvarchar(64) SR submit user. Corresponds to the "user_name" field in the "sysaid_user" table
insert_time datetime SR request time
due_date datetime Action item due date
title nvarchar(255) Action item title
description ntext Action item description
notes ntext Action item notes
proposed_delivery_date datetime Proposed delivery date for the SR
proposed_version nvarchar(64) Proposed version for implementation of the change/problem
complexity int Complexity of the SR. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "subTabComplexity"
urgency int Urgency of the action item. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "urgency"
priority int Priority of the action item. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "priority"
status int Whether the action item is Enabled (0), Disabled (1), or Completed (2)
auto_complete nchar(1) Indicates if the action item is set to autocomplete (Y) or requires manual completion (N)
on_complete_change_status int Update the SR to this status when the action item is completed. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "status"
notification_id nvarchar(255) Notification to send for this action item. Notifications are stored in the account conf, located in the "account_conf" field in the "account" table
notification_method nvarchar(64) Indicates whether the notification is sent on action item activation (on_active) or completion (on_completion)
duration float Duration in hours of the SR
resources_required_in_days float Anticipated resources (in days) required for the SR
ci_id int ID# of the CI attached to the action item. Corresponds to the "id" field in the "ci_attributes" table
cab_meeting_reference nvarchar(64) Reference number for the CAB meeting regarding a change
percent_completed int Percentage of the action item completed
cust_int1 - cust_int10 int Custom integer fields for action items
cust_text1 - cust_text10 nvarchar(255) Custom text fields for action items
cust_date1 - cust_date10 datetime Custom date fields for action items
cust_list1 - cust_list10 int Custom list fields for action items. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "subTabList1" - "subTabList10"
cust_notes1 - cust_notes10 ntext Custom notes fields for action items
task_id int ID# of the task attached to the action item. Corresponds to the "id" field in the "task" table
project_id int ID# of the project attached to the action item. Corresponds to the "id" field in the "project" table
assigned_group nvarchar(64) Group responsible for the action item. Corresponds to the "group_name" field in the "user_groups" table
location int Location associated with the action item. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "location"
completed_time datetime Time the action item was completed
enabled_time datetime Time the action item was enabled
company int Company associated with the action item. Corresponds to the "company_id" in the "company" table
company_backup int For internal use
modify_time datetime Action item last modify time
policy_compliance nchar(1) Indicates if there was policy compliance for the change/problem/request (Y) or not (N)
budgeted nchar(1) Indicates if change/problem/request was budgeted (Y) or not (N)
approved nchar(1) Indicates if the change/problem/request was approved (Y) or rejected (N)
user_acceptance nchar(1) Indicates if there was user acceptance for the change/problem/request (Y) or not (N)
hardware_costs float Hardware costs for the SR
software_costs float Software costs for the SR
installation_costs float Installation costs for the SR
training_costs float Training costs for the SR
maintenance_costs float Maintenance costs for the SR
total_costs float Total costs for the SR
internal_manpower_in_hours float Manpower in hours needed for the SR
total_expected_downtime_hours float Total expected downtime during implementation of the SR
department int Department associated with the action item. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "departments"
expected_downtime_start datetime Expected end of downtime associated with the SR
expected_downtime_end datetime Expected start of downtime associated with the SR
impact int Impact of the action item. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "impact"
reopened nvarchar(255) Whether the action item has not yet been completed (NULL), has been completed (No), or is currently reopened (Yes)
cust_float1 - cust_float10 float Custom float fields for action items
version int Version of the action item. Incremented by one on each save of the action item. Used by the action item history
change_time datetime History table only. Time the revision was saved
changed_by nvarchar(64) History table only. Username of the user who saved the revision. Corresponds to the "user_name" field in the "sysaid_user" table
allow_pdf nvarchar(1) Allow the end user to create a PDF of the action item (Y) or not (N)
update_user nvarchar(64) User who last modified an action item. Corresponds to the "user_name" field in the "sysaid_user" table
reopened_note ntext Note left by user who reopened an action item. Includes the user's name and a timestamp
and_condition nvarchar(1) Indicates whether action item dependencies are AND (Y) or OR (N) based
additional_user nvarchar(64) Additional user responsible for an action item. Corresponds to the "user_name" field in the "sysaid_user" table
on_activate_change_status int Update the SR to this status when the action item is activated. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "status"

 

sr_sub_tab_files

 

Holds action item attachments. Click here to view the structure of the attachments table.

 

sr_sub_tab_links

 

Stores action item hyperlinks. Click here to view the structure of the links table.

 

sysaid_user_routing

 

Records out of office SR reassignment.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
user_name nvarchar(64) Username of the out of office admin. Corresponds to the "user_name" field in the "sysaid_user" table
take_over_user nvarchar(64) Username of the admin to receive SRs instead. Corresponds to the "user_name" field in the "sysaid_user" table

 

status_settings

 

Tells SysAid which status an incident should receive when its linked request, change, or problem is updated to a particular status.

 

Field Type Description
id int ID# of the status setting
account_id nvarchar(32) SysAid account ID
change_status int Status of the request, change, or problem that causes the linked incident status to change. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "status"
incident_status int Status of the incident. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "status"
exclude_statuses nvarchar(255) If an incident is in one of these statuses, it is not changed. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "status"

 

priority_matrix_cust_values

 

Stores the Priority Matrix.

 

Field Type Description
disable int Whether the priority rule is enabled (1) or disabled (0)
agreement_key int The agreement to which the priority rule applies. Corresponds to the "id" field in the "agreement" table
company_key int The company to which the priority rule applies. Corresponds to the "company_id" field in the "company" table
impact_key int The impact for which the priority rule applies. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "subTabImpact"
urgency_key int The urgency for which the priority rule applies. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "urgency"
priority_key int The priority applied to the SR if all of the other fields match. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "priority"
last_update datetime The time the priority rule was last modified

 

automatic_texts

 

Stores automatic texts for the Send Message page.

 

Field Type Description
id int ID# of the automatic text
account_id nvarchar(32) SysAid account ID
title nvarchar(255) Title of the automatic text
description ntext Text to be inserted into messages
assigned_group nvarchar(64) Group that can select the automatic text. Corresponds to the "group_name" field in the "user_groups" table, or can choose "all"

 

sub_tab_views

 

Stores action item templates. There is no differentiation on the DB level between action items for Requests, Changes, and Problems.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
id int ID# of the action item template
name nvarchar(64) Name of the action item template
sub_tab_view ntext Specification of the fields on the action item template

 

sr_tab_dependences

 

Contains the dependencies between different action items on a change, problem, or request.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
sr_id int ID# of the SR. Corresponds to the "id" field in the "service_req" table
tab_name nvarchar(64) Number of the workflow tab in order from left-to-right as it appears in the sub type. The first workflow tab is 2
sub_tab_order int Number of the action item in order from left-to-right as it appears on the workflow tab. The action item used for the upper part of the screen is always (0), and the action items on the bottom part of the screen begin at (1)
depends_on_tab nvarchar(64) Number of the workflow tab this action item depends upon for activation
depends_on_sub int Number of the action item this action item depends upon for activation
dependent_method nvarchar(64) Whether the action item is activated upon activation (on_active) or upon completion (on_completion) of the target action item
filter_expression ntext Contains the query created by the dependency's Filter Expression as it appears in the SysAid Expression Builder
filter_sql ntext Contains the query created by the dependency's Filter Expression

 

sr_sub_tab_populate

 

Tells a change, problem, or request which fields to populate from other action items.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
sr_id int ID# of the SR. Corresponds to the "id" field in the "service_req" table
tab_name nvarchar(64) Number of the workflow tab in order from left-to-right as it appears in the sub type. The first workflow tab is 2
sub_tab_order int Number of the action item in order from left-to-right as it appears on the workflow tab. The action item used for the upper part of the screen is always 0, and the action items on the bottom part of the screen begin at 1
field_name nvarchar(64) The field to copy to. Corresponds to a field in the "sr_sub_tab" table
source_tab_name nvarchar(64) Number of the workflow tab from which to copy a field
source_sub_tab_order int Number of the action item from which to copy a field
source_field_name nvarchar(64) The field to be copied. Corresponds to a field in the "sr_sub_tab" table

 

static_filter

 

Field Type Description
id varchar(36)  
name varchar(64)  
filter longtext  

 

 

 

Assets

 

asset_types

 

Contains the list of asset types.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
computer_type nvarchar(64) Name of asset type
caption nvarchar(64) UI caption for asset type
file_name nvarchar(255) Filename of the icon for the asset type
default_file_name nvarchar(255) For predefined asset types, filename of the default icon for the asset type
ci_sub_type_id int CI asset sub type that corresponds to the asset type. Corresponds to an "id" in the table "ci_sub_type"

 

computer, computer_history

 

Fields for the Asset form. Some fields are also contained in the "computer_attributes" table. The type of asset is determined by the "computer_type" field.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
computer_id nvarchar(64) ID of the asset
computer_name nvarchar(64) Name of the asset
computer_type nvarchar(64) Type of the asset. Corresponds to the "computer_type" field in the "asset_types" table
parent_group nvarchar(255) Group of the asset. Corresponds to the "group_name" field in the "computer_group" table
inventory_xml ntext Stores the inventory.xml file sent by the SysAid Agent
inventory_time datetime The last time the asset inventory was updated (by the Agent, by network discovery, etc.)
update_time datetime Last time changes were saved to the asset
ip_address nvarchar(64) IP address of the asset
description nvarchar(255) Description of the asset
username nvarchar(64) Owner of the asset. Corresponds to the "user_name" field in the "sysaid_user" table
location nvarchar(255)  
location_idx int Location of the asset. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "location"
building nvarchar(64) Building in which the asset is located
floor nvarchar(64) Floor on which the asset is located
cubic nvarchar(64) Cubicle in which the asset is located
catalog_number nvarchar(64) Catalog number of the asset. Corresponds to the "catalog_number" field in the "asset_catalog" table
supplier int Supplier of the asset. Corresponds to the "supplier_id" field in the "supplier" table
maintenance_supplier int Organization that provides maintenance for the asset. Corresponds to the "supplier_id" field in the "supplier" table
company_serial nvarchar(64) Your company's serial number for the asset
external_serial nvarchar(64) The manufacturer's external serial number for the asset
monitor nvarchar(64) The asset's monitor
monitor_serial nvarchar(64) The serial number for the asset's monitor
collection_type int Manner in which the asset was added to SysAid: SNMP (1), Agent (2), Manual (3), WMI (4), iOS (5), Android (6)
collection_params ntext Contains credentials used for SNMP and WMI scanning
cust_list1 int Asset custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "assetList1"
cust_list2 int Asset custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "assetList2"
cust_text1 nvarchar(255) Asset custom text 1
cust_text2 nvarchar(255) Asset custom text 2
cust_notes ntext Asset custom notes
cust_int1 int Asset custom integer 1
cust_int2 int Asset custom integer 2
parent_asset nvarchar(64) ID of the parent asset of the current asset. Corresponds to the "computer_id" field in this table
department int Department of the asset. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "departments"
company int Company the asset belongs to. Corresponds to the "company_id" in the "company" table
company_backup int For internal use
disable nchar(1) Whether the asset is disabled (Y) or not (N)
manual_asset nchar(1)  
purchase_cost float Purchase cost of the asset
purchase_currency int Purchase currency of the asset. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "currencyList"
agent_version nvarchar(64) Version of the SysAid Agent installed on the asset
cust_date1 datetime Asset custom date 1
cust_date2 datetime Asset custom date 2
version int Version of the asset. Incremented by one on each save of the asset. Used by the asset history
change_time datetime History table only. Time the revision was saved
changed_by nvarchar(64) History table only. Username of the user who saved the revision. Corresponds to the "user_name" field in the "sysaid_user" table
snmp_cust_text_1 - snmp_cust_text_20 nvarchar(255) SNMP custom text fields. Used to store the values of customized OIDs
packets_in float Network traffic received by the asset's primary network card
packets_out float Network traffic sent by the asset's primary network card
mac_address nvarchar(255) MAC address of the asset's primary network card
last_boot datetime Last time the asset was booted
track_asset int Indicates whether asset availability is recorded for the asset (1) or not (0)
first_access datetime First time the SysAid Agent installed on the asset contacted the SysAid Server
device_status int Device MDM status: Enrolled (1), Not Enrolled (2), Revoked by Admin (3), Revoked by End User (4)
device_policy int Device MDM policy. Corresponds to the "id" field in the "mdm_policy" table
device_ownership int Whether a mobile device is Employee Owned (1) or Corporate Owned (2)
device_imei nvarchar(255) IMEI of the mobile device asset
device_icc nvarchar(255) ICC of the mobile device asset
device_home_carrier nvarchar(255) Home carrier of the mobile device asset
device_current_carrier nvarchar(255) Current carrier of the mobile device asset
device_phone_number nvarchar(64) Mobile device asset phone number
device_push nvarchar(255) Used for MDM push notifications
ios_push_magic nvarchar(255) Used for MDM push notifications
ios_unlock_token ntext Apple unlock key for iOS devices
designated_rds nvarchar(64) For future use
gfi_version nvarchar(64) Version of GFI used by Patch Management
gfi_build nvarchar(64) Build of GFI used by Patch Management
policy_id int ID for Patch Policy
patch_enabled numeric Indicates if the asset is enabled for Patch Management
last_scan_time datetime Last time the asset was scanned for new patches
last_patch_time datetime Last time a patch job ran on the asset
settings_id int The ID assigned to the asset's settings configuration

 

computer_attributes, computer_attributes_history

 

Fields for the Asset form. Some fields are also contained in the "computer" table.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
computer_id nvarchar(64) ID of the asset
cpu_vendor nvarchar(255) Vendor of the asset's CPU
cpu_model nvarchar(255) Model of the asset's CPU
cpu_speed int Speed of the asset's CPU
bios_type nvarchar(255) BIOS information for the asset
display_adapter nvarchar(255) Asset's display adapter
display_memory int On-board memory for the asset's display adapter
display_resolution nvarchar(255) Screen resolution for the asset's connected monitor
os_type nvarchar(255) Asset's OS type
os_version nvarchar(255) Asset's OS version
os_service_pack nvarchar(255) Service pack number for the asset's OS
memory_physical decimal Physical memory of the asset
serial nvarchar(64) Asset's serial number
model nvarchar(64) Model of the asset
manufacturer nvarchar(64) Manufacturer of the asset
purchase_date datetime Date the asset was purchased
warranty_expiration datetime Date the asset's warranty expires
last_maintenance datetime Last maintenance performed on the asset
last_page_count int For printers, the last page count taken
maintenance_page_count int For printers, the page count taken at the time of the last maintenance
disks_size int Total size of asset's storage devices
disks_count int Number of storage devices in the asset
mem_banks int Number of memory banks in the asset
occupied_mem_banks int Number of occupied memory banks in the asset
free_mem_banks int Number of free memory banks in the asset
cpu_count int Number of CPUs in the asset
os_name nvarchar(255) Name of the asset's OS
os_platform nvarchar(64) Platform of the asset's OS (e.g. Windows 7 Professional)
os_serial nvarchar(255) The asset's OS serial number
version int Revision # for the "computer_attributes" table. Used for " computer_attributes_history"
cpu_family int The number corresponding to the hardware that makes up the asset's CPU.
cpu_stepping int The number corresponding to the layers of hte chpi used by the asset's CPU.
cpu_model_number int The asset's CPU model number
cpu_serial_number nvarchar(255) The asset's CPU's serial number
nt_product_type nvarchar(255)  
nt_product_type_2 nvarchar(255) The asset's OS type
nt_os_edition nvarchar(255) The edition of the OS that the asset is using.
nt_service_pack_major_version int Service pack major version of the asset.
nt_service_pack_minor_version int Service pack minor version of the asset.
os_registered_user nvarchar(255) The asset's OS registered user
os_registered_org nvarchar(255) The asset's OS registered organization
os_build_number nvarchar(255) The asset's OS build number
os_kernel nvarchar(255) The version of the OS's kernel.

 

computer_group

 

Defines all asset groups.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
group_name nvarchar(255) Name of the asset group
parent_group_name nvarchar(255) Name of the parent group of the asset. Corresponds to the "group_name" field in this table
group_description nvarchar(255) A short description that includes the number of assets in the group
group_level int How many levels deep the group is from the top level group. The top level group is (1)

 

computer_lists

 

Stores additional information about assets, such as software products installed, services and processes running, etc.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
computer_id nvarchar(64) ID of the asset to which the list belongs. Corresponds to the "computer_id" field in the "computer" table
list_type nvarchar(64) The type of list, e.g. "Software"
value nvarchar(255) The list entry, e.g. "SysAid Agent version 8.6.1"
list_display nvarchar(255) For services, a short description of the service
version nvarchar(255) For software products, the version #
license nvarchar(255) For software products that SysAid knows how to extract the license key, the software license key

 

computer_users

 

List of computers and the users assigned to them.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
computer_id nvarchar(64) ID of the asset the users are connected to
user_name nvarchar(64) Username of the user who logged on to the computer. This is not the SysAid username, but rather the username used to log on to the computer
full_name nvarchar(255) Full name of the user connected to the asset
email_address nvarchar(64) Email address of the user connected to the asset

 

computer_files

 

Holds asset attachments. Click here to view the structure of the attachments table.

 

computer_links

 

Stores asset hyperlinks. Click here to view the structure of the links table.

 

computer_log

 

Stores entries to the asset Activity Log.

 

Field Type Description
log_id int ID# of the log details. Corresponds to the "log_id" field in the "computer_changes" table
account_id nvarchar(32) SysAid account ID
computer_id nvarchar(64) ID of the computer that had the change. Corresponds to the "computer_id" field in the "computer" table
log_time datetime Time of the log entry
log_type nvarchar(64) Type of the log entry
log_sub_type nvarchar(64) Sub type of the log entry
log_description ntext Short description of the log entry
ext_reference int SR number connected to the log entry, if applicable. Corresponds to the "id" field in the "service_req" table
user_name nvarchar(64) User who made the change. Corresponds to the "user_name" field in the "sysaid_user" table
ext_reference2 int SR# to which activity created from a chat between the admin and this computer was added. Corresponds to the "id" field in the "service_req" table

 

computer_changes

 

Stores additional information for entries to the asset Activity Log.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
computer_id nvarchar(64) ID of the computer that had the change. Corresponds to the "computer_id" field in the "computer" table
change_time datetime Time of the change
change_type nvarchar(64) Type of the change
change_sub_type nvarchar(64) Sub type of the change
change_description ntext A description of the change
log_id int ID# of the change in this log
policy_id int ID# of the change in this policy

 

user2asset

 

Records the contents of the Users field on the Asset form.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
user_name nvarchar(64) Username of the user connected to the asset. Corresponds to the "user_name" field in the "sysaid_user" table
computer_id nvarchar(64) ID of the asset the user is connected to. Corresponds to the "computer_id" field in the "computer" table

 

software, software_history

 

Contains all fields on the Software form.

 

Field Type Description
software_id int ID# of the software product
account_id nvarchar(32) SysAid account ID
product_name nvarchar(255) Name you give to the software product
version nvarchar(64) Version of the software product
vendor nvarchar(64) Vendor of the software product
licenses int Number of licenses of the software product that you've paid for
purchase_date datetime Date the software product was purchased
support_expiration datetime Date the support for the software product ends
notes ntext Notes about the software product
company int Company the software product belongs to. Corresponds to the "company_id" in the "company" table
company_backup int For internal use
supplier int Supplier of the software product. Corresponds to the "supplier_id" field in the "supplier" table
cust_list1 int Software custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "softwareList1"
cust_list2 int Software custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "softwareList2"
cust_text1 nvarchar(255) Software custom text 1
cust_text2 nvarchar(255) Software custom text 2
cust_notes ntext Software custom notes
cust_int1 int Software custom integer 1
cust_int2 int Software custom integer 2
cust_date1 datetime Software custom date 1
cust_date2 datetime Software custom date 2
history_version int Version of the software product (for the software product history). Incremented by one on each save of the software product
change_time datetime History table only. Time the revision was saved
changed_by nvarchar(64) History table only. Username of the user who saved the revision. Corresponds to the "user_name" field in the "sysaid_user" table
freeware nvarchar(1) Indicates if the product is freeware (0) or licensed (1)
exceedlic_installed int Number of licenses of the software product installed beyond the licensed amount
filter_xml    

 

software_files

 

Holds software item attachments. Click here to view the structure of the attachments table.

 

software_links

 

Stores software hyperlinks. Click here to view the structure of the links table.

 

software2install_name

 

Records which install names are linked to which software products.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
software_id int ID# of the software product. Corresponds to the "software_id" field in the "software" table
install_name nvarchar(255) Install name connected to the software product. Corresponds to the "value" field in the "computer_lists" table
version varchar(255) PK changed: (software_id,install_name,version)
name_and_version varchar(512)  

 

asset_catalog, asset_catalog_history

 

Contains all fields on the Catalog form.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
catalog_number nvarchar(64) Catalog item ID
name nvarchar(255) Catalog item name
model nvarchar(64) Model of the catalog item
manufacturer nvarchar(64) Manufacturer of the catalog item
supplier_id int ID# of the supplier of the catalog item. Corresponds to the "supplier_id" field in the "supplier" table
notes ntext Notes about the catalog item
cust_date1 datetime Catalog item custom date 1
cust_date2 datetime Catalog item custom date 2
version int Version of the catalog item. Updated each time the catalog item is saved. Used by asset_catalog_history 
change_time datetime History table only. Time the revision was saved
changed_by nvarchar(64) History table only. Username of the user who saved the revision. Corresponds to the "user_name" field in the "sysaid_user" table

 

asset_catalog_files

 

Holds catalog item attachments. Click here to view the structure of the attachments table.

 

asset_catalog_links

 

Stores catalog item hyperlinks. Click here to view the structure of the links table.

 

supplier, supplier_history

 

Contains all fields on the Supplier form.

 

Field Type Description
supplier_id int ID# of the supplier
account_id nvarchar(32) SysAid account ID
name nvarchar(255) Name of the supplier
address ntext Address of the supplier
phone nvarchar(64) Phone number of the supplier
fax nvarchar(64) Fax number of the supplier
email_address nvarchar(64) Email address of the supplier
notes ntext Notes about the supplier
mobile nvarchar(64) Mobile number of the contact person at the supplier
phone2 nvarchar(64) A second phone number for the supplier
contact_name nvarchar(64) Name of the contact person at the supplier
account_number nvarchar(64) Your account number with the supplier
cust_list1 int Supplier custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "supplierList2"
cust_list2 int Supplier custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "supplierList1"
cust_text1 nvarchar(255) Supplier custom text 1
cust_text2 nvarchar(255) Supplier custom text 2
cust_notes ntext Supplier custom notes
cust_int1 int Supplier custom integer 1
cust_int2 int Supplier custom integer 2
cust_date1 datetime Supplier custom date 1
cust_date2 datetime Supplier custom date 2
version int Version of the supplier. Incremented by one on each save of the supplier. Used by the asset history
change_time datetime History table only. Time the revision was saved
changed_by nvarchar(64) History table only. Username of the user who saved the revision. Corresponds to the "user_name" field in the "sysaid_user" table

 

supplier_files

 

Holds supplier attachments. Click here to view the structure of the attachments table.

 

supplier_links

 

Stores supplier hyperlinks. Click here to view the structure of the links table.

 

discovery_service

 

Records information about the different Remote Discovery Service (RDS) nodes reporting to SysAid.

 

Field Type Description
id int ID# of the RDS node
discovery_service_name nvarchar(64) Name of the RDS node
create_date datetime Date the RDS node was installed
upgrade_date datetime Date the RDS node was upgraded to a newer version
last_connection_date datetime Last time the RDS node contacted the SysAid Server
description nvarchar(255) For future use
ip_address nvarchar(64) IP address of the machine hosting the RDS node
location nvarchar(64) For future use
version nvarchar(64) Version of the SysAid Remote Discovery Service
owner nvarchar(64) For future use
domain nvarchar(255) Domains visible to the RDS node
windows nchar(1) Whether the hosting computer is Windows based (Y) or not (N)
rds_url nvarchar(255) URL that RDS attempts to connect to (e.g. the SysAid Server URL)
client_version nvarchar(64) Displays the latest version of the SysAidagent that RDS deploys. This field updates when RDS downloads a new version of the agent from the SysAid server
gfi_version nvarchar(64)  
gfi_build nvarchar(64)  
pgfi_url nvarchar(255)  
sr_notif_sent int  
mail_notif_sent int  
sms_notif_sent int  
rds_unavailable int  
logs_date nvarchar(255)  
log_level varchar(64)  
pm_relay_frequency int  
pm_relay_default_start_time int  

 

customized_snmp_oids

 

Stores entries to the list of Customized SNMP OIDs.

 

Field Type Description
id int Customized OID ID#
oid nvarchar(255) Customized OID address
display_name nvarchar(255) Customized OID display name
mapped_field int Asset field to which to import data. You may view the HTML source for CustomizedSnmpOids.jsp for a full list of which integers correspond to which fields
filter_name nvarchar(255) Contains the query created by the OID's Filter as it appears in the SysAid Expression Builder
filter_expression nvarchar(255) Contains the query created by the OID's Filter
filter_node ntext Contains the query created by the OID's Filter in XML format
is_writable nvarchar(1) Determines whether an OID can be written to from within SysAid (1) or not (0)
addon_db_name nvarchar(64) When mapping to custom asset fields added to the DB from Customize > Entities, this field shows the DB name of the mapped field. Corresponds to a custom field in the "computer" table

 

online_users

 

List of online users as reported by the SysAid Agent.

 

Field Type Description
id int ID# of the online user in this table
account_id nvarchar(32) SysAid account ID
user_name nvarchar(64) Username of the online user. Corresponds to the "user_name" field in the "sysaid_user" table
computer_id nvarchar(64) ID of the computer to which the user is logged in. Corresponds to the "computer_id" field in the "computer" table
computer_name nvarchar(64) Name of the computer to which the user is logged in. Corresponds to the "computer_name" field in the "computer" table
domain nvarchar(64) Domain of the logged in user (LDAP users only). Corresponds to the "login_domain" field in the "sysaid_user" table
client_name nvarchar(64) Method in which the user is logged in (e.g. console)
ip_address nvarchar(64) IP address from which the user is logged in (only shown if the user is logged in remotely and not from the console)
session_id nvarchar(64) ID number of the user's session on the logged in computer
last_update_date datetime Last time the logged in computer contacted the SysAid Server
disconnected int Whether the online user is connected to the computer's console (0) or not (1) (e.g. locked computer)

 

online_users_history

 

History of online_users table. Allows to see logins/logoffs of various assets in the network.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
id int ID# of the online user in this table
user_name nvarchar(64) Username of the user who had been online. Corresponds to the "user_name" field in the "sysaid_user" table
computer_id nvarchar(64) ID of the computer to which the user was logged in. Corresponds to the "computer_id" field in the "computer" table
computer_name nvarchar(64) Name of the computer to which the user was logged in. Corresponds to the "computer_name" field in the "computer" table
domain nvarchar(64) Domain of the user who had been logged in (LDAP users only). Corresponds to the "login_domain" field in the "sysaid_user" table
client_name nvarchar(64) Method in which the user was logged in (e.g. console)
ip_address nvarchar(64) IP address from which the user was logged in (only shown if the user is logged in remotely and not from the console)
session_id nvarchar(64) ID number of the user's session on the computer that had been logged in
login_time datetime Time the user logged in
logout_time datetime Time the user logged out

 

asset_offline_log

 

Records asset availability

 

Field Type Description
asset_id nvarchar(64) ID of the asset. Corresponds to the "computer_id" field in the "computer" table
offline_start_time datetime Time the asset went offline
offline_end_time datetime Time the asset came back online
offline_minutes int Time in minutes that the asset was offline
id int  

 

online_assets

 

Contains a list of all online assets that have the Agent installed.

 

Field Type Description
asset_id nvarchar(64) ID of the asset. Corresponds to the "computer_id" field in the "computer" table
is_online int Whether the asset is online (1) or not (0)
last_update datetime The last time the computer checked in with the SysAid Server

 

mdm_policy

 

Contains all MDM policies.

 

Field Type Description
id int ID# of the MDM policy
name nvarchar(255) Name of the policy
request_time datetime Time the policy was last edited
revision int Revision of the device policy. Incremented by 1 each time the policy is saved
enable_password nchar(1) Determines whether a mobile device requires a passcode (Y) or not (N)
allow_simple_password nchar(1) Determines whether a mobile device permits a simple passcode (Y) or not (N)
alphanumeric_password_req nchar(1) Determines whether a mobile device requires an alphanumeric passcode (Y) or not (N)
min_password_length int Minimum passcode length
min_complex_password int Minimum number of complex characters in the passcode
max_password_age int Maximum passcode length in days before user needs to change it
auto_lock int Time in minutes before mobile device locks automatically
password_history int Number of unique passcodes required before a passcode can be reused
max_failed_password int Maximum number of failed passcode attempts before all data on mobile device is erased
email_type int Email protocol used for the mobile device: IMAP (1), POP (2), Exchange ActiveSync (3)
account_name nvarchar(255) Name of the email account specified by the policy
path_prefix_imap nvarchar(255) When using the IMAP protocol, specifies the path prefix
user_display_name nvarchar(255) Display name of the user of the mobile device to which the policy is applied
email_address nvarchar(64) Email address of the user of the mobile device to which the policy is applied
host_name nvarchar(64) Host name of the incoming mail server
server_port nvarchar(32) Port on which to access the incoming mail server
user_name nvarchar(64) Username on the incoming mail server of the user of the mobile device to which the policy is applied
auth_type int The authentication method used by the incoming mail server: Password (1), MD5 Challenge Response (2), NTLM (3), HTTP MD5 Digest (4)
use_ssl nchar(1) Whether to use SSL for communication with the incoming mail server (Y) or not (N)
outoging_host_name nvarchar(64) Host name of the outgoing mail server
outgoing_server_port nvarchar(32) Port on which to access the outgoing mail server
outgoing_user_name nvarchar(64) Username on the outgoing mail server of the user of the mobile device to which the policy is applied
outgoing_auth_type int The authentication method used by the outgoing mail server: Password (1), MD5 Challenge Response (2), NTLM (3), HTTP MD5 Digest (4)
outgoing_use_ssl nchar(1) Whether to use SSL for communication with the outgoing mail server (Y) or not (N)
domain_name nvarchar(64) Domain name of the user of the mobile device to which the policy is applied
sync_emails_date_range int Time frame to synch emails to the user's mobile device: Unlimited (0), Three days (3), One week (7), Two weeks (14), One month (31)
passcode_grace_period int Maximum grace period to unlock a locked mobile device without needing a passcode: Immediately (0), 1 Minute (1), 5 Minutes (2), 15 Minutes (3), 1 Hour (4), 4 Hours (5)

 

mdm_wifi_policy

 

Contains all Wi-Fi policies for MDM.

 

Field Type Description
id int ID# of the Wi-Fi configuration
policy_id int MDM policy to which the Wi-Fi policy is connected. Corresponds to the "id" field in the "mdm_policy" table
type int Type of Wi-Fi encryption: Open (1), WEP (2), WPA/WPA2 (3)
ssid nvarchar(255) SSID of the wireless network configured
auto_join nchar(1) Whether to connect automatically to the network if it's within range (Y) or not (N)
hidden_network nchar(1) Whether the network is hidden (Y) or not (N)
encryption_key nvarchar(64) Password for the wireless network
revision int Revision of the Wi-Fi policy. Incremented by 1 each time the policy is saved

 

users_remote_assets

 

Specifies which users can perform My Desktop sessions to which computers.

 

  Field Type Description
  id int ID# of the assignment
  user_name nvarchar(64) Username of the user assigned to the asset. Corresponds to the "user_name" field in the "sysaid_user" table
  login_domain nvarchar(64) Domain of the user assigned to the asset. Corresponds to the "login_domain" field in the "sysaid_user" table
  login_user_upper nvarchar(64) User's login name in all uppercase letters
  computer_id nvarchar(64) Asset assigned to the user. Corresponds to the "computer_id" field in the "computer" table
  description nvarchar(255) Description of the asset
  note nvarchar(255) Note about the asset

 

remote_active_sessions

 

Contains a list of all active My Desktop sessions.

 

  Field Type Description
  user_name nvarchar(64) Username of the user with an active My desktop session. Corresponds to the "user_name" field in the "sysaid_user" table
  source_host nvarchar(64) Name of the computer that launched the My Desktop session
  target_host nvarchar(64) Computer being remotely controlled by the My Desktop session
  session_id nvarchar(64) ID of the My Desktop session
  rcg nvarchar(64) Computer hosting the SysAid Remote Control Gateway (RCG) being used for the My Desktop session
  session_start_time bigint Time the My Desktop session was initiated

 

account_attributes

 

Contains a list of all active My Desktop sessions.

 

  Field Type Description
  user_name nvarchar(64) Username of the user with an active My desktop session. Corresponds to the "user_name" field in the "sysaid_user" table
  source_host nvarchar(64) Name of the computer that launched the My Desktop session
  target_host nvarchar(64) Computer being remotely controlled by the My Desktop session
  session_id nvarchar(64) ID of the My Desktop session
  rcg nvarchar(64) Computer hosting the SysAid Remote Control Gateway (RCG) being used for the My Desktop session
  session_start_time bigint Time the My Desktop session was initiated
  gateway_api_base_url varchar(255)  
  gateway_api_mode varchar(20)  
  default_charset varchar(255)  
  default_export_import_charset varchar(255)  
  default_locale varchar(255)  
  case_insensitive_login numeric(1,0)  
  enable_usage_statistics numeric(1,0)  
  enable_it_benchmark numeric(1,0)  
  show_it_benchmark numeric(1,0)  
  enable_oauth numeric(1,0)  
  enable_asset_availability numeric(1,0)  
  enable_update_timers numeric(1,0)  

 

asset_data_day_data

 

 

  Field Type Description
  account_id nvarchar(32)  
  computer_id nvarchar(64)  
  check_name nvarchar(200)  
  idx int  
  check_value float  
  upd_time date  

 

asset_data_week_data

 

 

  Field Type Description
  account_id nvarchar(32)  
  computer_id nvarchar(64)  
  check_name nvarchar(200)  
  idx int  
  check_value float  
  upd_time date  

 

asset_data_month_data

 

 

  Field Type Description
  account_id nvarchar(32)  
  computer_id nvarchar(64)  
  check_name nvarchar(200)  
  idx int  
  check_value float  
  upd_time date  

 

asset_data_year_data

 

 

  Field Type Description
  account_id nvarchar(32)  
  computer_id nvarchar(64)  
  check_name nvarchar(200)  
  idx int  
  check_value float  
  upd_time date  

 

patch

 

 

  Field Type Description
  account_id nvarchar(32)  
  patch_id nvarchar(64)  
  product_name nvarchar(255)  
  vendor nvarchar(255)  
  name nvarchar(255)  
  file_digest nvarchar(4000)  
  bulletin nvarchar(64)  
  title nvarchar(4000)  
  max_download_size int  
  classification int  
  severity_type nvarchar(64)  
  release_date date  
  kb_article_id nvarchar(64)  
  switches nvarchar(4000)  
  url nvarchar(4000)  
  file_url nvarchar(4000)  
  language nvarchar(4000)  
  uninstallable int  
  security_update int  

 

computer_patches

 

 

  Field Type Description
  account_id nvarchar(32)  
  patch_id nvarchar(64)  
  computer_id nvarchar(64)  
  patch_status int  
  failure_reason nvarchar(255)  
  change_id int  
  installed_date date  
  manual_event_id int  
  start_schedule_date_time date  

 

patch_policy

 

 

  Field Type Description
  policy_id int  
  account_id nvarchar(32)  
  policy_name nvarchar(255)  
  policy_date date  
  last_scan date  
  next_scan date  
  last_patch date  
  next_patch date  
  last_scan_server_time date  
  last_patch_server_time date  
  scan_schedule    
  patch_schedule    
  reboot_settings    

 

gfi_products

 

 

  Field Type Description
  product_id int  
  vendor nvarchar(255)  
  product_name nvarchar(255)  

 

policy_gfi_products

 

 

  Field Type Description
  policy_id int  
  product_id int  
  approve_stat int  

 

patch_policy_status

 

 

  Field Type Description
  policy_id int  
  patch_id nvarchar(64)  
  patch_status int  

 

patch_policy_event

 

 

  Field Type Description
  id int  
  policy_id int  
  user_name nvarchar(64)  
  event_time date  
  is_scan int  
  is_manual int  

 

credentials_list

 

 

  Field Type Description
  id int  
  name varchar(256)  
  user_name varchar(256)  
  password varchar(256)  
  authentication_protocol varchar(256)

SNMP authentication protocol:

  • MD5

  • SHA
  encryption_protocol varchar(256)

SNMP encryption protocol:

  • DES

  • TRIPLE_DES

  • AES_128_BIT

  • AES_192_BIT

  • AES_256_BIT
  encryption_key varchar(256) SNMP encryption key
  description mediumtext  
  validity int  
  created_by varchar(255)  
  created_date date  
  modified_by varchar(255)  
  modified_date date  

 

vendors_list

 

 

  Field Type Description
  id int  
  manufacturer_name varchar(256)  
  asset_id_prefix varchar(256)  
  manufacturer_codes mediumtext  
  description mediumtext  
  created_date date  
  modified_by varchar(255)  
  modified_date date  

 

agent_settings

 

 

Field Type Description
enable_video_recording char(1) Default: ‘Y’

 

 

 

Users

 

sysaid_user, sysaid_user_history

 

Contains all fields on the User form. This table holds both administrators and end users. Admins and end users are differentiated by the value in the "administrator" field.

 

Field Type Description
user_name nvarchar(64) The username of the user
account_id nvarchar(32) SysAid account ID
password nvarchar(64) The user's password
first_name nvarchar(64) The user's first name
last_name nvarchar(64) The user's last name
main_user nchar(1) Indicates whether this is the main SysAid user created during installation (Y) or not (N). There is only one main user
email_address nvarchar(64) The user's email address
sms_number nvarchar(255) The phone number of the user's mobile device
user_conf ntext Contains all personal preferences for a given user
phone nvarchar(64) The user's phone number
cell_phone nvarchar(64) The user's cell phone number
notes nvarchar(255) Notes about the user
location int The user's location. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "location"
car_number nvarchar(64) The user's license plate number
building nvarchar(64) The building where the user is located
floor nvarchar(64) The floor where the user is located
cubic nvarchar(64) The cubicle where the user is located
administrator nchar(1) Whether the user is an administrator (Y) or an end user (N)
manager nchar(1) Whether the user has access to the Manager Portal (Y) or not (N)
version int Version of the user profile. Incremented by one on each save of the profile. Used by the user profile history
cust_list1 int User custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "userList1"
cust_list2 int User custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "userList2"
cust_text1 nvarchar(255) User custom text 1
cust_text2 nvarchar(255) User custom text 2
cust_notes ntext User custom notes
cust_int1 int User custom integer 1
cust_int2 int User custom integer 2
department int The user's department. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "departments"
company int The user's company. Corresponds to the "company_id" in the "company" table
company_backup int For internal use
disable nchar(1) Whether the user is disabled (Y) or not (N)
expiration_time datetime Date the user's access to SysAid expires
cust_date1 datetime User custom date 1
cust_date2 datetime User custom date 2
history_version int Version of the user's profile. Incremented by one on each save of the user profile. Used by the user history
ldap int Indicates whether a user is an enabled LDAP user (1), a disabled LDAP user (2), a disabled manually created user (3), or an enabled manually created user (4)
change_time datetime History table only. Time the revision was saved
changed_by nvarchar(64) History table only. Username of the user who saved the revision. Corresponds to the "user_name" field in the "sysaid_user" table
email_notifications nchar(1) Whether the user receives email notifications (Y) or not (N)
permissions_by_groups nchar(1) Applicable to admins only: Whether the admins permissions are managed by group (Y) or individually (N)
user_manager_name nvarchar(64) Username of the user's direct manager. Corresponds to another "user_name" in the "sysaid_user" table
chat_nick_name nvarchar(64) Name to display during chat sessions
enable_login_to_eup nchar(1) Determines whether the user can log in to the End-User Portal (Y) or not (N)
agreement int SLA agreement applied to the user. Corresponds to the "id" field in the "agreement" table
display_name nvarchar(64) Name to display for this user throughout SysAid
secondary_email nvarchar(64) Alternative user email address, used for Password Self Service unlock account and reset password
sr_email_notif_condition ntext Contains the filter for which automatic SR email notifications the user receives
login_user nvarchar(64) Username that the user uses to log into SysAid
login_domain nvarchar(64) Domain that the user uses to log into SysAid
login_guid nvarchar(64) Unique login ID for LDAP users. Randomly generated for non-LDAP users
calculated_user_name nvarchar(255) "first_name" + "last_name", or if both are blank, shows "user_name"
calculated_user_name_upper nvarchar(255) The "calculated_user_name" in all uppercase letters
locale nvarchar(64) User's chosen language. A full list of language options can be seen by viewing the HTML source for Preferences.jsp
timezone nvarchar(64) User's time zone. A full list of time zone options can be seen by viewing the HTML source for Preferences.jsp
charset nvarchar(64) Encoding used to display the user's chosen language
login_user_upper nvarchar(64) "login_user" in all uppercase letters
user_name_upper nvarchar(64) "user_name" in all uppercase letters
ssp_theme int(11) Default: 0

 

sysaid_user_files

 

Holds user attachments. Click here to view the structure of the attachments table.

 

sysaid_user_links

 

Stores user hyperlinks. Click here to view the structure of the links table.

 

sysaid_user_permissions

 

Stores permissions for users who are administrators.

 

Field Type Description
user_name nvarchar(64) Username of the administrator. Corresponds to the "user_name" field in the "sysaid_user" table
permission_conf ntext List of the administrator's permissions

 

user_groups

 

List of all user groups. Whether the group is for admins only, end users only, or both is determined by the "group_type" field.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
group_name nvarchar(64) Name of the user group
group_type int Type of the user group: general (0), administrators only (1), or end users only (2)
support_level int Support level of the group
permission ntext For groups with group permissions, lists all permissions for the group
display_group nchar(1) Whether the group appears in the Admin Group field on SRs (Y) or not (N)

 

user2group

 

Lists which users are part of each group.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
group_name nvarchar(64) Name of the user group. Corresponds to the "group_name" field in the "user_groups" table
user_name nvarchar(64) Name of the user in the group. Corresponds to the "user_name" field in the "sysaid_user" table

 

 

company, company_history

 

Contains all fields on the Company form.

 

Field Type Description
company_id int ID# of the company
account_id nvarchar(32) SysAid account ID
company_name nvarchar(255) Name of the company
address nvarchar(255) Line 1 of the company address
address2 nvarchar(255) Line 2 of the company address
city nvarchar(64) City where the company is located
state nvarchar(64) State where the company is located
zip nvarchar(64) Company's zip code
country nvarchar(64) Country where the company is located
phone nvarchar(64) Company's phone number
fax nvarchar(64) Company's fax number
notes ntext Notes about the company
cust_list1 int Company custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "companyList1"
cust_list2 int Company custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "companyList1"
cust_text1 nvarchar(255) Company custom text 1
cust_text2 nvarchar(255) Company custom text 2
cust_notes ntext Company custom notes
cust_int1 int Company custom int 1
cust_int2 int Company custom int 2
expiration_time datetime Date that the company's access to SysAid expires
cust_date1 datetime Company custom date 1
cust_date2 datetime Company custom date 2
version int Version of the company's profile. Incremented by one on each save of the company profile. Used by the company history
change_time datetime History table only. Time the revision was saved
changed_by nvarchar(64) History table only. Username of the user who saved the revision. Corresponds to the "user_name" field in the "sysaid_user" table
agreement int SLA agreement applied to the company. Corresponds to the "id" field in the "agreement" table
agreement_start datetime Start of your service agreement with the company
agreement_end datetime End of your service agreement with the company
logo_file_name nvarchar(64) Filename of the company logo you've uploaded to SysAid
email_account nvarchar(255)  
ssp_banner int Default: 0
ssp_theme int Default: 0

 

company_files

 

Holds company attachments. Click here to view the structure of the attachments table.

 

company_links

 

Stores company hyperlinks. Click here to view the structure of the links table.

 

uss_security_questions

 

Stores all security questions that a user can choose for Password Services.

 

Field Type Description
id int ID# of the security question
account_id nvarchar(32) SysAid account ID
security_question nvarchar(255) The security question
visible nvarchar(1) Whether the question is visible (Y) or not (N)
mandatory nvarchar(1) Whether the question is mandatory (Y) or not (N)

 

user_questions

 

Records which questions each user has selected to answer for Password Services, as well as their answers.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
user_name nvarchar(64) Username of the user who answered a question. Corresponds to the "user_name" field in the "sysaid_user" table
question_id int ID# of the question answered by the user. Corresponds to the "id" field in the "uss_security_questions" table
answer nvarchar(255) User's answer to the security question

 

user_answer_attempts

 

Records the number of unsuccessful user attempts to answer their Password Services security questions.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
user_name nvarchar(64) Username of the user who attempted to answer a security question. Corresponds to the "user_name" field in the "sysaid_user" table
attempts int Number of unsuccessful attempts to answer a security question that the user has made
lock_date datetime Time that the user was locked out of Password Self Service

 

user_settings_available_fields

 

Field Type Description
id nvarchar(255)  
label nvarchar(255)  
type nvarchar(32)  
db_table nvarchar(64)  
db_column nvarchar(64)  
list_attribute_name nvarchar(64)  
user_conf_name nvarchar(64)  
readOnly bit Default:0

 

uss_notif_events

 

Lists all Password Services events that trigger a notification, as well as the notification to be sent.

 

Field Type Description
id int ID# of the notification event
account_id nvarchar(32) SysAid account ID
event_name nvarchar(64) Type of event that triggered the notification: (ResetPassword) or (UnlockAccount)
notification nvarchar(64) Notification for the Password Services event. Notifications are stored in the account conf, located in the "account_conf" field in the "account" table
filter_expression ntext Contains the query created by the notification's Filter Expression as it appears in the SysAid Expression Builder
filter_sql ntext Contains the query created by the notification's Filter Expression

 

ldap_list

 

Field Type Description
ldap_id int(11) auto_increment
name nvarchar(255)  
url nvarchar(255)  
credentials_id int(11)  
domain nvarchar(255)  
domain_display_name nvarchar(255)  
rds_name nvarchar(255)  
disable_time datetime  
enable_cashing numeric(1,0) default 1

user_class_filter

varchar(400)

 

user_filter

varchar(400)

 

group_filter

varchar(400)

 

import_groups

numeric(1,0)

default 0

include_sub_ou

numeric(1,0)

default 0

authentication_type

varchar(255)

 

full_update

numeric(1,0)

default 0

last_update_date

varchar(255)

 

user_att_name

varchar(255)

 

group_att_name

varchar(255)

 

user_att_cn

varchar(255)

 
user_att_dn varchar(255)  

disable_non_exist_users

numeric(1,0)

 
disable_non_exist_admins numeric(1,0)  
last_login_user varchar(255)  
last_login_date datetime  
schedule    
next_run    
rerun_value    

ldap_enable

numeric(1,0)

Default:0

 

ldap_user_dn_list

 

Field Type Description
ldap_id int(11)  

user_dn_id

int(11)  

user_dn

nvarchar(255)  

 

ldap_user_roots_list

 

Field Type Description
ldap_id int(11)  

user_roots_id

int(11)  

user_roots

   

 

ldap_group_roots_list

 

Field Type Description
ldap_id int(11)  

group_roots_id

int(11)  

group_roots

   

 

ldap_user_attribute_list

 

Field Type Description
ldap_id int(11)  

user_attribute_id

int(11)

 

sysaid_attribute

varchar(255)

 

ldap_attribute

varchar(255)

 

 

 

 

Projects and Tasks

 

project, project_history

 

Contains all fields on the Project form.

 

Field Type Description
id int Project ID#
account_id nvarchar(32) SysAid account ID
version int Version of the project. Incremented by one on each save of the project. Used by the project history
category int Project category. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "projectCats"
title nvarchar(255) Title of the project
description ntext Description of the project
status int Project status.  Corresponds to the "value_key" field in the "cust_values" table for "list_name" "projectStatuses"
notes ntext Notes about the project
start_time datetime Project start time
end_time datetime Project end time
raw_estimation int Estimated time to complete project in hours
request_group nvarchar(64) Group that requested the project. Corresponds to the "group_name" field in the "user_groups" table
manager nvarchar(64) Project manager. Corresponds to the "user_name" field in the "sysaid_user" table
assigned_group nvarchar(64) Group assigned to the project. Corresponds to the "group_name" field in the "user_groups" table
cust_list1 int Project custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "prList1"
cust_list2 int Project custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "prList2"
cust_text1 nvarchar(255) Project custom text 1
cust_text2 nvarchar(255) Project custom text 2
cust_notes ntext Project custom notes
cust_int1 int Project custom integer 1
cust_int2 int Project custom integer 2
company int Company for which the project is being done. Corresponds to the "company_id" in the "company" table
company_backup int For internal use
incidentTitle nvarchar(255) Title to give to incidents created by notifications from this project
cust_date1 datetime Project custom date 1
cust_date2 datetime Project custom date 2
progress int Progress towards completion of the project. Displayed as a %

 

project_log

 

Contains the contents of the project history tab.

 

Field Type Description
log_id int ID# of the log entry
account_id nvarchar(32) SysAid account ID
project_id int ID# of the associated project. Corresponds to the "id" field in the "project" table
log_time datetime Time the project was updated
log_type nvarchar(64) Type of update made to the project
log_description ntext Description of the update made to the project
ext_reference int Entry number of the log for a given project
user_name nvarchar(64) Username of the user who updated the project. Corresponds to the "user_name" field in the "sysaid_user" table

 

project_files

 

Holds project attachments. Click here to view the structure of the attachments table.

 

project_links

 

Stores project hyperlinks. Click here to view the structure of the links table.

 

task, task_history

 

Contains all fields on the Task form.

 

Field Type Description
id int ID# of the task
account_id nvarchar(32) SysAid account ID
version int Version of the task. Incremented by one on each save of the task. Used by the task history
project_id int ID# of the parent project. Corresponds to the "id" field in the "project" table
category int Task category. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "taskCats"
title nvarchar(255) Title of the task
description ntext Description of the task
status int Status of the task. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "taskStatuses"
notes ntext Notes about the task
progress int Progress of the task. Measured as a %
start_time datetime Task start time
end_time datetime Task end time
estimation int Estimated time needed to complete the task (in hours)
cust_list1 int Task custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "taskList1"
cust_list2 int Task custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "taskList2"
cust_text1 nvarchar(255) Task custom text 1
cust_text2 nvarchar(255) Task custom text 2
cust_notes ntext Task custom notes
cust_int1 int Task custom integer 1
cust_int2 int Task custom integer 2
notification nvarchar(64) Notification to send for this task. Notifications are stored in the account conf, located in the "account_conf" field in the "account" table
cust_date1 datetime Task custom date 1
cust_date2 datetime Task custom date 2
ci_id int ID# of the CI attached to the task. Corresponds to the "id" field in the "ci_attributes" table
task_dependency int ID# of the task upon which this task is dependent. Corresponds to another "id" in the "task" table
task_dependency_type int Type of task dependency: Start to Start (1), Start to End (2), End to End (3), End to Start (4)

 

task_log

 

Contains the contents of the task history tab.

 

Field Type Description
log_id int ID# of the log entry
account_id nvarchar(32) SysAid account ID
task_id int ID# of the associated task. Corresponds to the "id" field in the "task" table
log_time datetime Time the task was updated
log_type nvarchar(64) Type of update made to the task
log_description ntext Description of the update made to the task
ext_reference int Entry number of the log for a given task
user_name nvarchar(64) Username of the user who updated the task. Corresponds to the "user_name" field in the "sysaid_user" table

 

task_users

 

Records the users assigned to a task.

 

Field Type Description
id int ID# of the table entry
account_id nvarchar(32) SysAid account ID
user_name nvarchar(64) Username of the user assigned to the task. Corresponds to the "user_name" field in the "sysaid_user" table
user_role int The users role in the project. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "userRoles"

 

task_activities

 

List of task activities.

 

Field Type Description
id int Activity#
task_id int ID# of the task the activity is attached to. Corresponds to the "id" field in the "task" form
account_id nvarchar(32) SysAid account ID
user_name nvarchar(64) Username of the user who left the activity. Corresponds to the "user_name" field in the "sysaid_user" table
from_time datetime Activity start time
to_time datetime Activity end time
description nvarchar(4000) A description of the activity performed
activity_status int Status of the activity. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "activityStatuses"
ci_id int ID# of the CI attached to the activity. Corresponds to the "id" field in the "ci_attributes" table

 

task_files

 

Holds task attachments. Click here to view the structure of the attachments table.

 

task_links

 

Stores task hyperlinks. Click here to view the structure of the links table.

 

 

 

CMDB

 

ci_attributes, ci_history

 

Contains all fields on the CI form. CI types are differentiated using the "ci_type" field.

 

Field Type Description
id int CI ID#
account_id nvarchar(32) SysAid account ID
ci_name nvarchar(64) Name of the CI
serial nvarchar(64) Serial number of the CI
ci_type int Type of the CI. Corresponds to the "id" field in the "ci_type" table
location int CI location. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "location"
owner nvarchar(64) Owner of the CI. Corresponds to the "user_name" field in the "sysaid_user" table
owner_group nvarchar(64) Group that owns the CI. Corresponds to the "group_name" field in the "user_groups" table
company int Company that the CI belongs to. Corresponds to the "company_id" in the "company" table
company_backup int For internal use
supplier int Supplier of the CI. Corresponds to the "supplier_id" field in the "supplier" table
supply_date datetime Date the CI is supposed to arrive
accept_date datetime Date the CI actually arrives
status int Status of the CI. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "CIStatus"
priority int Priority of the CI. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "priority"
notes ntext Notes about the CI
import_item_id nvarchar(64) ID of the asset, name of the catalog item, or ID# of the software product from which the CI was imported. Corresponds to the "computer_id" field in the "computer" table, the "catalog_number" field in the "asset_catalog" table, or the "software_id" field in the "software" table
import_item_type int Entity from which the CI was imported: Not imported (0), Asset (1), Software Product (2), or Catalog Item (3)
import_desc nvarchar(255) History table only. Type and name of the import item
history_version int Version of the CI. Incremented by one on each save of the CI. Used by the CI history
ci_cust_text_1 - ci_cust_text_50 nvarchar(64) CI custom text 1 - 50
ci_cust_long_text_1 - ci_cust_long_text_50 ntext CI custom long text 1 - 50
ci_cust_date_1 - ci_cust_date_50 datetime CI custom date 1 - 50
ci_cust_list_1 - ci_cust_list_50 int CI custom list 1 - 50. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "ci_cust_list_1" - "ci_cust_list_50"
ci_cust_int_1 - ci_cust_int_50 int CI custom integer 1 - 50
change_time datetime History table only. Time the revision was saved
changed_by nvarchar(64) History table only. Username of the user who saved the revision. Corresponds to the "user_name" field in the "sysaid_user" table
problem_type nvarchar(64) Category of the CI. Corresponds to the "problem_type" field in the "problem_type" table
problem_sub_type nvarchar(64) Sub-category of the CI. Corresponds to the "problem_sub_type" field in the "problem_type" table
third_level_category nvarchar(64) Third-level category of the CI. Corresponds to the "third_level_category" field in the "problem_type" table
ci_sub_type int Sub-type of the CI. Corresponds to the "id" field in the "ci_sub_type" table

 

ci_files

 

Holds CI attachments. Click here to view the structure of the attachments table.

 

ci_links

 

Stores CI hyperlinks. Click here to view the structure of the links table.

 

ci_type

 

List of all CI types.

 

Field Type Description
id int ID# of the CI type
account_id nvarchar(32) SysAid account ID
ci_type_name nvarchar(64) Name of the CI type
description ntext Description of the CI type
predefined nvarchar(1) Whether the CI type is predefined (Y) or not (N)

 

ci_sub_type

 

List of all CI sub types.

 

Field Type Description
id int ID# of the CI sub type
account_id nvarchar(32) SysAid account ID
ci_type_id int ID# of the parent CI type. Corresponds to the "id" field on the "ci_type" table
caption nvarchar(64) Name of the CI sub type
file_name nvarchar(255) Filename of the icon file for the CI sub type

 

ci_relation

 

Records all CI relations using a CI relation type defined in the "ci_relation_type" table.

 

Field Type Description
src int ID# of the source CI. Corresponds to the "id" field in the "ci_attributes" table
dest int ID# of the destination CI. Corresponds to the "id" field in the "ci_attributes" table
ci_relation_type int Relation between the two Cis. Corresponds to the "id" field in the "ci_relation_type" table

 

ci_relation_type

 

Defines all possible CI relation types.

 

Field Type Description
id int CI relation type ID#
account_id nvarchar(32) SysAid account ID
relation_name nvarchar(64) Name of the relationship, such as "installed on"
opposite_relation_name nvarchar(64) Name of the relationship in the other direction, such as "contains software"
predefined nvarchar(1) Whether the relationship type is predefined (Y) or not (N)

 

ci_template

 

Holds CI templates.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
template_name nvarchar(64) Name of the CI template
ci_type int Type of the CI created by the template. Corresponds to the "id" field in the "ci_type" table
location int Location of the CI created by the template. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "location"
owner nvarchar(64) Owner of the CI created by the template. Corresponds to the "user_name" field in the "sysaid_user" table
owner_group nvarchar(64) Group that owns the CI created by the template. Corresponds to the "group_name" field in the "user_groups" table
company int Company that the CI created by the template belongs to. Corresponds to the "company_id" in the "company" table
company_backup int For internal use
supplier int Supplier of the CI created by the template. Corresponds to the "supplier_id" field in the "supplier" table
supply_date datetime Date the CI created by the template is supposed to arrive
accept_date datetime Date the CI created by the template actually arrives
status int Status of the CI created by the template. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "CIStatus"
priority int Priority of the CI created by the template. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "priority"
notes ntext Notes about the CI created by the template
ci_cust_text_1 - ci_cust_text_50 nvarchar(64) CI template custom text 1 - 50
ci_cust_long_text_1 - ci_cust_long_text_50 ntext CI template custom long text 1 - 50
ci_cust_date_1 - ci_cust_date_50 datetime CI template custom date 1 - 50
ci_cust_list_1 - ci_cust_list_50 int CI template custom list 1 - 50. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "ci_cust_list_1" - "ci_cust_list_50"
ci_cust_int_1 - ci_cust_int_50 int CI template custom integer 1 - 50
problem_type nvarchar(64) Category of the CI created by the template. Corresponds to the "problem_type" field in the "problem_type" table
problem_sub_type nvarchar(64) Sub-category of the CI created by the template. Corresponds to the "problem_sub_type" field in the "problem_type" table
third_level_category nvarchar(64) Third-level category of the CI created by the template. Corresponds to the "third_level_category" field in the "problem_type" table
ci_sub_type int Sub-type of the CI created by the template. Corresponds to the "id" field in the "ci_sub_type" table

 

ci_template_links

 

Holds hyperlinks for CI templates.

 

Field Type Description
template_name nvarchar(64) Name of the template to which the link belongs. Corresponds to the "template_name" field in the "ci_template" table
ci_type int Type of the CI created by the template. Corresponds to the "id" field in the "ci_type" table
account_id nvarchar(32) SysAid account ID
file_id nvarchar(64) Database name of the link, including full path
link nvarchar(255) The hyperlink itself
file_name nvarchar(255) Description of the link
file_date datetime Timestamp that the link was added

 

asset2ci

 

Stores data about which asset fields are mapped to which CI fields when importing assets to the CMDB.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
asset_field nvarchar(64) Field on the asset form to be mapped. Corresponds to fields in the "computer" table and in the "computer_attributes" table. Fields in the "computer" table are listed as "c.<fieldname>" and fields in the "computer_attributes" table are listed as "a.<fieldname>". A full list of supported fields can be seen in the HTML source for "ImportAsset2CISettings.jsp"
ci_field nvarchar(64) Field on the CI form to receive the mapped data. Corresponds to the different field names in the "ci_attributes" table. A full list of supported fields can be seen in the HTML source for "ImportAsset2CISettings.jsp"
position int Position in the list of mapped fields

 

user2ci

 

Records the contents of the Users field on the CI form.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
user_name nvarchar(64) User of the CI. Corresponds to the "user_name" field in the "sysaid_user" table
ci_id int ID# of the CI. Corresponds to the "id" field on the "ci_attributes" table

 

 

 

Monitoring

 

monitor_templates

 

Contains all monitoring templates.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
template_name nvarchar Name of the monitoring template
is_server nchar(1) Whether the template applies to servers (1) or not (0)
check_type nvarchar(32) The type of monitoring rule
check_name nvarchar(200) The name of the monitoring rule
protocol nvarchar(64) The protocol to use for monitoring rules that connect directly to another device (e.g. http, ping)
port_num int The port number checked by the monitoring template
url_path nvarchar(255) The URL checked by the monitoring template
expression nvarchar(255) The expression searched for in URL monitoring, software/hardware/device updates, running services checks, etc.
update_type nvarchar(64) The type of update to a computer (Software, Hardware, etc.)
warning_at int Warning trigger number
warning_notification nvarchar(64) Monitoring notification to send for a warning event. Notifications are stored in the account conf, located in the "account_conf" field in the "account" table
error_at int Error trigger number
error_notification nvarchar(64) Monitoring notification to send for an error event. Notifications are stored in the account conf, located in the "account_conf" field in the "account" table

 

monitoring_conf

 

Contains monitoring configurations per computer.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
computer_id nvarchar(64) ID of the monitored computer or name of the monitoring template. Corresponds to the field "computer_id" in the "computer" table or to the "template_name" field in the "monitor_templates" table
is_server nchar(1) Type of the monitoring rule: Workstation (0), Server (1), Network Device (4), Workstation Template (2), or Server Template (3)
template_name nvarchar(64) For assets attached to a template, the name of the attached template. Corresponds to the "template_name" field in the "monitor_templates" table
notification nvarchar(64) No data error notification. Notifications are stored in the account conf, located in the "account_conf" field in the "account" table
alert int Whether there is a no data notification alert (1) or not (0)
sr_sent int Indicates if an incident was opened after the no data notification error (1) or not (0)
mail_sent int Indicates if an email was sent after the no data notification error (1) or not (0)
sms_sent int Indicates if an SMS was sent after the no data notification error (1) or not (0)
monitoring_enabled nchar(1) Indicates whether monitoring is enabled (1) or not (0)

 

monitor_embed_data

 

Stores current monitoring results for each monitoring configuration.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
computer_id nvarchar ID of the monitored computer. Corresponds to the field "computer_id" in the "computer" table
is_server nchar(1) Whether the monitoring rule applies to servers (1) or not (0)
check_type nvarchar(32) The type of monitoring rule
check_name nvarchar(200) The target of the monitoring rule, e.g. Ping, memUsage, TCP/IP1527
protocol nvarchar(64) The protocol to use for monitoring rules that connect directly to another device (e.g. http, ping)
port_num int The port number checked by the monitoring template
url_path nvarchar(255) The URL checked by the monitoring template
expression nvarchar(255) The expression searched for in URL monitoring, software/hardware/device updates, running services checks, etc.
update_type nvarchar(64) The type of update to a computer (Software, Hardware, etc.)
warning_at int Warning trigger number
warning_notification nvarchar(64) Monitoring notification to send for a warning event. Notifications are stored in the account conf, located in the "account_conf" field in the "account" table
error_at int Error trigger number
error_notification nvarchar(64) Monitoring notification to send for an error event. Notifications are stored in the account conf, located in the "account_conf" field in the "account" table
idx_day int ID# of the last poll of this asset in the daily monitoring values table. Corresponds to the "idx" field in the "<monitor_type>_day_data" table
idx_week int ID# of the last poll of this asset in the weekly monitoring values table. Corresponds to the "idx" field in the "<monitor_type>_day_data" table
idx_month int ID# of the last poll of this asset in the monthly monitoring values table. Corresponds to the "idx" field in the "<monitor_type>_day_data" table
idx_year int ID# of the last poll of this asset in the yearly monitoring values table. Corresponds to the "idx" field in the "<monitor_type>_day_data" table
time_day datetime Time of the last poll of this asset
time_week datetime Time the last weekly average was calculated for this asset
time_month datetime Time the last monthly average was calculated for this asset
time_year datetime Time the last yearly average was calculated for this asset
warn_sr_sent int Indicates if a warning incident was opened after the latest monitoring warning (1) or not (0)
warn_mail_sent int Indicates if a warning email was sent after the latest monitoring warning (1) or not (0)
warn_sms_sent int Indicates if a warning SMS was sent after the latest monitoring warning (1) or not (0)
err_sr_sent int Indicates if an error incident was opened after the latest monitoring error (1) or not (0)
err_mail_sent int Indicates if an error email was sent after the latest monitoring error (1) or not (0)
err_sms_sent int Indicates if an error SMS was sent after the latest monitoring error (1) or not (0)
alert int Indicates the alert type for this monitoring rule: Normal (0), Warning (1), or Error (2)
extra_data nvarchar(255) Stores additional information for specific monitoring tests
predefined_check int Indicates if the monitoring rule is predefined (1) or not (0)

 

services_day_data

 

Stores monitoring results for services monitoring for the daily results table. Monitoring data is recorded at 5 minute intervals.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
computer_id nvarchar(64) ID of the monitored computer. Corresponds to the field "computer_id" in the "computer" table
check_name nvarchar(64) Name of the monitoring rule, monitored service/process, etc.
idx int ID# of the results from a single poll of the asset
check_value float Results of the poll of the asset. Can be binary (0/1) or a data value, depending upon the specific monitoring rule
check_value2 float For network activity, the outgoing data value
upd_time datetime Time the asset was polled

 

services_week_data

 

Stores monitoring results for services monitoring for the weekly results table. Each entry is a 30 minute average from the "services_day_data" table.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
computer_id nvarchar(64) ID of the monitored computer. Corresponds to the field "computer_id" in the "computer" table
check_name nvarchar(64) Name of the monitoring rule, monitored service/process, etc.
idx int ID# of the results from the average of 1/2 hour's worth of polls of the asset
check_value float Results of the average of 1/2 hour's worth of polls of the asset. Can be binary (0/1) or a data value, depending upon the specific monitoring rule
check_value2 float Results of the average of 1/2 hour's worth of polls of the asset. For network activity, the outgoing data value
upd_time datetime Time the average was taken

 

services_month_data

 

Stores monitoring results for services monitoring for the monthly results table. Each entry is a 2 hour average from the "services_day_data" table.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
computer_id nvarchar(64) ID of the monitored computer. Corresponds to the field "computer_id" in the "computer" table
check_name nvarchar(64) Name of the monitoring rule, monitored service/process, etc.
idx int ID# of the results from the average of two hours' worth of polls of the asset
check_value float Results of the average of two hours' worth of polls of the asset. Can be binary (0/1) or a data value, depending upon the specific monitoring rule
check_value2 float Results of the average of two hours' worth of polls of the asset. For network activity, the outgoing data value
upd_time datetime Time the average was taken

 

services_year_data

 

Stores monitoring results for services monitoring for the yearly results table. Each entry is a daily average from the "services_day_data" table.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
computer_id nvarchar(64) ID of the monitored computer. Corresponds to the field "computer_id" in the "computer" table
check_name nvarchar(64) Name of the monitoring rule, monitored service/process, etc.
idx int ID# of the results from the average of 1 day's worth of polls of the asset
check_value float Results of the average of 1 day's worth of polls of the asset. Can be binary (0/1) or a data value, depending upon the specific monitoring rule
check_value2 float Results of the average of 1 day's worth of polls of the asset. For network activity, the outgoing data value
upd_time datetime Time the average was taken

 

traps_data

 

Records SNMP traps received by the SysAid Server.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
computer_id nvarchar(64) ID of the asset that sent the trap. Corresponds to the field "computer_id" in the "computer" table
trap_0 int Number of traps of level 0 received by the SNMP device
trap_1 int Number of traps of level 1 received by the SNMP device
trap_2 int Number of traps of level 2 received by the SNMP device
trap_3 int Number of traps of level 3 received by the SNMP device
trap_4 int Number of traps of level 4 received by the SNMP device
trap_5 int Number of traps of level 5 received by the SNMP device
trap_6 int Number of traps of level 6 received by the SNMP device

 

monitor_events

 

Contains the data of the Monitoring Events.

 

Field Type Description
id int ID# of the monitoring event
severity int Type of the monitoring event: Normal (0), Warning (1), Error (2)
source nvarchar(255) ID of the asset that generated the monitoring event. Corresponds to the "computer_id" field in the "computer" table
source_name nvarchar(255) Name of the source computer
monitor_type nvarchar(64) Type of the monitoring rule
monitor_target nvarchar(255) The target of the monitoring rule, e.g. Ping, memUsage, TCP/IP1527
category nvarchar(64) Type of asset affected by the monitoring rule: Workstation (0), Server (1), Network Device (4), Workstation Template (2), or Server Template (3)
upd_time datetime Time the monitoring event was logged
check_value float Value returned by the monitoring rule. Can be binary (0/1), or a numerical value
expression nvarchar(255) The expression searched for in URL monitoring, software/hardware/device updates, running services checks, etc.
warning_threshold int Warning trigger number
warning_notification nvarchar(64) Monitoring notification to send for a warning event. Notifications are stored in the account conf, located in the "account_conf" field in the "account" table
error_threshold int Error trigger number
error_notification nvarchar(64) Monitoring notification to send for an error event. Notifications are stored in the account conf, located in the "account_conf" field in the "account" table
check_value1 float Stores incoming data usage for network monitoring
check_value2 float Stores outgoing data usage for network monitoring

 

Other monitoring tables

 

Following are the tables used to store collected monitoring data for each different monitor type. The structures of these tables are identical to the structures of the "services_period_data" tables listed above. Note that "_period_" is either "_day_", "_week_", "_month_", or "_year_", corresponding to the daily, weekly, monthly, and yearly tables for each monitor type.

 

Monitor Description
services_period_data Monitoring for computer services
processes_period_data Monitoring for computer processes
performance_period_data Monitoring for performance rules, such as HD usage, memory usage, CPU usage, etc.
comp_update_period_data Monitoring for software, hardware, and device updates
network_period_data Ports monitoring
asset_data_period_data Monitoring of asset data
network_activity_period_data Network usage monitoring
customized_period_data Monitoring of customized monitoring rules
url_period_data URL monitoring

 

 

 

SLA/SLM

 

agreement

 

Lists all SLAs you've created.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
id int ID# of the agreement
title nvarchar(255) Name of the agreement

 

measurements_lists, measurements_lists_history

 

Defines all measurement lists.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
id int ID# of the measurement list
title nvarchar(255) Name of the measurement list
field_name nvarchar(64) Name of the field used in the measurement. A complete list of available fields can be seen by viewing the HTML source of MeasurementListEdit.jsp
date_field nvarchar(64) Determines the date assigned to the data value when running the measurement. Can be either Request Time (insert_time) or Close Time (close_time)
sr_types nvarchar(64) Type of SRs included in the measurement: Incident (1), Problem (6), Change (4), Request (10)
include_statuses ntext Only SRs with the statuses specified here are included in the measurement list. Statuses are separated by commas. Statuses correspond to the "value_caption" field in the "cust_values" table for "list_name" "status"
filter_sql ntext Contains the query created by the measurement list's Filter Expression
filter_xml ntext Contains the query created by the measurement list's Filter Expression in XML format
filter_expression ntext Contains the query created by the measurement list's Filter Expression as it appears in the SysAid Expression Builder
status_class int Only SRs whose status falls into the chosen status class are included in the measurement list: All status classes (99), Open (0), Closed (1), Deleted/Ignore (2)
version int Version of the measurement list. Incremented by one on each save of the measurement list. Used by the measurement list history
change_time datetime History table only. Time the revision was saved
changed_by nvarchar(64) History table only. Username of the user who saved the revision. Corresponds to the "user_name" field in the "sysaid_user" table

 

measurements_def, measurements_def_history

 

Defines all measurements, including grades for measurements.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
id int ID# of the measurement
title nvarchar(255) Name of the measurement
agreement_id int ID# of the agreement to which the measurement is attached. Corresponds to the "id" field in the "agreement" table
parent_id int ID# of the parent measurement. Corresponds to the "id" field in this table
weight int Weight of the measurement when creating average grades for parent measurements
formula1 nvarchar(64) Calculation used to calculate the measurement: Average (avg), Minimum (min), Maximum (max), Count (count), Sum (sum), Ration (ratio)
list1_id int First measurement list used in the calculation. Default is "1" for non-calculated measurements. Corresponds to the "id" field in the "measurements_lists" table
formula2 nvarchar(64) Not in use
list2_id int Second measurement list used in the calculation. Default is "1" for non-calculated measurements and for measurements that use only one list. Corresponds to the "id" field in the "measurements_lists" table
time_interval int How far back in time to take data for the measurement: Daily (1), Monthly (2), Yearly (3), Total (4)
units nvarchar(64) Units of the measurement result, e.g. percent
critical_grade int SLA grade to assign if the measurement results are in the critical range
warning_grade int SLA grade to assign if the measurement results are in the warning range
optimum_grade int SLA grade to assign if the measurement results are in the optimum range
goal_critical int Measurement result at which the internal goal receives the critical grade
goal_warning int Measurement result at which the internal goal receives the warning grade
goal_optimum int Measurement result at which the internal goal receives the optimum grade
sla_critical int Measurement result at which the SLA performance receives the critical grade
sla_warning int Measurement result at which the SLA performance receives the warning grade
sla_optomum int Measurement result at which the SLA performance receives the optimum grade
calculated nchar(1) Whether the measurement is calculated based upon a measurement list (Y) or whether it's the average of other measurements (N)
enabled nchar(1) Whether the measurement is enabled (Y) or not (N)
version int Version of the measurement. Incremented by one on each save of the measurement. Used by the measurement history
change_time datetime History table only. Time the revision was saved
changed_by nvarchar(64) History table only. Username of the user who saved the revision. Corresponds to the "user_name" field in the "sysaid_user" table

 

current_measurement_lists

 

Stores the recorded values for measurement lists.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
agreement int SLA to which the SR in the list belongs. Corresponds to the "id" field in the "agreement" table. If a measurement is applied to all agreements, this field shows (-1)
measurement int Measurement for which the list value is included. Corresponds to the "id" field in the "measurements_def" table
list int Measurement list which the value is part of. Corresponds to the "id" field in the "measurements_lists" table
service_req_id int ID of the SR from which the measurement value is taken. Corresponds to the "id" field in the "service_req" table
list_value float Measured value from the SR based upon the field selected in the "field_name" field in the "measurements_lists" table
run_date datetime Time that the data was last collected

 

last_run_measurement_lists

 

Stores the last measurement of the previous period (daily, monthly, yearly) for each measurement list. For example, with a monthly period, "current_measurement_lists" is updated daily, while "last_run_measurement_lists" is updated on the last day of each month. Same structures as the "current_measurement_lists" table.

 

current_sla_results

 

Current results for each SLA measurement.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
measurement_id int ID# of the measurement. Corresponds to the "id" field in the "measurements_def" table
current_result float Numerical result for a calculated measurement
sla_grade float Grade given for a measurement based upon the SLA goal
internal_grade float Grade given for a measurement based upon the internal goal
run_date datetime Day on which the measurement was calculated (time is not calculated here and is displayed as 12:00:00)
final_result nchar(1) Whether the current results are the final results of the reporting period (Y) or not (N)

 

 

 

Chat

 

chat_queue

 

Stores all settings for chat queues.

 

Field Type Description
id int ID# of the chat queue
name nvarchar(64) Name of the chat queue
group_name nvarchar(64) Group assigned to answer chats from this queue. Corresponds to the "group_name" field in the "user_groups" table, or can be "None"
welcome_message ntext Message displayed to end user who begins a chat
welcome_message_from_agent ntext Message displayed to end user when an admin initiates a chat
idle_message ntext Message displayed to admin and end user when a chat becomes idle
operator_accept_message ntext Message displayed when an admin accepts a chat
operator_release_message ntext Message displayed when an admin releases a chat
offline_image_url nvarchar(64) URL to offline chat image. If blank, default icon is used
online_image_url nvarchar(64) URL to online chat image. If blank, default icon is used
add_hour_in_chat_session nchar(1) Whether or not to add a timestamp to each chat line (1) or not (0)
embed_in_site_script ntext HTML text to embed in company website to enable chatting without accessing the End-User Portal
time_before_idle int Time in minutes ellapsed with no messages sent before a chat becomes idle
time_before_close int Time in minutes ellapsed with no messages sent before a chat automatically closes
allow_offline_chat nchar(1) Whether to allow leaving a message when chat is offline (1) or not (0)
submit_offline_chat_message ntext Message displayed to end users after submitting an SR when chat is unavailable
display_details_screen nchar(1) Whether to display the end user details screen before the chat begins (1) or not (0)
email_address nvarchar(256) Reserved
operator_close_message ntext Message displayed to end user when an admin closes a chat

 

chat_queue_messages

 

Stores automatic texts for chat.

 

Field Type Description
id int ID# of the chat automatic text
title nvarchar(64) Title of the automatic text
message ntext Text to be inserted into the chat
queue int Queue to which the automatic text belongs. Corresponds to the "id" field in the "chat_queue" table

 

chat_active_sessions, chat_closed_sessions

 

Records all chat sessions.

 

Field Type Description
session_id int ID# of the chat session
chat_status int Status of the chat indicated to admin by color of bell: Orange (1), Green (2), Red (3), Closed chat (4)
request_user nvarchar(64) End user participating in the chat. Corresponds to the "user_name" field in the "sysaid_user" table
service_request int Number of any associated SR. Corresponds to the "id" field in the "service_req" table
queue nvarchar(64) Queue the chat is assigned to. Corresponds to the "id" field in the "chat_queue" table
assigned_user nvarchar(64) Admin who was last assigned to the chat. Corresponds to the "user_name" field in the "sysaid_user" table
session_password nvarchar(64) Password used for authentication during the session
full_name nvarchar(255) Full name of the end user participating in the chat
email_address nvarchar(64) Email address of the end user participating in the chat
ip_address nvarchar(64) IP address of the end user participating in the chat
start_time datetime Time the chat was initiated
close_time datetime Time the chat was closed
update_time datetime Time the chat was last updated by an admin or end user sending text
accept_time datetime Time the chat was accepted by an admin. If a chat is released and then reaccepted, the last accept time is used
account_id nvarchar(64) SysAid account ID
line_count int Number of lines of text in a chat as measured by the number of times that the admin or end user pressed Send/Enter
session_text ntext Transcript of the chat

 

 

 

Knowledge Base

 

faq, faq_history

 

Contains KB and FAQ articles. All articles in this table appear in the KB, whereas only articles with a value of (1) in the "publish" field appear in the FAQ.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
id int ID# of the KB article
title nvarchar(255) Title of the KB article
question ntext KB article question
answer ntext Answer to the KB article question
category nvarchar(64) Category of the KB article. Corresponds to the "problem_type" field in the "problem_type" table
sub_category nvarchar(64) Sub category of the KB article. Corresponds to the "problem_sub_type" field in the "problem_type" table
third_level_category nvarchar(64) Third level category of the KB article. Corresponds to the "third_level_category" field in the "problem_type" table
update_time datetime Time a KB article was last updated
user_topic_views int Times the KB article has been viewed (includes admins and end users)
created_on datetime Time the KB article was created
created_by nvarchar(64) Admin who created the KB article. Corresponds to the "user_name" field in the "sysaid_user" table
update_by nvarchar(64) Admin who last updated the KB article. Corresponds to the "user_name" field in the "sysaid_user" table
enable_expire int Whether the KB article expires (1) or not (0)
expire_date datetime Date the KB article expires
publish int Whether the article is published to the End-User Portal (1) or not (0)
voteYes int Number of thumbs-up votes for an article
voteNo int Number of thumbs-down votes for an article
question_no_html nvarchar(max)  
answer_no_html nvarchar(max)  

 

faq_files

 

Holds KB and FAQ attachments.

 

faq_tags

 

Stores tags for KB and FAQ articles.

 

Field Type Description
id int ID# of the KB article. Corresponds to the "id" field in the "faq" table
tag nvarchar(255) Contents of the tag

 

 

 

Miscellaneous

 

account

 

Stores information about your SysAid account. Also includes the account conf, which contains most customizable settings for the different SysAid modules.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
customer_name nvarchar(64) For internal use
expiration_time datetime License expiration date
serial_key nvarchar(64) SysAid serial key
account_conf ntext Contains various settings and preferences for SysAid. Do not edit manually!
version int Version of the account table. Incremented by one on each save of the account table

 

messages

 

Records SysAid instant messages.

 

Field Type Description
message_id int Auto increment
user_name nvarchar(64) User to whom the instant message was sent. Corresponds to the "user_name" field in the "sysaid_user" table
sent_time datetime Time the instant message was sent
sender nvarchar(64) User who sent the instant message. Corresponds to the "user_name" field in the "sysaid_user" table
recv_flag int Whether the admin has received the instant message already (1) or has still not received it (0)
msg ntext Contents of the instant message
msgid nvarchar(64) Internal SysAid ID for the message
service_request_id int For instant messages generated by new SRs, the SR ID#. Corresponds to the "id" field in the "service_req" table

 

cust_values

 

Stores all custom lists from Customize > Lists.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
list_name nvarchar(64) Name of the custom list
value_key int Number of the entry in the list
value_caption nvarchar(255) Name of an entry in a specific list
value_class int Status class for entries in the list with "list_name" "status": Open (0), Closed (1), Deleted/Ignore (2)
module_relevance int Used by "list_name" "status" to determine which SR types can be assigned a given status: Incidents (2), Changes (4), Problems (8), Requests (16). Values for all valid SR types are added together, such that a status that could be assigned to all SR types would have value (30). The value (1023) is used for all lists where "list_name" is not "status"
valid_for_user_group nvarchar(255) Groups that can select a particular "value_key" from a list. Corresponds to the "group_name" field in the "user_groups" table

 

sort_cust_values

 

Specifies how each list in the "cust_values" table should be sorted.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
list_name nvarchar(64) Name of the list. Corresponds to the "list_name" field in the "cust_values" table
sort_by nvarchar(64) Whether the list is sorted by Key (key) or by Caption (caption)

 

news

 

Stores new items. The "administrator" field determines whether the news is available to admins, end users, or all.

 

Field Type Description
news_id int ID# of the news item
account_id nvarchar(32) SysAid account ID
present nchar(1) Whether the news item is visible (y) or not (n)
title nvarchar(255) Title of the news item
description ntext Content of the news item
administrator nchar(1) Display the news to administrators only (y), end users only (n), or everybody (a)
urgency nchar(1) Whether the news item is urgent (y) or not (n)
insert_time datetime News item insert time
company int ID# of the company to show the news to. Corresponds to the "company_id" in the "company" table. (0) displays news to all companies
company_backup int For internal use
assigned_group nvarchar(64) Name of the group to show the news to. Corresponds to the "group_name" field in the "user_groups" table. (0) displays news to all groups

 

survey_questions

 

Contains your survey questions.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
id int ID# of the survey question
question_text ntext Text of the survey question
enabled nchar(1) Whether the question is enabled (1) or not (0)
display_comment nchar(1) Whether to display a free text field for user comments (1) or not (0)
position int Position in the survey of an individual question

 

survey_answers

 

Contains the predefined answers that end users may select for the survey questions.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
question_id int ID# of the question. Corresponds to the "id" field in the "survey_questions" table
answer_id int ID# of the answer
answer_text ntext Text of the answer

 

satisfaction_survey

 

Stores end user responses to the survey questions.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
sr_id int ID# of the SR for which the survey was submitted. Corresponds to the "id" field in the "service_req" table
answer int Answer to the survey question as selected by the end user. Corresponds to the "answer_id" field in the "survey_answers" table for a given "question_id"
comments nvarchar(255) Text entered into the comments field by the end user
update_time datetime Time the survey was answered
responsibility nvarchar(64) Admin assigned to the SR. Corresponds to the "user_name" field in the "sysaid_user" table
question_id int ID# of the question answered. Corresponds to the "id" field in the "survey_questions" table

 

audit_log

 

Contains the SysAid audit log. Log entries from different modules in SysAid are differentiated by the "audit_module" field.

 

Field Type Description
id int ID# of the log entry
account_id nvarchar(32) SysAid account ID
audit_date datetime Date and time of the log entry
audit_module int

Module that created the log entry. The numbers in this field correspond to the following modules:
HelpDesk (1), Knowledge Base (2), Login (3), Project (4), Asset Management (5), Monitoring (6), Preferences (7), Analyzer (8), CMDB (9), Manual (10), Password Services (11), Survey (12), SLA (13), Chat Queue (14), User Management (15), My Desktop (16), Integration (17), Reports (18), BI Analytics (19), SysAid Health Check (20)

audit_sub_module int Sub module that created the log entry. For a list of all sub module names, see the HTML source for AuditLog.jsp
audit_type int Type of log entry: Purge (1), Failure (2), Success (3), Update (4), Assignment (5), Remote Access (6), License Exceed (7)
audit_sub_type int Sub type of log entry: Task (1), Account (2)
audit_severity int Severity of the log entry: Info (1), Alert (2)
user_name nvarchar(64) User, service, or process whose action created the audit log entry
audit_info nvarchar(4000) Text describing the log entry
max_line_id int For audit log entries that have sub entries in the "audit_log_lines" table, the number of lines that appear in that table

 

audit_log_lines

 

Contains details for multiple line audit log entries from the "audit_log" table.

 

Field Type Description
log_id int ID# of the log. Corresponds to the "id" field in the "audit_log" table
line_id int ID# of the line in this log
audit_date datetime Timestamp of the entry into the log
account_id nvarchar(32) SysAid account ID
audit_info nvarchar(255) Details of the log entry

 

sysaid_item_links

 

Stores the contents of the linked items tables for all entities.

 

Field Type Description
account_id varchar(32) SysAid account ID
item_id nvarchar(64) The ID of the first of the two linked items
item_type int The type of the first of the two linked items: Service Item (1), Knowledge Base (2), Project (3), Task (4), Asset (5), CI (6), Patch (8), User (9), Company (10), SLA (11), SW Product (12), Catalog (13), Supplier (14), User Group (15)
linked_item_id nvarchar(64) The ID of the second of the two linked items
linked_item_type int The type of the second of the two linked items: Service Item (1), Knowledge Base (2), Project (3), Task (4), Asset (5), CI (6), Patch (8), User (9), Company (10), SLA (11), SW Product (12), Catalog (13), Supplier (14), User Group (15)
is_main_linked_item nvarchar(1) Whether the linked item is a Main Asset, Main CI, Main Project, or Main Task (1) or not (0)
cause_item nvarchar(64) Of the two linked items, the ID of the item that caused the second item. Can also be (None)

 

reminders

 

Contains reminders you've created, and indicates which notification to send for each reminder.

 

Field Type Description
id int ID# of the reminder
account_id nvarchar(32) SysAid account ID
reminder_name nvarchar(64) Field upon which the reminder is based. For a list of possible fields, see the HTML source for RemindersList.jsp
notification nvarchar(64) Name of the notification to send for the reminder. Notifications are stored in the account conf, located in the "account_conf" field in the "account" table
alert_before int Number of days in advance of date to send the reminder
alert_time nvarchar(64) Hour of the day to send the reminder. Must be in format HH:MM
filter_expression ntext Contains the query created by the reminder's Filter Expression as it appears in the SysAid Expression Builder
filter_sql ntext Contains the query created by the reminder's Filter Expression

 

user_favorites

 

Stores user favorites (URL bookmarks).

 

Field Type Description
id int ID# of the favorite
account_id nvarchar(32) SysAid account ID
caption nvarchar(64) Name of the favorite
url_string nvarchar(255) URL of the favorite
user_name nvarchar(64) Username of the user who the favorite belongs to. Corresponds to the "user_name" field in the "sysaid_user" table

 

custom_columns

 

Records all custom fields added to the SysAid DB.

 

Field Type Description
id int ID# of the custom field
account_id nvarchar(32) SysAid account ID
entity_type nvarchar(64) Type of entity to which the custom field has been added: SRs (sr), Tasks (task), Assets (asset), Projects (project), Cis (ci), Users (user), Companies (company), Action Items (srSubTab)
field_caption nvarchar(64) Caption of the field to display in forms and lists
field_type nvarchar(64) Type of the field: Text (string), Integer (int), Float (float), Text area (long), Date (date), List (list)
attribute_name nvarchar(64) Internal name of the custom field created as follows: (CustomColumn) + "id" + "entity_type"
addon_db_name nvarchar(64) Name of the new field in the SysAid DB
compatibility_mode Nchar(1) For internal use
write_in_list ntext Display in List text
write_in_form ntext Display in Form text
write_in_form_mobile ntext Display in Mobile Form text
read_data_from_form ntext Server-side validation text
validation_in_form ntext Client-side validation text
hidden_control_in_form ntext For internal use
upload_from_file Nchar(1) For internal use

 

custom_triggers

 

Records all custom triggers you create for the different SysAid entities.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
entity_type nvarchar(64) SysAid entity to which the trigger applies
trigger_onload ntext Text of the On Load trigger
trigger_before_save ntext Text of the Before Save trigger
trigger_after_save ntext Text of the After Save trigger
trigger_onload_lastlog ntext Log file for the last time the On Load trigger was run
trigger_before_save_lastlog ntext Log file for the last time the Before Save trigger was run
trigger_after_save_lastlog ntext Log file for the last time the After Save trigger was run
compatibility_mode Nchar(1) For internal use

 

custom_notification

Records all custom notification fields added to the SysAid DB.

 

Field Type Description
id int ID# of the custom notification
name nvarchar(256) Custom notification name
subject_notification_value mediumtext  
subject_notification_default mediumtext  
body_notification_value mediumtext  
body_notification_default mediumtext  
addon_db_name nvarchar(256)  

 

sysaid_events

 

Records SysAid Events you create for the SysAid Calendar.

 

Field Type Description
account_id nvarchar(32) SysAid account ID
id int ID# of the event
title nvarchar(255) Title of the event
description ntext Description of the event
start_date datetime Start time of the event
end_date datetime End time of the event
available_groups nvarchar(64) Group to which the event applies. Corresponds to the "group_name" field in the "user_groups" table. All users including end users is represented by (-3). NULL if not a group event
create_user nvarchar(64) Username of the user who created the event. Corresponds to the "user_name" field in the "sysaid_user" table. NULL if the event is a group event

 

sysaid_user_push_enable

 

Records all mobile devices that have been registered in SysAid to receive push notifications.

 

Field Type Description
user_name nvarchar(64) Username of the user registered to push notifications. Corresponds to the "user_name" field in the "sysaid_user" table
account_id nvarchar(32) SysAid account ID
device_type nvarchar(64) Type of mobile device registered to receive notifications
device_id nvarchar(255) ID of the mobile device
enable_date datetime Date and time the device was registered
is_production nchar(1) For internal use
is_chat_online int For future use

 

login_log

 

Records all SysAid login attempts and logouts.

 

Field Type Description
log_time datetime Time of the login attempt
account_id nvarchar(32) SysAid account ID
user_name nvarchar(64) Username of the user who attempted to log in. Corresponds to the "user_name" field in the "sysaid_user" table
status nvarchar(64) Type of login attempt: (Logged In), (Login Failed), or (Logged Out)
ip_address nvarchar(64) IP address from which the login took place

 

form_history

 

Records the last 10 forms visited by each user. If a user has not visited at least 10 forms, the field "form_caption" shows "sysaid_caption" and the field "form_url" shows "sysaid_url" for any unused lines. This table includes entries for both admins and end users.

 

Field Type Description
user_name nvarchar(64) Username of the user who visited the form. Corresponds to the "user_name" field in the "sysaid_user" table
account_id nvarchar(32) SysAid account ID
form_caption nvarchar(255) Short description of the form, including ID# and Title, if applicable
form_url nvarchar(255) URL of the visited form
form_visit_time datetime Time the form was last visited

 

list_view

 

Contains all the different list views for all of the different SysAid lists. Do not attempt to manually edit this table!

 

Field Type Description
account_id nvarchar(32) SysAid account ID
list_name nvarchar(64) Name of the list to which the view applies
list_view_name nvarchar(64) Name of the list view as it appears in the drop-down list of list views
user_name nvarchar(64) Deprecated
list_conf ntext An XML file that contains the layout of the list
enable_delete nchar(1) Whether the view can be deleted (Y) or not (N)
version int Version of the list view. Incremented by one on each save of the list view

 

addon_attributes

 

Field Type Description
addon_name varchar(255)  
attribute_name varchar(255)  
attribute_value longtext  

 

Addon

Field Type Description
title varchar(255)  
addon_type varchar(255)  
link varchar(255)  
link_text varchar(255)  

 

addon_history

 

Field Type Description
using_sysaid_gateway numeric(1)  

 

sc_banner

 

Field Type Description
id int(11) auto_increment
name varchar(64)  
banner_text varchar(64)  
image_file_name varchar(255)  
image_overlay int(11)  
text_color varchar(64)  
banner_link varchar(255)  
last_modified_time datetime  
last_modified_by varchar(255)  
height int(11) Defaults to 1

 

sc_theme

 

Field Type Description
id int(11) auto_increment
name varchar(255)  
last_modified_time datetime  
last_modified_by varchar(255)  
brand_color varchar(64)  
brand_contrast varchar(64)  
page_background varchar(64)  
headline_color varchar(64)  
text_color varchar(64)  
header_background varchar(64)  
menu_text varchar(64)  
toolbar_background varchar(64)  
button_background varchar(64)  
button_content varchar(64)  
border_color varchar(64)  
hover_color varchar(64)  
visible_to_end_user int(1) Defaults to 0 (false)

 

sc_theme_to_company

 

Field Type Description
sc_theme_id int(11)  
company_id int(11)  

sc_toolbox_item

 

Field Type Description
unique_name varchar(64)  

 

sc_widgets

 

Field Type Description
visible_to_guest char(1) Whether a widget is visible to guest user. Default value: ‘Y’
widget_order int(11)  

 

search_engine_queue

 

Field Type Description
id int(11) Auto increment
object_id int(11)  
object_type varchar(32)  
handling_attempts int(11) Default: 0

 

 

addon_license, addon_license_history

 

Field Type Description
enable_video_recording char(1) Default: ‘Y’

 

 

 

Omitted Tables

 

Several tables have intentionally been left out of this guide. For the sake of completeness, these tables are listed here. A short description is provided for each table.

 

Table Description
schedule_task Scheduled tasks in SysAid
event For internal use
commands For internal use
project_users Not in use
url_embed_data For future use
predefined_services_check Predefined monitoring services checks
predefined_network_check Predefined monitoring port # checks
share_and_compare IT Benchmark statistics
statistics_data IT Benchmark data
generic_messages Green popup messages on the bottom of the admin console
custom_services For internal use
sysaid_user_push_notifications Push notifications sent by SysAid to mobile devices
mdm_actions MDM actions waiting to be executed
quick_list No longer in use as of version 9.0.70

 

 

 

 

 

Contact Us

 

SysAid welcomes your questions and suggestions. We can be reached via phone and email:

Toll Free phone center (U.S): 800-686-7047

Tel (U.S): +1 617-231-0124

Fax (U.S): +1 617 507 2559

Tel (Israel): +972 3 533 3675

Fax (Israel): +972 3 761 7205

Email: helpdesk@sysaid.com

SysAid community: http://www.sysaid.com/Sysforums/forums/home.page

You can also view our full support page at http://www.sysaid.com/contact_support.htm.

 

This message was edited 3 times. Last update was at Oct. 29, 2012 10:16 AM