SysAid Database Guide
  • 14 Dec 2022
  • 73 Minutes to read
  • Dark
    Light
  • PDF

SysAid Database Guide

  • Dark
    Light
  • PDF

Article Summary

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.

Please note that Oracle is not supported in versions 21.4 or higher.

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
BigintInteger (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
TinyintInteger data from 0 through 255. Storage size is 1 byte.SQL
NumberStores 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
FloatA 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
NVarcharVariable-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
CharFixed-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
NCharFixed-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
DateTimeStore values that are both dates and times or only dates. A DateTime value is stored in eight bytes — two four-byte integers.SQL
DateThe 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
ImageVariable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.SQL
Long RawUsed 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 keysAll fields that are primary keys for a table are marked by the key icon .
Linked fieldsMany 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 namesAny 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 valuesAny 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 SysAidLists 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 listsThere 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
idintID 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_idnvarchar(32)SysAid account ID
image-1654274231682file_idnvarchar(64)Database name of the file, including full path

file_namenvarchar(255)Filename of the file

file_contentimageThe file itself

chat_session_idintFor SRs only: attached chat ID#, for attachments that are attached to both an SR and a chat

file_datedatetimeTimestamp 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
image-1654274231682idintID number of the entity with the attachment, e.g. SR #23, Task# 5, etc.
image-1654274231682account_idnvarchar(32)SysAid account ID
image-1654274231682file_idnvarchar(64)Database name of the link, including full path

linknvarchar(255)The hyperlink itself

file_namenvarchar(255)Description of the link

file_datedatetimeTimestamp 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
image-1654274231682account_idnvarchar(32)SysAid account ID
image-1654274231682problem_typenvarchar(64)Category of the SR
image-1654274231682problem_sub_typenvarchar(64)Subcategory of the SR

routenvarchar(64)For internal use

desc_templatentextContains the text of the description template for this category/sub-category/third-level category combination
image-1654274231682third_level_categorynvarchar(64)Third level category of the SR

module_relevanceintDetermines 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_templateintSpecifies 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_templateintSpecifies 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
image-1654274438542change_templateintSpecifies 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
image-1654274438542problem_templateintSpecifies 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
image-1654274438542first_level_keyint(11) 
image-1654274438542second_level_keyint(11) 
image-1654274438542third_level_keyint(11) 
image-1654274438542admin_groupsnvarchar(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
image-1654274231682idintThe ID of the service record

account_idnvarchar(32)SysAid account ID
image-1654274438542computer_idnvarchar(64)SR's main asset. Corresponds to the "computer_id" field in the "computer" table
image-1654274438542ci_idintSR's main CI. Corresponds to the "id" field in the "ci_attributes" table
image-1654274438542problem_typenvarchar(64)Category of the SR. Corresponds to the "problem_type" field in the "problem_type" table
image-1654274438542problem_sub_typenvarchar(64)Sub-category of the SR. Corresponds to the "problem_sub_type" field in the "problem_type" table

titlenvarchar(255)Title of the SR

descriptionntextDescription of the SR

workaroundntextWorkaround for a problem

known_errornchar(1)Known error list: No (N), Production (P), and Development (D)

statusintStatus of the SR. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "status"

contactntextObsolete
image-1654274438542responsibilitynvarchar(64)Assigned to administrator. Corresponds to the "user_name" field in the "sysaid_user" table
image-1654274438542urgencyintUrgency of the SR. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "urgency"
image-1654274438542priorityintPriority of the SR. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "priority"

notesntextNotes field

resolutionntextResolution field

solutionntextSolution field

insert_timedatetimeSR request time

update_timedatetimeSR modify time

close_timedatetimeSR close time
image-1654274438542update_usernvarchar(64)User who last modified the SR. Corresponds to the "user_name" field in the "sysaid_user" table
image-1654274231682versionintVersion of the SR. Updated each time the SR is saved. Used by the SR history
image-1654274438542knowledge_baseInt 
image-1654274438542submit_usernvarchar(64)User who submitted the SR. Corresponds to the "user_name" field in the "sysaid_user" table

submit_user_typetinyintDeprecated
image-1654274438542request_usernvarchar(64)Request user of the SR. Corresponds to the "user_name" field in the "sysaid_user" table

request_user_typetinyintA 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
image-1654274438542responsible_managernvarchar(64)SR's responsible admin. Corresponds to the "user_name" field in the "sysaid_user" table

email_accountnvarchar(64)The email account from which the SR originated. Blank if the SR was not submitted by email

due_datedatetimeSR's due date
image-1654274449724locationintSR's location. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "location"
image-1654274449724parent_linkintID# of the SR's parent SR. Corresponds to the "id" field in the "service_req" table

escalationintThe escalation level of the SR. (0) is not escalated, otherwise the escalation level appears here
image-1654274449724(1)third_level_categorynvarchar(64)Third level category of the SR. Corresponds to the "third_level_category" field in the "problem_type" table
image-1654274449724(1)assigned_groupnvarchar(64)SR's assigned admin group. Corresponds to the "group_name" field in the "user_groups" table

timers_update_timedatetimeThe last time the timers associated with the SR were updated

timer1 - timer10bigintAccumulated timer value for the corresponding timer. For example, the default for Timer 1 is Time to Repair
image-1654274449724(1)cust_list1intSR custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "srList1"
image-1654274449724(1)cust_list2intSR custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "srList2"

cust_text1nvarchar(255)SR custom text 1

cust_text2nvarchar(255)SR custom text 2

cust_notesntextSR custom notes

cust_int1intSR custom int 1

cust_int2intSR custom int 2
image-1654274449724(1)ccnvarchar(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
image-1654274449724(1)project_idintID of the project associated with the SR. Corresponds to the "id" field in the "project" table
task_idintID of the task associated with the SR. Corresponds to the "id" field in the "task" table

sr_typeintType 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_namenvarchar(255)Reserved

cust_date1datetimeSR custom date 1

cust_date2datetimeSR custom date 2

sourceintSource 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_typeintSub type of the SR. Corresponds to the sub types in the table "sr_sub_type"

followup_planned_datedatetimeSR follow up planned date

followup_actual_datedatetimeSR follow up actual date
followup_usernvarchar(64)User marked for follow up. Corresponds to the "user_name" field in the "sysaid_user" table

followup_textntextFollow up text

success_ratingintSuccess rating field

reopen_counterintNumber of times the SR has been changed from a closed status class to an open one

assign_counterintNumber of times the assigned to admin has changed

max_support_levelintHighest support level the SR has reached

current_support_levelintCurrent support level for the SR
agreementintAgreement attached to the SR. Corresponds to the "id" field in the "agreement" table

survey_statusintStatus of the survey: has not been sent (0), has been sent (1), has been answered (2)
impactintImpact of the SR. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "impact"
change_categoryintChange classification for changes. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "changeCategory"

archiveintIndicates whether an SR is archived (1) or not (0)
closure_informationintClosure information for a closed SR. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "closureInformation"

visible_to_eunchar(1)Whether the incident/request template is visible to end users from the End-User Portal (Y) or not (N)
sr_classnvarchar(255)Class of the incident/request template
sr_weightIntWeight of the SR.

service_req_log

Contains a summary of all changes to a service record.


Field
Type
Description
log_idintLog ID #

account_idnvarchar(32)SysAid account ID
service_req_idintSR the log reports on. Corresponds to the "id" field in the "service_req" table

log_timedatetimeTime that the SR changed

log_typenvarchar(64)Type of change to the SR, such as incident changed or service record changed

log_descriptionntextA description of the change, such as New Incident or Incident has been assigned to…

ext_referenceintEntry number of the log for a given service record
user_namenvarchar(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
idintSR#
account_idnvarchar(32)SysAid account ID

screen_captureimageThe screenshot attached to the SR during submission from the End-User Portal

service_req_merge


Field
Type
Description
idint(11)Not null, default 0
account_idint(11)default 0

merged_toint(11)default 0

merged_service_recordsvarchar(4000) 

screen_capturevarchar(4000) 

service_req_msg

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


Field
Type
Description
idintSR# the message is attached to. Corresponds to the "id" field in the "service_req" table
account_idnvarchar(32)SysAid account ID
msg_timedatetimeTime the message was sent/received

from_usernvarchar(64)Message sent from

to_usernvarchar(255)Message sent to

cc_usernvarchar(255)Users CCd on the message

methodnvarchar(64)Type of message: Email (email), Automatic notification (auto), Action item notification (ITIL), SMS (sms), Instant Message (im)

subjectntextMessage subject

msgidnvarchar(64)ID of the message. The timestamp + GMT offset (i.e. time zone) is used as the ID

msg_bodyntextBody of the message

email_html_sourcentextOriginal 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
idintActivity#
service_req_idintID# of the SR the activity is attached to. Corresponds to the "id" field in the "service_req" table

account_idnvarchar(32)SysAid account ID
user_namenvarchar(64)Username of the user who left the activity. Corresponds to the "user_name" field in the "sysaid_user" table

from_timedatetimeActivity start time

to_timedatetimeActivity end time

descriptionntextThe description of the activity performed
cust_list1intActivity custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "srActList1"
cust_list2intActivity custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "srActList2"

cust_text1nvarchar(255)Activity custom text 1

cust_text2nvarchar(255)Activity custom text 2

cust_notesntextActivity custom notes

cust_int1intActivity custom int 1

cust_int2intActivity custom int 2

cust_int3intActivity custom int 3

cust_int4intActivity custom int 4

cust_date1datetimeActivity custom date 1

cust_date2datetimeActivity custom date 2
ci_idintID# 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_idnvarchar(32)SysAid account ID
sr_typeintType of the SR:  Incident (1), Request (10), Problem (6), Change (4), Phone call (8)
sr_sub_typeintID# of the SR sub type

sub_type_namenvarchar(255)Name of the sub type

sub_type_form_viewntextLists the fields that are displayed on the form for that sub type

end_user_viewntextLists 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_idnvarchar(32)SysAid account ID
sr_idintSR ID#. Corresponds to the "id" field in the "service_req" table
tab_namenvarchar(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_idintID# of the action item
sub_tab_orderintNumber 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_tonvarchar(64)Assigned to user for the action item. Corresponds to the "user_name" field in the "sysaid_user" table
submit_usernvarchar(64)SR submit user. Corresponds to the "user_name" field in the "sysaid_user" table

insert_timedatetimeSR request time

due_datedatetimeAction item due date

titlenvarchar(255)Action item title

descriptionntextAction item description

notesntextAction item notes

proposed_delivery_datedatetimeProposed delivery date for the SR

proposed_versionnvarchar(64)Proposed version for implementation of the change/problem
complexityintComplexity of the SR. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "subTabComplexity"
urgencyintUrgency of the action item. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "urgency"
priorityintPriority of the action item. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "priority"

statusintWhether the action item is Enabled (0), Disabled (1), or Completed (2), OverDue (3)

auto_completenchar(1)Indicates if the action item is set to autocomplete (Y) or requires manual completion (N)
on_complete_change_statusintUpdate 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_idnvarchar(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_methodnvarchar(64)Indicates whether the notification is sent on action item activation (on_active) or completion (on_completion)

durationfloatDuration in hours of the SR

resources_required_in_daysfloatAnticipated resources (in days) required for the SR
ci_idintID# of the CI attached to the action item. Corresponds to the "id" field in the "ci_attributes" table

cab_meeting_referencenvarchar(64)Reference number for the CAB meeting regarding a change

percent_completedintPercentage of the action item completed

cust_int1 - cust_int10intCustom integer fields for action items

cust_text1 - cust_text10nvarchar(255)Custom text fields for action items

cust_date1 - cust_date10datetimeCustom date fields for action items
cust_list1 - cust_list10intCustom list fields for action items. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "subTabList1" - "subTabList10"

cust_notes1 - cust_notes10ntextCustom notes fields for action items
task_idintID# of the task attached to the action item. Corresponds to the "id" field in the "task" table
project_idintID# of the project attached to the action item. Corresponds to the "id" field in the "project" table
assigned_groupnvarchar(64)Group responsible for the action item. Corresponds to the "group_name" field in the "user_groups" table
locationintLocation associated with the action item. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "location"

completed_timedatetimeTime the action item was completed

enabled_timedatetimeTime the action item was enabled
companyintCompany associated with the action item. Corresponds to the "company_id" in the "company" table

company_backupintFor internal use

modify_timedatetimeAction item last modify time

policy_compliancenchar(1)Indicates if there was policy compliance for the change/problem/request (Y) or not (N)

budgetednchar(1)Indicates if change/problem/request was budgeted (Y) or not (N)

approvednchar(1)Indicates if the change/problem/request was approved (Y) or rejected (N)

user_acceptancenchar(1)Indicates if there was user acceptance for the change/problem/request (Y) or not (N)

hardware_costsfloatHardware costs for the SR

software_costsfloatSoftware costs for the SR

installation_costsfloatInstallation costs for the SR

training_costsfloatTraining costs for the SR

maintenance_costsfloatMaintenance costs for the SR

total_costsfloatTotal costs for the SR

internal_manpower_in_hoursfloatManpower in hours needed for the SR

total_expected_downtime_hoursfloatTotal expected downtime during implementation of the SR
departmentintDepartment associated with the action item. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "departments"

expected_downtime_startdatetimeExpected end of downtime associated with the SR

expected_downtime_enddatetimeExpected start of downtime associated with the SR
impactintImpact of the action item. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "impact"

reopenednvarchar(255)Whether the action item has not yet been completed (NULL), has been completed (No), or is currently reopened (Yes)

cust_float1 - cust_float10floatCustom float fields for action items

versionintVersion of the action item. Incremented by one on each save of the action item. Used by the action item history

change_timedatetimeHistory table only. Time the revision was saved
changed_bynvarchar(64)History table only. Username of the user who saved the revision. Corresponds to the "user_name" field in the "sysaid_user" table

allow_pdfnvarchar(1)Allow the end user to create a PDF of the action item (Y) or not (N)
update_usernvarchar(64)User who last modified an action item. Corresponds to the "user_name" field in the "sysaid_user" table

reopened_notentextNote left by user who reopened an action item. Includes the user's name and a timestamp

and_conditionnvarchar(1)Indicates whether action item dependencies are AND (Y) or OR (N) based
additional_usernvarchar(64)Additional user responsible for an action item. Corresponds to the "user_name" field in the "sysaid_user" table
on_activate_change_statusintUpdate 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_idnvarchar(32)SysAid account ID
user_namenvarchar(64)Username of the out of office admin. Corresponds to the "user_name" field in the "sysaid_user" table
take_over_usernvarchar(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
idintID# of the status setting

account_idnvarchar(32)SysAid account ID
change_statusintStatus 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_statusintStatus of the incident. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "status"
exclude_statusesnvarchar(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

disableintWhether the priority rule is enabled (1) or disabled (0)
agreement_keyintThe agreement to which the priority rule applies. Corresponds to the "id" field in the "agreement" table
company_keyintThe company to which the priority rule applies. Corresponds to the "company_id" field in the "company" table
impact_keyintThe impact for which the priority rule applies. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "subTabImpact"
urgency_keyintThe urgency for which the priority rule applies. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "urgency"
priority_keyintThe 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_updatedatetimeThe time the priority rule was last modified

automatic_texts

Stores automatic texts for the Send Message page.


Field
Type
Description
idintID# of the automatic text
account_idnvarchar(32)SysAid account ID

titlenvarchar(255)Title of the automatic text

descriptionntextText to be inserted into messages
assigned_groupnvarchar(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_idnvarchar(32)SysAid account ID
idintID# of the action item template

namenvarchar(64)Name of the action item template

sub_tab_viewntextSpecification 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_idnvarchar(32)SysAid account ID
sr_idintID# of the SR. Corresponds to the "id" field in the "service_req" table

tab_namenvarchar(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_orderintNumber 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_tabnvarchar(64)Number of the workflow tab this action item depends upon for activation

depends_on_subintNumber of the action item this action item depends upon for activation

dependent_methodnvarchar(64)Whether the action item is activated upon activation (on_active) or upon completion (on_completion) of the target action item

filter_expressionntextContains the query created by the dependency's Filter Expression as it appears in the SysAid Expression Builder

filter_sqlntextContains 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_idnvarchar(32)SysAid account ID
sr_idintID# of the SR. Corresponds to the "id" field in the "service_req" table

tab_namenvarchar(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_orderintNumber 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_namenvarchar(64)The field to copy to. Corresponds to a field in the "sr_sub_tab" table

source_tab_namenvarchar(64)Number of the workflow tab from which to copy a field

source_sub_tab_orderintNumber of the action item from which to copy a field
source_field_namenvarchar(64)The field to be copied. Corresponds to a field in the "sr_sub_tab" table

help_desk_routing


Field
Type
Description

idint(11)Not null, auto_increment

execution_orderint(11) 

routing_agreementint(11)default 0

routing_companyint(11)default 0

routing_eu_groupvarchar(256) 

routing_categoryvarchar(256) 

routing_sub_categoryvarchar(256) 

routing_third_categoryvarchar(256) 

routing_adminvarchar(256) 

routing_admin_groupvarchar(256) 

routing_enabledint(1)default 1

modified_byvarchar(256) 

modified_datedatetime 

help_desk_due_date


Field
Type
Description

idint(11)Not null, auto_increment

execution_orderint(11) 

duedate_agreementint(11)default 0

duedate_companyint(11)default 0

duedate_categoryvarchar(256) 

duedate_sub_categoryvarchar(256) 

duedate_third_categoryvarchar(256) 

duedate_urgencyvarchar(256) 

duedate_priorityvarchar(256) 

duedate_hoursint(11)default 0

duedate_enabledint(1)default 1

modified_byvarchar(255) 

modified_datedatetime 

help_desk_timers


Field
TypeDescription

idint(11) 

timer_captionvarchar(255) 

timer_expressionvarchar(4000) 

timer_expression_srcvarchar(4000) 

timer_sqlvarchar(4000) 

timer_enabled  

modified_byvarchar(255) 

modified_datedatetime 

category_keys


Field
Type
Description

idint(11)Not null, auto_increment

namevarchar(255)UNIQUE

static_filter


Field
Type
Description

idvarchar(36) 

namevarchar(64) 

filterlongtext 

Assets

asset_types

Contains the list of asset types.


Field
Type
Description
account_idnvarchar(32)SysAid account ID
computer_typenvarchar(64)Name of asset type

captionnvarchar(64)UI caption for asset type

file_namenvarchar(255)Filename of the icon for the asset type

default_file_namenvarchar(255)For predefined asset types, filename of the default icon for the asset type
ci_sub_type_idintCI 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_idnvarchar(32)SysAid account ID
computer_idnvarchar(64)ID of the asset

computer_namenvarchar(64)Name of the asset
computer_typenvarchar(64)Type of the asset. Corresponds to the "computer_type" field in the "asset_types" table
parent_groupnvarchar(255)Group of the asset. Corresponds to the "group_name" field in the "computer_group" table

inventory_xmlntextStores the inventory.xml file sent by the SysAid Agent

inventory_timedatetimeThe last time the asset inventory was updated (by the Agent, by network discovery, etc.)

update_timedatetimeLast time changes were saved to the asset

ip_addressnvarchar(64)IP address of the asset

descriptionnvarchar(255)Description of the asset
usernamenvarchar(64)Owner of the asset. Corresponds to the "user_name" field in the "sysaid_user" table
locationnvarchar(255) 
location_idxintLocation of the asset. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "location"

buildingnvarchar(64)Building in which the asset is located

floornvarchar(64)Floor on which the asset is located

cubicnvarchar(64)Cubicle in which the asset is located
catalog_numbernvarchar(64)Catalog number of the asset. Corresponds to the "catalog_number" field in the "asset_catalog" table
supplierintSupplier of the asset. Corresponds to the "supplier_id" field in the "supplier" table
maintenance_supplierintOrganization that provides maintenance for the asset. Corresponds to the "supplier_id" field in the "supplier" table

company_serialnvarchar(64)Your company's serial number for the asset

external_serialnvarchar(64)The manufacturer's external serial number for the asset

monitornvarchar(64)The asset's monitor

monitor_serialnvarchar(64)The serial number for the asset's monitor

collection_typeintManner in which the asset was added to SysAid: SNMP (1), Agent (2), Manual (3), WMI (4), iOS (5), Android (6)

collection_paramsntextContains credentials used for SNMP and WMI scanning
cust_list1intAsset custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "assetList1"
cust_list2intAsset custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "assetList2"

cust_text1nvarchar(255)Asset custom text 1

cust_text2nvarchar(255)Asset custom text 2

cust_notesntextAsset custom notes

cust_int1intAsset custom integer 1

cust_int2intAsset custom integer 2
parent_assetnvarchar(64)ID of the parent asset of the current asset. Corresponds to the "computer_id" field in this table
departmentintDepartment of the asset. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "departments"
companyintCompany the asset belongs to. Corresponds to the "company_id" in the "company" table

company_backupintFor internal use

disablenchar(1)Whether the asset is disabled (Y) or not (N)

manual_assetnchar(1) 

purchase_costfloatPurchase cost of the asset
purchase_currencyintPurchase currency of the asset. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "currencyList"

agent_versionnvarchar(64)Version of the SysAid Agent installed on the asset

cust_date1datetimeAsset custom date 1

cust_date2datetimeAsset custom date 2

versionintVersion of the asset. Incremented by one on each save of the asset. Used by the asset history

change_timedatetimeHistory table only. Time the revision was saved
changed_bynvarchar(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_20nvarchar(255)SNMP custom text fields. Used to store the values of customized OIDs

packets_infloatNetwork traffic received by the asset's primary network card

packets_outfloatNetwork traffic sent by the asset's primary network card

mac_addressnvarchar(255)MAC address of the asset's primary network card

last_bootdatetimeLast time the asset was booted

track_assetintIndicates whether asset availability is recorded for the asset (1) or not (0)

first_accessdatetimeFirst time the SysAid Agent installed on the asset contacted the SysAid Server

device_statusintDevice MDM status: Enrolled (1), Not Enrolled (2), Revoked by Admin (3), Revoked by End User (4)
device_policyintDevice MDM policy. Corresponds to the "id" field in the "mdm_policy" table

device_ownershipintWhether a mobile device is Employee Owned (1) or Corporate Owned (2)

device_imeinvarchar(255)IMEI of the mobile device asset

device_iccnvarchar(255)ICC of the mobile device asset

device_home_carriernvarchar(255)Home carrier of the mobile device asset

device_current_carriernvarchar(255)Current carrier of the mobile device asset

device_phone_numbernvarchar(64)Mobile device asset phone number

device_pushnvarchar(255)Used for MDM push notifications

ios_push_magicnvarchar(255)Used for MDM push notifications

ios_unlock_tokenntextApple unlock key for iOS devices

designated_rdsnvarchar(64)For future use

gfi_versionnvarchar(64)Version of GFI used by Patch Management

gfi_buildnvarchar(64)Build of GFI used by Patch Management

policy_idintID for Patch Policy

patch_enablednumericIndicates if the asset is enabled for Patch Management

last_scan_timedatetimeLast time the asset was scanned for new patches

last_patch_timedatetimeLast time a patch job ran on the asset

settings_idintThe 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_idnvarchar(32)SysAid account ID
computer_idnvarchar(64)ID of the asset

cpu_vendornvarchar(255)Vendor of the asset's CPU

cpu_modelnvarchar(255)Model of the asset's CPU

cpu_speedintSpeed of the asset's CPU

bios_typenvarchar(255)BIOS information for the asset

display_adapternvarchar(255)Asset's display adapter

display_memoryintOn-board memory for the asset's display adapter

display_resolutionnvarchar(255)Screen resolution for the asset's connected monitor

os_typenvarchar(255)Asset's OS type

os_versionnvarchar(255)Asset's OS version

os_service_packnvarchar(255)Service pack number for the asset's OS

memory_physicaldecimalPhysical memory of the asset

serialnvarchar(64)Asset's serial number

modelnvarchar(64)Model of the asset

manufacturernvarchar(64)Manufacturer of the asset

purchase_datedatetimeDate the asset was purchased

warranty_expirationdatetimeDate the asset's warranty expires

last_maintenancedatetimeLast maintenance performed on the asset

last_page_countintFor printers, the last page count taken

maintenance_page_countintFor printers, the page count taken at the time of the last maintenance

disks_sizeintTotal size of asset's storage devices

disks_countintNumber of storage devices in the asset

mem_banksintNumber of memory banks in the asset

occupied_mem_banksintNumber of occupied memory banks in the asset

free_mem_banksintNumber of free memory banks in the asset

cpu_countintNumber of CPUs in the asset

os_namenvarchar(255)Name of the asset's OS

os_platformnvarchar(64)Platform of the asset's OS

os_serialnvarchar(255)The asset's OS serial number
versionintRevision # for the "computer_attributes" table. Used for " computer_attributes_history"

cpu_familyintThe number corresponding to the hardware that makes up the asset's CPU.

cpu_steppingintThe number corresponding to the layers of hte chpi used by the asset's CPU.

cpu_model_numberintThe asset's CPU model number

cpu_serial_numbernvarchar(255)The asset's CPU's serial number

nt_product_typenvarchar(255) 

nt_product_type_2nvarchar(255)The asset's OS type

nt_os_editionnvarchar(255)The edition of the OS that the asset is using.

nt_service_pack_major_versionintService pack major version of the asset.

nt_service_pack_minor_versionintService pack minor version of the asset.

os_registered_usernvarchar(255)The asset's OS registered user

os_registered_orgnvarchar(255)The asset's OS registered organization

os_build_numbernvarchar(255)The asset's OS build number

os_kernelnvarchar(255)The version of the OS's kernel.

computer_group

Defines all asset groups.


Field
Type
Description
account_idnvarchar(32)SysAid account ID
group_namenvarchar(255)Name of the asset group
parent_group_namenvarchar(255)Name of the parent group of the asset. Corresponds to the "group_name" field in this table

group_descriptionnvarchar(255)A short description that includes the number of assets in the group

group_levelintHow 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_idnvarchar(32)SysAid account ID
computer_idnvarchar(64)ID of the asset to which the list belongs. Corresponds to the "computer_id" field in the "computer" table

list_typenvarchar(64)The type of list, e.g. "Software"

valuenvarchar(255)The list entry, e.g. "SysAid Agent version 8.6.1"

list_displaynvarchar(255)For services, a short description of the service

versionnvarchar(255)For software products, the version #

licensenvarchar(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_idnvarchar(32)SysAid account ID
computer_idnvarchar(64)ID of the asset the users are connected to
user_namenvarchar(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_namenvarchar(255)Full name of the user connected to the asset

email_addressnvarchar(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_idintID# of the log details. Corresponds to the "log_id" field in the "computer_changes" table

account_idnvarchar(32)SysAid account ID
computer_idnvarchar(64)ID of the computer that had the change. Corresponds to the "computer_id" field in the "computer" table

log_timedatetimeTime of the log entry

log_typenvarchar(64)Type of the log entry

log_sub_typenvarchar(64)Sub type of the log entry

log_descriptionntextShort description of the log entry
ext_referenceintSR number connected to the log entry, if applicable. Corresponds to the "id" field in the "service_req" table
user_namenvarchar(64)User who made the change. Corresponds to the "user_name" field in the "sysaid_user" table
ext_reference2intSR# 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_idnvarchar(32)SysAid account ID
computer_idnvarchar(64)ID of the computer that had the change. Corresponds to the "computer_id" field in the "computer" table

change_timedatetimeTime of the change

change_typenvarchar(64)Type of the change

change_sub_typenvarchar(64)Sub type of the change

change_descriptionntextA description of the change

log_idintID# of the change in this log

policy_idintID# of the change in this policy

user2asset

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


Field
Type
Description
account_idnvarchar(32)SysAid account ID
user_namenvarchar(64)Username of the user connected to the asset. Corresponds to the "user_name" field in the "sysaid_user" table
computer_idnvarchar(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_idintID# of the software product

account_idnvarchar(32)SysAid account ID

product_namenvarchar(255)Name you give to the software product

versionnvarchar(64)Version of the software product

vendornvarchar(64)Vendor of the software product

licensesintNumber of licenses of the software product that you've paid for

purchase_datedatetimeDate the software product was purchased

support_expirationdatetimeDate the support for the software product ends

notesntextNotes about the software product
companyintCompany the software product belongs to. Corresponds to the "company_id" in the "company" table

company_backupintFor internal use

supplierintSupplier of the software product. Corresponds to the "supplier_id" field in the "supplier" table
cust_list1intSoftware custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "softwareList1"
cust_list2intSoftware custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "softwareList2"

cust_text1nvarchar(255)Software custom text 1

cust_text2nvarchar(255)Software custom text 2

cust_notesntextSoftware custom notes

cust_int1intSoftware custom integer 1

cust_int2intSoftware custom integer 2

cust_date1datetimeSoftware custom date 1

cust_date2datetimeSoftware custom date 2

history_versionintVersion of the software product (for the software product history). Incremented by one on each save of the software product

change_timedatetimeHistory table only. Time the revision was saved
changed_bynvarchar(64)History table only. Username of the user who saved the revision. Corresponds to the "user_name" field in the "sysaid_user" table

freewarenvarchar(1)Indicates if the product is freeware (0) or licensed (1)

exceedlic_installedintNumber 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_idnvarchar(32)SysAid account ID
software_idintID# of the software product. Corresponds to the "software_id" field in the "software" table
install_namenvarchar(255)Install name connected to the software product. Corresponds to the "value" field in the "computer_lists" table
versionvarchar(255)PK changed: (software_id,install_name,version)
name_and_versionvarchar(512) 

asset_catalog, asset_catalog_history

Contains all fields on the Catalog form.


Field
Type
Description
account_idnvarchar(32)SysAid account ID
catalog_numbernvarchar(64)Catalog item ID

namenvarchar(255)Catalog item name

modelnvarchar(64)Model of the catalog item

manufacturernvarchar(64)Manufacturer of the catalog item
supplier_idintID# of the supplier of the catalog item. Corresponds to the "supplier_id" field in the "supplier" table

notesntextNotes about the catalog item

cust_date1datetimeCatalog item custom date 1

cust_date2datetimeCatalog item custom date 2

versionintVersion of the catalog item. Updated each time the catalog item is saved. Used by asset_catalog_history 

change_timedatetimeHistory table only. Time the revision was saved
changed_bynvarchar(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_idintID# of the supplier

account_idnvarchar(32)SysAid account ID

namenvarchar(255)Name of the supplier

addressntextAddress of the supplier

phonenvarchar(64)Phone number of the supplier

faxnvarchar(64)Fax number of the supplier

email_addressnvarchar(64)Email address of the supplier

notesntextNotes about the supplier

mobilenvarchar(64)Mobile number of the contact person at the supplier

phone2nvarchar(64)A second phone number for the supplier

contact_namenvarchar(64)Name of the contact person at the supplier

account_numbernvarchar(64)Your account number with the supplier
cust_list1intSupplier custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "supplierList2"
cust_list2intSupplier custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "supplierList1"

cust_text1nvarchar(255)Supplier custom text 1

cust_text2nvarchar(255)Supplier custom text 2

cust_notesntextSupplier custom notes

cust_int1intSupplier custom integer 1

cust_int2intSupplier custom integer 2

cust_date1datetimeSupplier custom date 1

cust_date2datetimeSupplier custom date 2

versionintVersion of the supplier. Incremented by one on each save of the supplier. Used by the asset history

change_timedatetimeHistory table only. Time the revision was saved
changed_bynvarchar(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
idintID# of the RDS node

discovery_service_namenvarchar(64)Name of the RDS node

create_datedatetimeDate the RDS node was installed

upgrade_datedatetimeDate the RDS node was upgraded to a newer version

last_connection_datedatetimeLast time the RDS node contacted the SysAid Server

descriptionnvarchar(255)For future use

ip_addressnvarchar(64)IP address of the machine hosting the RDS node

locationnvarchar(64)For future use

versionnvarchar(64)Version of the SysAid Remote Discovery Service

ownernvarchar(64)For future use

domainnvarchar(255)Domains visible to the RDS node

windowsnchar(1)Whether the hosting computer is Windows based (Y) or not (N)

rds_urlnvarchar(255)URL that RDS attempts to connect to (e.g. the SysAid Server URL)

client_versionnvarchar(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_versionnvarchar(64) 

gfi_buildnvarchar(64) 

pgfi_urlnvarchar(255) 

sr_notif_sentint 

mail_notif_sentint 

sms_notif_sentint 

rds_unavailableint 

logs_datenvarchar(255) 

log_levelvarchar(64) 

pm_relay_frequencyint 

pm_relay_default_start_timeint 

credentials_listvarchar(400) 

customized_snmp_oids

Stores entries to the list of Customized SNMP OIDs.


Field
Type
Description
idintCustomized OID ID#

oidnvarchar(255)Customized OID address

display_namenvarchar(255)Customized OID display name

mapped_fieldintAsset 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_namenvarchar(255)Contains the query created by the OID's Filter as it appears in the SysAid Expression Builder

filter_expressionnvarchar(255)Contains the query created by the OID's Filter

filter_nodentextContains the query created by the OID's Filter in XML format

is_writablenvarchar(1)Determines whether an OID can be written to from within SysAid (1) or not (0)
addon_db_namenvarchar(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
idintID# of the online user in this table

account_idnvarchar(32)SysAid account ID
user_namenvarchar(64)Username of the online user. Corresponds to the "user_name" field in the "sysaid_user" table
computer_idnvarchar(64)ID of the computer to which the user is logged in. Corresponds to the "computer_id" field in the "computer" table
computer_namenvarchar(64)Name of the computer to which the user is logged in. Corresponds to the "computer_name" field in the "computer" table
domainnvarchar(64)Domain of the logged in user (LDAP users only). Corresponds to the "login_domain" field in the "sysaid_user" table

client_namenvarchar(64)Method in which the user is logged in (e.g. console)

ip_addressnvarchar(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_idnvarchar(64)ID number of the user's session on the logged in computer

last_update_datedatetimeLast time the logged in computer contacted the SysAid Server

disconnectedintWhether 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_idnvarchar(32)SysAid account ID
idintID# of the online user in this table
user_namenvarchar(64)Username of the user who had been online. Corresponds to the "user_name" field in the "sysaid_user" table
computer_idnvarchar(64)ID of the computer to which the user was logged in. Corresponds to the "computer_id" field in the "computer" table
computer_namenvarchar(64)Name of the computer to which the user was logged in. Corresponds to the "computer_name" field in the "computer" table
domainnvarchar(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_namenvarchar(64)Method in which the user was logged in (e.g. console)

ip_addressnvarchar(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_idnvarchar(64)ID number of the user's session on the computer that had been logged in

login_timedatetimeTime the user logged in

logout_timedatetimeTime the user logged out

asset_offline_log

Records asset availability


Field
Type
Description
asset_idnvarchar(64)ID of the asset. Corresponds to the "computer_id" field in the "computer" table
offline_start_timedatetimeTime the asset went offline

offline_end_timedatetimeTime the asset came back online

offline_minutesintTime in minutes that the asset was offline

idint 

online_assets

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


Field
Type
Description
asset_idnvarchar(64)ID of the asset. Corresponds to the "computer_id" field in the "computer" table

is_onlineintWhether the asset is online (1) or not (0)

last_updatedatetimeThe last time the computer checked in with the SysAid Server

mdm_policy

Contains all MDM policies.


Field
Type
Description
idintID# of the MDM policy

namenvarchar(255)Name of the policy

request_timedatetimeTime the policy was last edited

revisionintRevision of the device policy. Incremented by 1 each time the policy is saved

enable_passwordnchar(1)Determines whether a mobile device requires a passcode (Y) or not (N)

allow_simple_passwordnchar(1)Determines whether a mobile device permits a simple passcode (Y) or not (N)

alphanumeric_password_reqnchar(1)Determines whether a mobile device requires an alphanumeric passcode (Y) or not (N)

min_password_lengthintMinimum passcode length

min_complex_passwordintMinimum number of complex characters in the passcode

max_password_ageintMaximum passcode length in days before user needs to change it

auto_lockintTime in minutes before mobile device locks automatically

password_historyintNumber of unique passcodes required before a passcode can be reused

max_failed_passwordintMaximum number of failed passcode attempts before all data on mobile device is erased

email_typeintEmail protocol used for the mobile device: IMAP (1), POP (2), Exchange ActiveSync (3)

account_namenvarchar(255)Name of the email account specified by the policy

path_prefix_imapnvarchar(255)When using the IMAP protocol, specifies the path prefix

user_display_namenvarchar(255)Display name of the user of the mobile device to which the policy is applied

email_addressnvarchar(64)Email address of the user of the mobile device to which the policy is applied

host_namenvarchar(64)Host name of the incoming mail server

server_portnvarchar(32)Port on which to access the incoming mail server

user_namenvarchar(64)Username on the incoming mail server of the user of the mobile device to which the policy is applied

auth_typeintThe authentication method used by the incoming mail server: Password (1), MD5 Challenge Response (2), NTLM (3), HTTP MD5 Digest (4)

use_sslnchar(1)Whether to use SSL for communication with the incoming mail server (Y) or not (N)

outoging_host_namenvarchar(64)Host name of the outgoing mail server

outgoing_server_portnvarchar(32)Port on which to access the outgoing mail server

outgoing_user_namenvarchar(64)Username on the outgoing mail server of the user of the mobile device to which the policy is applied

outgoing_auth_typeintThe authentication method used by the outgoing mail server: Password (1), MD5 Challenge Response (2), NTLM (3), HTTP MD5 Digest (4)

outgoing_use_sslnchar(1)Whether to use SSL for communication with the outgoing mail server (Y) or not (N)

domain_namenvarchar(64)Domain name of the user of the mobile device to which the policy is applied

sync_emails_date_rangeintTime 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_periodintMaximum 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
idintID# of the Wi-Fi configuration
policy_idintMDM policy to which the Wi-Fi policy is connected. Corresponds to the "id" field in the "mdm_policy" table

typeintType of Wi-Fi encryption: Open (1), WEP (2), WPA/WPA2 (3)

ssidnvarchar(255)SSID of the wireless network configured

auto_joinnchar(1)Whether to connect automatically to the network if it's within range (Y) or not (N)

hidden_networknchar(1)Whether the network is hidden (Y) or not (N)

encryption_keynvarchar(64)Password for the wireless network

revisionintRevision 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
 idintID# of the assignment
 user_namenvarchar(64)Username of the user assigned to the asset. Corresponds to the "user_name" field in the "sysaid_user" table
 login_domainnvarchar(64)Domain of the user assigned to the asset. Corresponds to the "login_domain" field in the "sysaid_user" table
 login_user_uppernvarchar(64)User's login name in all uppercase letters
 computer_idnvarchar(64)Asset assigned to the user. Corresponds to the "computer_id" field in the "computer" table
 descriptionnvarchar(255)Description of the asset
 notenvarchar(255)Note about the asset

remote_active_sessions

Contains a list of all active My Desktop sessions.


Field
Type
Description
 user_namenvarchar(64)Username of the user with an active My desktop session. Corresponds to the "user_name" field in the "sysaid_user" table
 source_hostnvarchar(64)Name of the computer that launched the My Desktop session
 target_hostnvarchar(64)Computer being remotely controlled by the My Desktop session
 session_idnvarchar(64)ID of the My Desktop session
 rcgnvarchar(64)Computer hosting the SysAid Remote Control Gateway (RCG) being used for the My Desktop session
 session_start_timebigintTime the My Desktop session was initiated

account_attributes

Contains a list of all active My Desktop sessions.


Field
Type
Description
 user_namenvarchar(64)Username of the user with an active My desktop session. Corresponds to the "user_name" field in the "sysaid_user" table
 source_hostnvarchar(64)Name of the computer that launched the My Desktop session
 target_hostnvarchar(64)Computer being remotely controlled by the My Desktop session
 session_idnvarchar(64)ID of the My Desktop session
 rcgnvarchar(64)Computer hosting the SysAid Remote Control Gateway (RCG) being used for the My Desktop session
 session_start_timebigintTime the My Desktop session was initiated
 gateway_api_base_urlvarchar(255) 
 gateway_api_modevarchar(20) 
 default_charsetvarchar(255) 
 default_export_import_charsetvarchar(255) 
 default_localevarchar(255) 
 case_insensitive_loginnumeric(1,0) 
 enable_usage_statisticsnumeric(1,0) 
 enable_it_benchmarknumeric(1,0) 
 show_it_benchmarknumeric(1,0) 
 enable_oauthnumeric(1,0) 
 enable_asset_availabilitynumeric(1,0) 
 enable_update_timersnumeric(1,0) 

asset_data_day_data


Field
Type
Description
 account_idnvarchar(32) 
 computer_idnvarchar(64) 
 check_namenvarchar(200) 
 idxint 
 check_valuefloat 
 upd_timedate 

asset_data_week_data


Field
Type
Description
 account_idnvarchar(32) 
 computer_idnvarchar(64) 
 check_namenvarchar(200) 
 idxint 
 check_valuefloat 
 upd_timedate 

asset_data_month_data


Field
Type
Description
 account_idnvarchar(32) 
 computer_idnvarchar(64) 
 check_namenvarchar(200) 
 idxint 
 check_valuefloat 
 upd_timedate 

asset_data_year_data


Field
Type
Description
 account_idnvarchar(32) 
 computer_idnvarchar(64) 
 check_namenvarchar(200) 
 idxint 
 check_valuefloat 
 upd_timedate 

patch


Field
Type
Description
 account_idnvarchar(32) 
 patch_idnvarchar(64) 
 product_namenvarchar(255) 
 vendornvarchar(255) 
 namenvarchar(255) 
 file_digestnvarchar(4000) 
 bulletinnvarchar(64) 
 titlenvarchar(4000) 
 max_download_sizeint 
 classificationint 
 severity_typenvarchar(64) 
 release_datedate 
 kb_article_idnvarchar(64) 
 switchesnvarchar(4000) 
 urlnvarchar(4000) 
 file_urlnvarchar(4000) 
 languagenvarchar(4000) 
 uninstallableint 
 security_updateint 

computer_patches


Field
Type
Description
 account_idnvarchar(32) 
 patch_idnvarchar(64) 
 computer_idnvarchar(64) 
 patch_statusint 
 failure_reasonnvarchar(255) 
 change_idint 
 installed_datedate 
 manual_event_idint 
 start_schedule_date_timedate 

patch_policy


Field
Type
Description
 policy_idint 
 account_idnvarchar(32) 
 policy_namenvarchar(255) 
 policy_datedate 
 last_scandate 
 next_scandate 
 last_patchdate 
 next_patchdate 
 last_scan_server_timedate 
 last_patch_server_timedate 
 scan_schedule  
 patch_schedule  
 reboot_settings  

gfi_products


Field
Type
Description
 product_idint 
 vendornvarchar(255) 
 product_namenvarchar(255) 

policy_gfi_products


Field
Type
Description
 policy_idint 
 product_idint 
 approve_statint 

patch_policy_status


Field
Type
Description
 policy_idint 
 patch_idnvarchar(64) 
 patch_statusint 

patch_policy_event


Field
Type
Description
 idint 
 policy_idint 
 user_namenvarchar(64) 
 event_timedate 
 is_scanint 
 is_manualint 

credentials_list


Field
TypeDescription
 idint 
 namevarchar(256) 
 user_namevarchar(256) 
 passwordvarchar(256) 
 authentication_protocolvarchar(256)

SNMP authentication protocol:

  • MD5

  • SHA
 encryption_protocolvarchar(256)

SNMP encryption protocol:

  • DES

  • TRIPLE_DES

  • AES_128_BIT

  • AES_192_BIT

  • AES_256_BIT
 encryption_keyvarchar(256)SNMP encryption key
 descriptionmediumtext 
 validityint 
 created_byvarchar(255) 
 created_datedate 
 modified_byvarchar(255) 
 modified_datedate 

vendors_list


Field
Type
Description
 idint 
 manufacturer_namevarchar(256) 
 asset_id_prefixvarchar(256) 
 manufacturer_codesmediumtext 
 descriptionmediumtext 
 created_datedate 
 modified_byvarchar(255) 
 modified_datedate 

agent_settings


Field
Type
Description
enable_video_recordingchar(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_namenvarchar(64)The username of the user

account_idnvarchar(32)SysAid account ID

passwordnvarchar(64)The user's password

first_namenvarchar(64)The user's first name

last_namenvarchar(64)The user's last name

main_usernchar(1)Indicates whether this is the main SysAid user created during installation (Y) or not (N). There is only one main user

email_addressnvarchar(64)The user's email address

sms_numbernvarchar(255)The phone number of the user's mobile device

user_confntextContains all personal preferences for a given user

phonenvarchar(64)The user's phone number

cell_phonenvarchar(64)The user's cell phone number

notesnvarchar(255)Notes about the user
locationintThe user's location. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "location"

car_numbernvarchar(64)The user's license plate number

buildingnvarchar(64)The building where the user is located

floornvarchar(64)The floor where the user is located

cubicnvarchar(64)The cubicle where the user is located

administratornchar(1)Whether the user is an administrator (Y) or an end user (N)

managernchar(1)Whether the user has access to the Manager Portal (Y) or not (N)

versionintVersion of the user profile. Incremented by one on each save of the profile. Used by the user profile history
cust_list1intUser custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "userList1"
cust_list2intUser custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "userList2"

cust_text1nvarchar(255)User custom text 1

cust_text2nvarchar(255)User custom text 2

cust_notesntextUser custom notes

cust_int1intUser custom integer 1

cust_int2intUser custom integer 2
departmentintThe user's department. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "departments"
companyintThe user's company. Corresponds to the "company_id" in the "company" table

company_backupintFor internal use

disablenchar(1)Whether the user is disabled (Y) or not (N)

expiration_timedatetimeDate the user's access to SysAid expires

cust_date1datetimeUser custom date 1

cust_date2datetimeUser custom date 2

history_versionintVersion of the user's profile. Incremented by one on each save of the user profile. Used by the user history

ldapintIndicates 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_timedatetimeHistory table only. Time the revision was saved
changed_bynvarchar(64)History table only. Username of the user who saved the revision. Corresponds to the "user_name" field in the "sysaid_user" table

email_notificationsnchar(1)Whether the user receives email notifications (Y) or not (N)

permissions_by_groupsnchar(1)Applicable to admins only: Whether the admins permissions are managed by group (Y) or individually (N)
user_manager_namenvarchar(64)Username of the user's direct manager. Corresponds to another "user_name" in the "sysaid_user" table

chat_nick_namenvarchar(64)Name to display during chat sessions

enable_login_to_eupnchar(1)Determines whether the user can log in to the End-User Portal (Y) or not (N)
agreementintSLA agreement applied to the user. Corresponds to the "id" field in the "agreement" table

display_namenvarchar(64)Name to display for this user throughout SysAid

secondary_emailnvarchar(64)Alternative user email address, used for Password Self Service unlock account and reset password

sr_email_notif_conditionntextContains the filter for which automatic SR email notifications the user receives

login_usernvarchar(64)Username that the user uses to log into SysAid

login_domainnvarchar(64)Domain that the user uses to log into SysAid

login_guidnvarchar(64)Unique login ID for LDAP users. Randomly generated for non-LDAP users

calculated_user_namenvarchar(255)"first_name" + "last_name", or if both are blank, shows "user_name"

calculated_user_name_uppernvarchar(255)The "calculated_user_name" in all uppercase letters

localenvarchar(64)User's chosen language. A full list of language options can be seen by viewing the HTML source for Preferences.jsp

timezonenvarchar(64)User's time zone. A full list of time zone options can be seen by viewing the HTML source for Preferences.jsp

charsetnvarchar(64)Encoding used to display the user's chosen language

login_user_uppernvarchar(64)"login_user" in all uppercase letters

user_name_uppernvarchar(64)"user_name" in all uppercase letters

ssp_themeint(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_namenvarchar(64)Username of the administrator. Corresponds to the "user_name" field in the "sysaid_user" table

permission_confntextList 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_idnvarchar(32)SysAid account ID
group_namenvarchar(64)Name of the user group

group_typeintType of the user group: general (0), administrators only (1), or end users only (2)

support_levelintSupport level of the group

permissionntextFor groups with group permissions, lists all permissions for the group

display_groupnchar(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_idnvarchar(32)SysAid account ID
group_namenvarchar(64)Name of the user group. Corresponds to the "group_name" field in the "user_groups" table
user_namenvarchar(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_idintID# of the company

account_idnvarchar(32)SysAid account ID

company_namenvarchar(255)Name of the company

addressnvarchar(255)Line 1 of the company address

address2nvarchar(255)Line 2 of the company address

citynvarchar(64)City where the company is located

statenvarchar(64)State where the company is located

zipnvarchar(64)Company's zip code

countrynvarchar(64)Country where the company is located

phonenvarchar(64)Company's phone number

faxnvarchar(64)Company's fax number

notesntextNotes about the company
cust_list1intCompany custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "companyList1"
cust_list2intCompany custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "companyList1"

cust_text1nvarchar(255)Company custom text 1

cust_text2nvarchar(255)Company custom text 2

cust_notesntextCompany custom notes

cust_int1intCompany custom int 1

cust_int2intCompany custom int 2

expiration_timedatetimeDate that the company's access to SysAid expires

cust_date1datetimeCompany custom date 1

cust_date2datetimeCompany custom date 2

versionintVersion of the company's profile. Incremented by one on each save of the company profile. Used by the company history

change_timedatetimeHistory table only. Time the revision was saved
changed_bynvarchar(64)History table only. Username of the user who saved the revision. Corresponds to the "user_name" field in the "sysaid_user" table
agreementintSLA agreement applied to the company. Corresponds to the "id" field in the "agreement" table

agreement_startdatetimeStart of your service agreement with the company

agreement_enddatetimeEnd of your service agreement with the company

logo_file_namenvarchar(64)Filename of the company logo you've uploaded to SysAid

email_accountnvarchar(255) 

ssp_bannerintDefault: 0

ssp_themeintDefault: 0

user_settings


Field
TypeDescription
account_idnvarchar(32)NOT NULL
user_ref_id(int11)NOT NULL

create_datebigint 

settings_jsonvarchar(4000)NOT NULL

account_settings


Field
Type
Description
account_idnvarchar(32)NOT NULL

create_datebigint 

settings_jsonvarchar(4000)NOT NULL

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
idintID# of the security question

account_idnvarchar(32)SysAid account ID

security_questionnvarchar(255)The security question

visiblenvarchar(1)Whether the question is visible (Y) or not (N)

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


FieldType
Description
account_idnvarchar(32)SysAid account ID
user_namenvarchar(64)Username of the user who answered a question. Corresponds to the "user_name" field in the "sysaid_user" table
question_idintID# of the question answered by the user. Corresponds to the "id" field in the "uss_security_questions" table

answernvarchar(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_idnvarchar(32)SysAid account ID
user_namenvarchar(64)Username of the user who attempted to answer a security question. Corresponds to the "user_name" field in the "sysaid_user" table

attemptsintNumber of unsuccessful attempts to answer a security question that the user has made

lock_datedatetimeTime that the user was locked out of Password Self Service

user_settings_available_fields


Field
TypeDescription
idnvarchar(255) 
labelnvarchar(255) 

typenvarchar(32) 

db_tablenvarchar(64) 

db_columnnvarchar(64) 

list_attribute_namenvarchar(64) 

user_conf_namenvarchar(64) 

readOnlybitDefault:0

uss_notif_events

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


Field
Type
Description
idintID# of the notification event

account_idnvarchar(32)SysAid account ID

event_namenvarchar(64)Type of event that triggered the notification: (ResetPassword) or (UnlockAccount)

notificationnvarchar(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_expressionntextContains the query created by the notification's Filter Expression as it appears in the SysAid Expression Builder

filter_sqlntextContains the query created by the notification's Filter Expression

ldap_list


Field
Type
Description
ldap_idint(11)auto_increment

namenvarchar(255) 

urlnvarchar(255) 

credentials_idint(11) 

domainnvarchar(255) 

domain_display_namenvarchar(255) 

rds_namenvarchar(255) 

disable_timedatetime 

enable_cashingnumeric(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_dnvarchar(255) 

disable_non_exist_users

numeric(1,0)

 

disable_non_exist_adminsnumeric(1,0) 

last_login_uservarchar(255) 

last_login_datedatetime 

schedule  

next_run  

rerun_value  

ldap_enablenumeric(1,0)Default:0

save_datedatetime 

last_errorvarchar(255) 

save_action

numeric(1,0)

 

ldap_user_dn_list


Field
Type
Description

ldap_idint(11) 

user_dn_id

int(11) 

user_dn

nvarchar(255) 

ldap_user_roots_list


Field
Type
Description
ldap_idint(11) 

user_roots_id

int(11) 

user_roots

  

ldap_group_roots_list


Field
Type
Description
ldap_idint(11) 

group_roots_id

int(11) 

group_roots

  

ldap_user_attribute_list


Field
Type
Description
ldap_idint(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
idintProject ID#

account_idnvarchar(32)SysAid account ID

versionintVersion of the project. Incremented by one on each save of the project. Used by the project history
categoryintProject category. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "projectCats"

titlenvarchar(255)Title of the project

descriptionntextDescription of the project
statusintProject status.  Corresponds to the "value_key" field in the "cust_values" table for "list_name" "projectStatuses"

notesntextNotes about the project

start_timedatetimeProject start time

end_timedatetimeProject end time

raw_estimationintEstimated time to complete project in hours
request_groupnvarchar(64)Group that requested the project. Corresponds to the "group_name" field in the "user_groups" table
managernvarchar(64)Project manager. Corresponds to the "user_name" field in the "sysaid_user" table
assigned_groupnvarchar(64)Group assigned to the project. Corresponds to the "group_name" field in the "user_groups" table
cust_list1intProject custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "prList1"
cust_list2intProject custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "prList2"

cust_text1nvarchar(255)Project custom text 1

cust_text2nvarchar(255)Project custom text 2

cust_notesntextProject custom notes

cust_int1intProject custom integer 1

cust_int2intProject custom integer 2
companyintCompany for which the project is being done. Corresponds to the "company_id" in the "company" table

company_backupintFor internal use

incidentTitlenvarchar(255)Title to give to incidents created by notifications from this project

cust_date1datetimeProject custom date 1

cust_date2datetimeProject custom date 2

progressintProgress towards completion of the project. Displayed as a %

project_log

Contains the contents of the project history tab.


Field
Type
Description
log_idintID# of the log entry

account_idnvarchar(32)SysAid account ID
project_idintID# of the associated project. Corresponds to the "id" field in the "project" table

log_timedatetimeTime the project was updated

log_typenvarchar(64)Type of update made to the project

log_descriptionntextDescription of the update made to the project

ext_referenceintEntry number of the log for a given project
user_namenvarchar(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
idintID# of the task

account_idnvarchar(32)SysAid account ID

versionintVersion of the task. Incremented by one on each save of the task. Used by the task history
project_idintID# of the parent project. Corresponds to the "id" field in the "project" table
categoryintTask category. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "taskCats"

titlenvarchar(255)Title of the task

descriptionntextDescription of the task
statusintStatus of the task. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "taskStatuses"

notesntextNotes about the task

progressintProgress of the task. Measured as a %

start_timedatetimeTask start time

end_timedatetimeTask end time

estimationintEstimated time needed to complete the task (in hours)
cust_list1intTask custom list 1. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "taskList1"
cust_list2intTask custom list 2. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "taskList2"

cust_text1nvarchar(255)Task custom text 1

cust_text2nvarchar(255)Task custom text 2

cust_notesntextTask custom notes

cust_int1intTask custom integer 1

cust_int2intTask custom integer 2

notificationnvarchar(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_date1datetimeTask custom date 1

cust_date2datetimeTask custom date 2
ci_idintID# of the CI attached to the task. Corresponds to the "id" field in the "ci_attributes" table
task_dependencyintID# of the task upon which this task is dependent. Corresponds to another "id" in the "task" table

task_dependency_typeintType 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_idintID# of the log entry

account_idnvarchar(32)SysAid account ID
task_idintID# of the associated task. Corresponds to the "id" field in the "task" table

log_timedatetimeTime the task was updated

log_typenvarchar(64)Type of update made to the task

log_descriptionntextDescription of the update made to the task

ext_referenceintEntry number of the log for a given task
user_namenvarchar(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
idintID# of the table entry
account_idnvarchar(32)SysAid account ID
user_namenvarchar(64)Username of the user assigned to the task. Corresponds to the "user_name" field in the "sysaid_user" table
user_roleintThe 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
idintActivity#
task_idintID# of the task the activity is attached to. Corresponds to the "id" field in the "task" form

account_idnvarchar(32)SysAid account ID
user_namenvarchar(64)Username of the user who left the activity. Corresponds to the "user_name" field in the "sysaid_user" table

from_timedatetimeActivity start time

to_timedatetimeActivity end time

descriptionnvarchar(4000)A description of the activity performed
activity_statusintStatus of the activity. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "activityStatuses"
ci_idintID# 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
idintCI ID#

account_idnvarchar(32)SysAid account ID

ci_namenvarchar(64)Name of the CI

serialnvarchar(64)Serial number of the CI
ci_typeintType of the CI. Corresponds to the "id" field in the "ci_type" table
locationintCI location. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "location"
ownernvarchar(64)Owner of the CI. Corresponds to the "user_name" field in the "sysaid_user" table
owner_groupnvarchar(64)Group that owns the CI. Corresponds to the "group_name" field in the "user_groups" table

companyintCompany that the CI belongs to. Corresponds to the "company_id" in the "company" table

company_backupintFor internal use
supplierintSupplier of the CI. Corresponds to the "supplier_id" field in the "supplier" table

supply_datedatetimeDate the CI is supposed to arrive

accept_datedatetimeDate the CI actually arrives
statusintStatus of the CI. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "CIStatus"
priorityintPriority of the CI. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "priority"

notesntextNotes about the CI
import_item_idnvarchar(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_typeintEntity from which the CI was imported: Not imported (0), Asset (1), Software Product (2), or Catalog Item (3)

import_descnvarchar(255)History table only. Type and name of the import item

history_versionintVersion of the CI. Incremented by one on each save of the CI. Used by the CI history

ci_cust_text_1 - ci_cust_text_50nvarchar(64)CI custom text 1 - 50

ci_cust_long_text_1 - ci_cust_long_text_50ntextCI custom long text 1 - 50

ci_cust_date_1 - ci_cust_date_50datetimeCI custom date 1 - 50
ci_cust_list_1 - ci_cust_list_50intCI 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_50intCI custom integer 1 - 50

change_timedatetimeHistory table only. Time the revision was saved
changed_bynvarchar(64)History table only. Username of the user who saved the revision. Corresponds to the "user_name" field in the "sysaid_user" table
problem_typenvarchar(64)Category of the CI. Corresponds to the "problem_type" field in the "problem_type" table
problem_sub_typenvarchar(64)Sub-category of the CI. Corresponds to the "problem_sub_type" field in the "problem_type" table
third_level_categorynvarchar(64)Third-level category of the CI. Corresponds to the "third_level_category" field in the "problem_type" table
ci_sub_typeintSub-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
idintID# of the CI type

account_idnvarchar(32)SysAid account ID

ci_type_namenvarchar(64)Name of the CI type

descriptionntextDescription of the CI type

predefinednvarchar(1)Whether the CI type is predefined (Y) or not (N)

ci_sub_type

List of all CI sub types.


Field
Type
Description
idintID# of the CI sub type

account_idnvarchar(32)SysAid account ID
ci_type_idintID# of the parent CI type. Corresponds to the "id" field on the "ci_type" table

captionnvarchar(64)Name of the CI sub type

file_namenvarchar(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
srcintID# of the source CI. Corresponds to the "id" field in the "ci_attributes" table
destintID# of the destination CI. Corresponds to the "id" field in the "ci_attributes" table
ci_relation_typeintRelation 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
TypeDescription
idintCI relation type ID#

account_idnvarchar(32)SysAid account ID

relation_namenvarchar(64)Name of the relationship, such as "installed on"

opposite_relation_namenvarchar(64)Name of the relationship in the other direction, such as "contains software"

predefinednvarchar(1)Whether the relationship type is predefined (Y) or not (N)

ci_template

Holds CI templates.


Field
Type
Description
account_idnvarchar(32)SysAid account ID
template_namenvarchar(64)Name of the CI template
ci_typeintType of the CI created by the template. Corresponds to the "id" field in the "ci_type" table
locationintLocation of the CI created by the template. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "location"
ownernvarchar(64)Owner of the CI created by the template. Corresponds to the "user_name" field in the "sysaid_user" table
owner_groupnvarchar(64)Group that owns the CI created by the template. Corresponds to the "group_name" field in the "user_groups" table
companyintCompany that the CI created by the template belongs to. Corresponds to the "company_id" in the "company" table

company_backupintFor internal use
supplierintSupplier of the CI created by the template. Corresponds to the "supplier_id" field in the "supplier" table

supply_datedatetimeDate the CI created by the template is supposed to arrive

accept_datedatetimeDate the CI created by the template actually arrives
statusintStatus of the CI created by the template. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "CIStatus"
priorityintPriority of the CI created by the template. Corresponds to the "value_key" field in the "cust_values" table for "list_name" "priority"

notesntextNotes about the CI created by the template

ci_cust_text_1 - ci_cust_text_50nvarchar(64)CI template custom text 1 - 50

ci_cust_long_text_1 - ci_cust_long_text_50ntextCI template custom long text 1 - 50

ci_cust_date_1 - ci_cust_date_50datetimeCI template custom date 1 - 50
ci_cust_list_1 - ci_cust_list_50intCI 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_50intCI template custom integer 1 - 50
problem_typenvarchar(64)Category of the CI created by the template. Corresponds to the "problem_type" field in the "problem_type" table
problem_sub_typenvarchar(64)Sub-category of the CI created by the template. Corresponds to the "problem_sub_type" field in the "problem_type" table
third_level_categorynvarchar(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_typeintSub-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_namenvarchar(64)Name of the template to which the link belongs. Corresponds to the "template_name" field in the "ci_template" table
ci_typeintType of the CI created by the template. Corresponds to the "id" field in the "ci_type" table
account_idnvarchar(32)SysAid account ID
file_idnvarchar(64)Database name of the link, including full path

linknvarchar(255)The hyperlink itself

file_namenvarchar(255)Description of the link

file_datedatetimeTimestamp 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_idnvarchar(32)SysAid account ID
asset_fieldnvarchar(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_fieldnvarchar(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"

positionintPosition in the list of mapped fields

user2ci

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


Field
TypeDescription
account_idnvarchar(32)SysAid account ID
user_namenvarchar(64)User of the CI. Corresponds to the "user_name" field in the "sysaid_user" table
ci_idintID# of the CI. Corresponds to the "id" field on the "ci_attributes" table

Monitoring

monitor_templates

Contains all monitoring templates.


Field
TypeDescription
account_idnvarchar(32)SysAid account ID
template_namenvarcharName of the monitoring template
is_servernchar(1)Whether the template applies to servers (1) or not (0)
check_typenvarchar(32)The type of monitoring rule
check_namenvarchar(200)The name of the monitoring rule

protocolnvarchar(64)The protocol to use for monitoring rules that connect directly to another device (e.g. http, ping)

port_numintThe port number checked by the monitoring template

url_pathnvarchar(255)The URL checked by the monitoring template

expressionnvarchar(255)The expression searched for in URL monitoring, software/hardware/device updates, running services checks, etc.

update_typenvarchar(64)The type of update to a computer (Software, Hardware, etc.)

warning_atintWarning trigger number

warning_notificationnvarchar(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_atintError trigger number

error_notificationnvarchar(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
TypeDescription
account_idnvarchar(32)SysAid account ID
computer_idnvarchar(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_servernchar(1)Type of the monitoring rule: Workstation (0), Server (1), Network Device (4), Workstation Template (2), or Server Template (3)

template_namenvarchar(64)For assets attached to a template, the name of the attached template. Corresponds to the "template_name" field in the "monitor_templates" table

notificationnvarchar(64)No data error notification. Notifications are stored in the account conf, located in the "account_conf" field in the "account" table

alertintWhether there is a no data notification alert (1) or not (0)

sr_sentintIndicates if an incident was opened after the no data notification error (1) or not (0)

mail_sentintIndicates if an email was sent after the no data notification error (1) or not (0)

sms_sentintIndicates if an SMS was sent after the no data notification error (1) or not (0)

monitoring_enablednchar(1)Indicates whether monitoring is enabled (1) or not (0)

monitor_embed_data

Stores current monitoring results for each monitoring configuration.


FieldType
Description

account_idnvarchar(32)SysAid account ID
computer_idnvarcharID of the monitored computer. Corresponds to the field "computer_id" in the "computer" table

is_servernchar(1)Whether the monitoring rule applies to servers (1) or not (0)

check_typenvarchar(32)The type of monitoring rule

check_namenvarchar(200)The target of the monitoring rule, e.g. Ping, memUsage, TCP/IP1527

protocolnvarchar(64)The protocol to use for monitoring rules that connect directly to another device (e.g. http, ping)

port_numintThe port number checked by the monitoring template

url_pathnvarchar(255)The URL checked by the monitoring template

expressionnvarchar(255)The expression searched for in URL monitoring, software/hardware/device updates, running services checks, etc.

update_typenvarchar(64)The type of update to a computer (Software, Hardware, etc.)

warning_atintWarning trigger number

warning_notificationnvarchar(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_atintError trigger number

error_notificationnvarchar(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_dayintID# 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_weekintID# 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_monthintID# 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_yearintID# 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_daydatetimeTime of the last poll of this asset

time_weekdatetimeTime the last weekly average was calculated for this asset

time_monthdatetimeTime the last monthly average was calculated for this asset

time_yeardatetimeTime the last yearly average was calculated for this asset

warn_sr_sentintIndicates if a warning incident was opened after the latest monitoring warning (1) or not (0)

warn_mail_sentintIndicates if a warning email was sent after the latest monitoring warning (1) or not (0)

warn_sms_sentintIndicates if a warning SMS was sent after the latest monitoring warning (1) or not (0)

err_sr_sentintIndicates if an error incident was opened after the latest monitoring error (1) or not (0)

err_mail_sentintIndicates if an error email was sent after the latest monitoring error (1) or not (0)

err_sms_sentintIndicates if an error SMS was sent after the latest monitoring error (1) or not (0)

alertintIndicates the alert type for this monitoring rule: Normal (0), Warning (1), or Error (2)

extra_datanvarchar(255)Stores additional information for specific monitoring tests

predefined_checkintIndicates 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

FieldTypeDescription

account_idnvarchar(32)SysAid account ID
computer_idnvarchar(64)ID of the monitored computer. Corresponds to the field "computer_id" in the "computer" table

check_namenvarchar(64)Name of the monitoring rule, monitored service/process, etc.

idxintID# of the results from a single poll of the asset

check_valuefloatResults of the poll of the asset. Can be binary (0/1) or a data value, depending upon the specific monitoring rule

check_value2floatFor network activity, the outgoing data value

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


FieldType
Description

account_idnvarchar(32)SysAid account ID
computer_idnvarchar(64)ID of the monitored computer. Corresponds to the field "computer_id" in the "computer" table

check_namenvarchar(64)Name of the monitoring rule, monitored service/process, etc.

idxintID# of the results from the average of 1/2 hour's worth of polls of the asset

check_valuefloatResults 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_value2floatResults of the average of 1/2 hour's worth of polls of the asset. For network activity, the outgoing data value

upd_timedatetimeTime 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_idnvarchar(32)SysAid account ID
computer_idnvarchar(64)ID of the monitored computer. Corresponds to the field "computer_id" in the "computer" table

check_namenvarchar(64)Name of the monitoring rule, monitored service/process, etc.

idxintID# of the results from the average of two hours' worth of polls of the asset

check_valuefloatResults 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_value2floatResults of the average of two hours' worth of polls of the asset. For network activity, the outgoing data value

upd_timedatetimeTime 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_idnvarchar(32)SysAid account ID
computer_idnvarchar(64)ID of the monitored computer. Corresponds to the field "computer_id" in the "computer" table

check_namenvarchar(64)Name of the monitoring rule, monitored service/process, etc.

idxintID# of the results from the average of 1 day's worth of polls of the asset

check_valuefloatResults 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_value2floatResults of the average of 1 day's worth of polls of the asset. For network activity, the outgoing data value

upd_timedatetimeTime the average was taken

traps_data

Records SNMP traps received by the SysAid Server.


Field
Type
Description

account_idnvarchar(32)SysAid account ID
computer_idnvarchar(64)ID of the asset that sent the trap. Corresponds to the field "computer_id" in the "computer" table

trap_0intNumber of traps of level 0 received by the SNMP device

trap_1intNumber of traps of level 1 received by the SNMP device

trap_2intNumber of traps of level 2 received by the SNMP device

trap_3intNumber of traps of level 3 received by the SNMP device

trap_4intNumber of traps of level 4 received by the SNMP device

trap_5intNumber of traps of level 5 received by the SNMP device

trap_6intNumber of traps of level 6 received by the SNMP device

monitor_events

Contains the data of the Monitoring Events.


Field
TypeDescription

idintID# of the monitoring event

severityintType of the monitoring event: Normal (0), Warning (1), Error (2)

sourcenvarchar(255)ID of the asset that generated the monitoring event. Corresponds to the "computer_id" field in the "computer" table

source_namenvarchar(255)Name of the source computer

monitor_typenvarchar(64)Type of the monitoring rule

monitor_targetnvarchar(255)The target of the monitoring rule, e.g. Ping, memUsage, TCP/IP1527

categorynvarchar(64)Type of asset affected by the monitoring rule: Workstation (0), Server (1), Network Device (4), Workstation Template (2), or Server Template (3)

upd_timedatetimeTime the monitoring event was logged

check_valuefloatValue returned by the monitoring rule. Can be binary (0/1), or a numerical value

expressionnvarchar(255)The expression searched for in URL monitoring, software/hardware/device updates, running services checks, etc.

warning_thresholdintWarning trigger number

warning_notificationnvarchar(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_thresholdintError trigger number

error_notificationnvarchar(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_value1floatStores incoming data usage for network monitoring

check_value2floatStores 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_dataMonitoring for computer services
processes_period_dataMonitoring for computer processes
performance_period_dataMonitoring for performance rules, such as HD usage, memory usage, CPU usage, etc.
comp_update_period_dataMonitoring for software, hardware, and device updates
network_period_dataPorts monitoring
asset_data_period_dataMonitoring of asset data
network_activity_period_dataNetwork usage monitoring
customized_period_dataMonitoring of customized monitoring rules
url_period_dataURL monitoring

SLA/SLM

agreement

Lists all SLAs you've created.


Field
TypeDescription

account_idnvarchar(32)SysAid account ID

idintID# of the agreement

titlenvarchar(255)Name of the agreement

measurements_lists, measurements_lists_history

Defines all measurement lists.


Field
TypeDescription

account_idnvarchar(32)SysAid account ID

idintID# of the measurement list

titlenvarchar(255)Name of the measurement list

field_namenvarchar(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_fieldnvarchar(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_typesnvarchar(64)Type of SRs included in the measurement: Incident (1), Problem (6), Change (4), Request (10)

include_statusesntextOnly 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_sqlntextContains the query created by the measurement list's Filter Expression

filter_xmlntextContains the query created by the measurement list's Filter Expression in XML format

filter_expressionntextContains the query created by the measurement list's Filter Expression as it appears in the SysAid Expression Builder

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

versionintVersion of the measurement list. Incremented by one on each save of the measurement list. Used by the measurement list history

change_timedatetimeHistory table only. Time the revision was saved

changed_bynvarchar(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_idnvarchar(32)SysAid account ID

idintID# of the measurement

titlenvarchar(255)Name of the measurement

agreement_idintID# of the agreement to which the measurement is attached. Corresponds to the "id" field in the "agreement" table

parent_idintID# of the parent measurement. Corresponds to the "id" field in this table

weightintWeight of the measurement when creating average grades for parent measurements

formula1nvarchar(64)Calculation used to calculate the measurement: Average (avg), Minimum (min), Maximum (max), Count (count), Sum (sum), Ration (ratio)

list1_idintFirst measurement list used in the calculation. Default is "1" for non-calculated measurements. Corresponds to the "id" field in the "measurements_lists" table

formula2nvarchar(64)Not in use

list2_idintSecond 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_intervalintHow far back in time to take data for the measurement: Daily (1), Monthly (2), Yearly (3), Total (4)

unitsnvarchar(64)Units of the measurement result, e.g. percent

critical_gradeintSLA grade to assign if the measurement results are in the critical range

warning_gradeintSLA grade to assign if the measurement results are in the warning range

optimum_gradeintSLA grade to assign if the measurement results are in the optimum range

goal_criticalintMeasurement result at which the internal goal receives the critical grade

goal_warningintMeasurement result at which the internal goal receives the warning grade

goal_optimumintMeasurement result at which the internal goal receives the optimum grade

sla_criticalintMeasurement result at which the SLA performance receives the critical grade

sla_warningintMeasurement result at which the SLA performance receives the warning grade

sla_optomumintMeasurement result at which the SLA performance receives the optimum grade

calculatednchar(1)Whether the measurement is calculated based upon a measurement list (Y) or whether it's the average of other measurements (N)

enablednchar(1)Whether the measurement is enabled (Y) or not (N)

versionintVersion of the measurement. Incremented by one on each save of the measurement. Used by the measurement history

change_timedatetimeHistory table only. Time the revision was saved

changed_bynvarchar(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_idnvarchar(32)SysAid account ID
agreementintSLA 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)
measurementintMeasurement for which the list value is included. Corresponds to the "id" field in the "measurements_def" table
listintMeasurement list which the value is part of. Corresponds to the "id" field in the "measurements_lists" table
service_req_idintID of the SR from which the measurement value is taken. Corresponds to the "id" field in the "service_req" table

list_valuefloatMeasured value from the SR based upon the field selected in the "field_name" field in the "measurements_lists" table

run_datedatetimeTime 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_idnvarchar(32)SysAid account ID
measurement_idintID# of the measurement. Corresponds to the "id" field in the "measurements_def" table

current_resultfloatNumerical result for a calculated measurement

sla_gradefloatGrade given for a measurement based upon the SLA goal

internal_gradefloatGrade given for a measurement based upon the internal goal

run_datedatetimeDay on which the measurement was calculated (time is not calculated here and is displayed as 12:00:00)

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


FieldType
Description

idintID# of the chat queue

namenvarchar(64)Name of the chat queue

group_namenvarchar(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_messagentextMessage displayed to end user who begins a chat

welcome_message_from_agentntextMessage displayed to end user when an admin initiates a chat

idle_messagentextMessage displayed to admin and end user when a chat becomes idle

operator_accept_messagentextMessage displayed when an admin accepts a chat

operator_release_messagentextMessage displayed when an admin releases a chat

offline_image_urlnvarchar(64)URL to offline chat image. If blank, default icon is used

online_image_urlnvarchar(64)URL to online chat image. If blank, default icon is used

add_hour_in_chat_sessionnchar(1)Whether or not to add a timestamp to each chat line (1) or not (0)

embed_in_site_scriptntextHTML text to embed in company website to enable chatting without accessing the End-User Portal

time_before_idleintTime in minutes ellapsed with no messages sent before a chat becomes idle

time_before_closeintTime in minutes ellapsed with no messages sent before a chat automatically closes

allow_offline_chatnchar(1)Whether to allow leaving a message when chat is offline (1) or not (0)

submit_offline_chat_messagentextMessage displayed to end users after submitting an SR when chat is unavailable

display_details_screennchar(1)Whether to display the end user details screen before the chat begins (1) or not (0)

email_addressnvarchar(256)Reserved

operator_close_messagentextMessage displayed to end user when an admin closes a chat

chat_queue_messages

Stores automatic texts for chat.


FieldType
Description

idintID# of the chat automatic text

titlenvarchar(64)Title of the automatic text

messagentextText to be inserted into the chat

queueintQueue 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_idintID# of the chat session

chat_statusintStatus of the chat indicated to admin by color of bell: Orange (1), Green (2), Red (3), Closed chat (4)

request_usernvarchar(64)End user participating in the chat. Corresponds to the "user_name" field in the "sysaid_user" table

service_requestintNumber of any associated SR. Corresponds to the "id" field in the "service_req" table

queuenvarchar(64)Queue the chat is assigned to. Corresponds to the "id" field in the "chat_queue" table

assigned_usernvarchar(64)Admin who was last assigned to the chat. Corresponds to the "user_name" field in the "sysaid_user" table

session_passwordnvarchar(64)Password used for authentication during the session

full_namenvarchar(255)Full name of the end user participating in the chat

email_addressnvarchar(64)Email address of the end user participating in the chat

ip_addressnvarchar(64)IP address of the end user participating in the chat

start_timedatetimeTime the chat was initiated

close_timedatetimeTime the chat was closed

update_timedatetimeTime the chat was last updated by an admin or end user sending text

accept_timedatetimeTime the chat was accepted by an admin. If a chat is released and then reaccepted, the last accept time is used

account_idnvarchar(64)SysAid account ID

line_countintNumber of lines of text in a chat as measured by the number of times that the admin or end user pressed Send/Enter

session_textntextTranscript 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_idnvarchar(32)SysAid account ID

idintID# of the KB article

titlenvarchar(255)Title of the KB article

questionntextKB article question

answerntextAnswer to the KB article question

categorynvarchar(64)Category of the KB article. Corresponds to the "problem_type" field in the "problem_type" table

sub_categorynvarchar(64)Sub category of the KB article. Corresponds to the "problem_sub_type" field in the "problem_type" table

third_level_categorynvarchar(64)Third level category of the KB article. Corresponds to the "third_level_category" field in the "problem_type" table

update_timedatetimeTime a KB article was last updated

user_topic_viewsintTimes the KB article has been viewed (includes admins and end users)

created_ondatetimeTime the KB article was created

created_bynvarchar(64)Admin who created the KB article. Corresponds to the "user_name" field in the "sysaid_user" table

update_bynvarchar(64)Admin who last updated the KB article. Corresponds to the "user_name" field in the "sysaid_user" table

enable_expireintWhether the KB article expires (1) or not (0)

expire_datedatetimeDate the KB article expires

publishintWhether the article is published to the End-User Portal (1) or not (0)

voteYesintNumber of thumbs-up votes for an article

voteNointNumber of thumbs-down votes for an article

question_no_htmlnvarchar(max) 

answer_no_htmlnvarchar(max) 

faq_files

Holds KB and FAQ attachments.

faq_tags

Stores tags for KB and FAQ articles.


Field
Type
Description

idintID# of the KB article. Corresponds to the "id" field in the "faq" table

tagnvarchar(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_idnvarchar(32)SysAid account ID

customer_namenvarchar(64)For internal use

expiration_timedatetimeLicense expiration date

serial_keynvarchar(64)SysAid serial key

account_confntextContains various settings and preferences for SysAid. Do not edit manually!

versionintVersion of the account table. Incremented by one on each save of the account table

messages

Records SysAid instant messages.


Field
Type
Description

message_idintAuto increment
user_namenvarchar(64)User to whom the instant message was sent. Corresponds to the "user_name" field in the "sysaid_user" table

sent_timedatetimeTime the instant message was sent

sendernvarchar(64)User who sent the instant message. Corresponds to the "user_name" field in the "sysaid_user" table

recv_flagintWhether the admin has received the instant message already (1) or has still not received it (0)

msgntextContents of the instant message

msgidnvarchar(64)Internal SysAid ID for the message

service_request_idintFor 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
TypeDescription

account_idnvarchar(32)SysAid account ID

list_namenvarchar(64)Name of the custom list

value_keyintNumber of the entry in the list

value_captionnvarchar(255)Name of an entry in a specific list

value_classintStatus class for entries in the list with "list_name" "status": Open (0), Closed (1), Deleted/Ignore (2)

module_relevanceintUsed 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_groupnvarchar(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_idnvarchar(32)SysAid account ID
list_namenvarchar(64)Name of the list. Corresponds to the "list_name" field in the "cust_values" table

sort_bynvarchar(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_idintID# of the news item

account_idnvarchar(32)SysAid account ID

presentnchar(1)Whether the news item is visible (y) or not (n)

titlenvarchar(255)Title of the news item

descriptionntextContent of the news item

administratornchar(1)Display the news to administrators only (y), end users only (n), or everybody (a)

urgencynchar(1)Whether the news item is urgent (y) or not (n)

insert_timedatetimeNews item insert time

companyintID# of the company to show the news to. Corresponds to the "company_id" in the "company" table. (0) displays news to all companies

company_backupintFor internal use

assigned_groupnvarchar(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_idnvarchar(32)SysAid account ID

idintID# of the survey question

question_textntextText of the survey question

enablednchar(1)Whether the question is enabled (1) or not (0)

display_commentnchar(1)Whether to display a free text field for user comments (1) or not (0)

positionintPosition 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_idnvarchar(32)SysAid account ID
question_idintID# of the question. Corresponds to the "id" field in the "survey_questions" table

answer_idintID# of the answer

answer_textntextText of the answer

satisfaction_survey

Stores end user responses to the survey questions.


Field
Type
Description

account_idnvarchar(32)SysAid account ID
sr_idintID# of the SR for which the survey was submitted. Corresponds to the "id" field in the "service_req" table

answerintAnswer 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"

commentsnvarchar(255)Text entered into the comments field by the end user

update_timedatetimeTime the survey was answered

responsibilitynvarchar(64)Admin assigned to the SR. Corresponds to the "user_name" field in the "sysaid_user" table
question_idintID# 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

idintID# of the log entry

account_idnvarchar(32)SysAid account ID

audit_datedatetimeDate and time of the log entry

audit_moduleint

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_moduleintSub module that created the log entry. For a list of all sub module names, see the HTML source for AuditLog.jsp

audit_typeintType of log entry: Purge (1), Failure (2), Success (3), Update (4), Assignment (5), Remote Access (6), License Exceed (7)

audit_sub_typeintSub type of log entry: Task (1), Account (2)

audit_severityintSeverity of the log entry: Info (1), Alert (2)

user_namenvarchar(64)User, service, or process whose action created the audit log entry

audit_infonvarchar(4000)Text describing the log entry

max_line_idintFor 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
TypeDescription

FieldTypeDescription
log_idintID# of the log. Corresponds to the "id" field in the "audit_log" table

line_idintID# of the line in this log

audit_datedatetimeTimestamp of the entry into the log

account_idnvarchar(32)SysAid account ID

audit_infonvarchar(255)Details of the log entry

sysaid_item_links

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


Field
Type
Description

account_idvarchar(32)SysAid account ID
item_idnvarchar(64)The ID of the first of the two linked items

item_typeintThe 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_idnvarchar(64)The ID of the second of the two linked items

linked_item_typeintThe 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_itemnvarchar(1)Whether the linked item is a Main Asset, Main CI, Main Project, or Main Task (1) or not (0)

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


FieldType
Description

idintID# of the reminder

account_idnvarchar(32)SysAid account ID

reminder_namenvarchar(64)Field upon which the reminder is based. For a list of possible fields, see the HTML source for RemindersList.jsp

notificationnvarchar(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_beforeintNumber of days in advance of date to send the reminder

alert_timenvarchar(64)Hour of the day to send the reminder. Must be in format HH:MM

filter_expressionntextContains the query created by the reminder's Filter Expression as it appears in the SysAid Expression Builder

filter_sqlntextContains the query created by the reminder's Filter Expression

user_favorites

Stores user favorites (URL bookmarks).


Field
Type
Description

idintID# of the favorite

account_idnvarchar(32)SysAid account ID

captionnvarchar(64)Name of the favorite

url_stringnvarchar(255)URL of the favorite

user_namenvarchar(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

idintID# of the custom field

account_idnvarchar(32)SysAid account ID

entity_typenvarchar(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_captionnvarchar(64)Caption of the field to display in forms and lists

field_typenvarchar(64)Type of the field: Text (string), Integer (int), Float (float), Text area (long), Date (date), List (list)

attribute_namenvarchar(64)Internal name of the custom field created as follows: (CustomColumn) + "id" + "entity_type"

addon_db_namenvarchar(64)Name of the new field in the SysAid DB

compatibility_modeNchar(1)For internal use

write_in_listntextDisplay in List text

write_in_formntextDisplay in Form text

write_in_form_mobilentextDisplay in Mobile Form text

read_data_from_formntextServer-side validation text

validation_in_formntextClient-side validation text

hidden_control_in_formntextFor internal use

upload_from_fileNchar(1)For internal use

custom_triggers

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


Field
Type
Description

account_idnvarchar(32)SysAid account ID

entity_typenvarchar(64)SysAid entity to which the trigger applies

trigger_onloadntextText of the On Load trigger

trigger_before_saventextText of the Before Save trigger

trigger_after_saventextText of the After Save trigger

trigger_onload_lastlogntextLog file for the last time the On Load trigger was run

trigger_before_save_lastlogntextLog file for the last time the Before Save trigger was run

trigger_after_save_lastlogntextLog file for the last time the After Save trigger was run

compatibility_modeNchar(1)For internal use

custom_notification

Records all custom notification fields added to the SysAid DB.


Field
Type
Description

idintID# of the custom notification

namenvarchar(256)Custom notification name

subject_notification_valuemediumtext 

subject_notification_defaultmediumtext 

body_notification_valuemediumtext 

body_notification_defaultmediumtext 

addon_db_namenvarchar(256) 

sysaid_events

Records SysAid Events you create for the SysAid Calendar.


Field
TypeDescription

account_idnvarchar(32)SysAid account ID

idintID# of the event

titlenvarchar(255)Title of the event

descriptionntextDescription of the event

start_datedatetimeStart time of the event

end_datedatetimeEnd time of the event

available_groupsnvarchar(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_usernvarchar(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_namenvarchar(64)Username of the user registered to push notifications. Corresponds to the "user_name" field in the "sysaid_user" table

account_idnvarchar(32)SysAid account ID

device_typenvarchar(64)Type of mobile device registered to receive notifications

device_idnvarchar(255)ID of the mobile device

enable_datedatetimeDate and time the device was registered

is_productionnchar(1)For internal use

is_chat_onlineintFor future use

login_log

Records all SysAid login attempts and logouts.


Field
Type
Description

log_timedatetimeTime of the login attempt

account_idnvarchar(32)SysAid account ID

user_namenvarchar(64)Username of the user who attempted to log in. Corresponds to the "user_name" field in the "sysaid_user" table

statusnvarchar(64)Type of login attempt: (Logged In), (Login Failed), or (Logged Out)

ip_addressnvarchar(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_namenvarchar(64)Username of the user who visited the form. Corresponds to the "user_name" field in the "sysaid_user" table

account_idnvarchar(32)SysAid account ID

form_captionnvarchar(255)Short description of the form, including ID# and Title, if applicable

form_urlnvarchar(255)URL of the visited form

form_visit_timedatetimeTime 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!


FieldTypeDescription

account_idnvarchar(32)SysAid account ID

list_namenvarchar(64)Name of the list to which the view applies

list_view_namenvarchar(64)Name of the list view as it appears in the drop-down list of list views

user_namenvarchar(64)Deprecated

list_confntextAn XML file that contains the layout of the list

enable_deletenchar(1)Whether the view can be deleted (Y) or not (N)

versionintVersion of the list view. Incremented by one on each save of the list view

account_properties


Field
Type
Description

property_keyvarchar(255)NOT NULL

property_valuevarchar(255) 

property_typevarchar(255) 

addon_attributes


Field
Type
Description

addon_namevarchar(255) 

attribute_namevarchar(255) 

attribute_valuelongtext 

Addon


Field
Type
Description

titlevarchar(255) 

addon_typevarchar(255) 

linkvarchar(255) 

link_textvarchar(255) 

addon_history


Field
Type
Description

using_sysaid_gatewaynumeric(1) 

sc_banner


Field
Type
Description

idint(11)auto_increment

namevarchar(64) 

banner_textvarchar(64) 

image_file_namevarchar(255) 

image_overlayint(11) 

text_colorvarchar(64) 

banner_linkvarchar(255) 

last_modified_timedatetime 

last_modified_byvarchar(255) 

heightint(11)Defaults to 1

sc_theme


Field
Type
Description

idint(11)auto_increment

namevarchar(255) 

last_modified_timedatetime 

last_modified_byvarchar(255) 

brand_colorvarchar(64) 

brand_contrastvarchar(64) 

page_backgroundvarchar(64) 

headline_colorvarchar(64) 

text_colorvarchar(64) 

header_backgroundvarchar(64) 

menu_textvarchar(64) 

toolbar_backgroundvarchar(64) 

button_backgroundvarchar(64) 

button_contentvarchar(64) 

border_colorvarchar(64) 

hover_colorvarchar(64) 

Is_system_themeint(1)Defaults to 0

visible_to_end_userint(1)Defaults to 0 (false)

sc_theme_to_company


Field
Type
Description

sc_theme_idint(11) 

company_idint(11) 

sc_toolbox_item


Field
Type
Description

unique_namevarchar(64) 

guest_visibilityintDefault 124

sc_toolbox_item_exclude_group


Field
Type
Description

toolbox_item_idint(11)Not null

group_namevarchar(64)Not null

toolbox_item_idint(11)Not null

company_idint(11)Not null

sc_widgets


FieldType
Description

visible_to_guestchar(1)Whether a widget is visible to guest user. Default value: ‘Y’

widget_orderint(11) 

search_engine_queue


Field
Type
Description

idint(11)Auto increment

object_idint(11) 

object_typevarchar(32) 

handling_attemptsint(11)Default: 0

company_categories_settings


Field
Type
Description

account_idvarchar(32)Not null

company_idint(11)Not null

create_datebigint 

settings_jsonvarchar(400)Not null

sysaid_forms


Field
Type
Description

form_namevarchar(255)Not null

form_xmllongtext 

configuration_flags


Field
Type
Description

config_keyvarchar(64)Not null

config_valuenumeric(1,0)Default 0

addon_license, addon_license_history


Field
Type
Description

enable_video_recordingchar(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_taskScheduled tasks in SysAid
eventFor internal use
commandsFor internal use
project_usersNot in use
url_embed_dataFor future use
predefined_services_checkPredefined monitoring services checks
predefined_network_checkPredefined monitoring port # checks
share_and_compareIT Benchmark statistics
statistics_dataIT Benchmark data
generic_messagesGreen popup messages on the bottom of the admin console
custom_servicesFor internal use
sysaid_user_push_notificationsPush notifications sent by SysAid to mobile devices
mdm_actionsMDM actions waiting to be executed
quick_listNo longer in use as of version 9.0.70



Was this article helpful?