SysAid Database Guide

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.

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

Guide Conventions

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

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

Recurring Tables

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

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

History tables

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

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

Attachments tables

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

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

Links tables

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

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

Monitor tables

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

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

Database Tables

Service Records

problem_type

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

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

service_req, service_req_history

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

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

service_req_log

Contains a summary of all changes to a service record.

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

service_req_data

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

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

service_req_merge

	Field	Type	Description
	id	int(11)	Not null, default 0
	account_id	int(11)	default 0
	merged_to	int(11)	default 0
	merged_service_records	varchar(4000)
	screen_capture	varchar(4000)

service_req_msg

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

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

service_req_files

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

service_req_links

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

service_req_video

service_req_template_ext

work_report

Contains a log of all SR activities.

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

sr_sub_type

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

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

sr_sub_tab, sr_sub_tab_history

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

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

sr_sub_tab_files

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

sr_sub_tab_links

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

sysaid_user_routing

Records out of office SR reassignment.

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

status_settings

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

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

priority_matrix_cust_values

Stores the Priority Matrix.

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

automatic_texts

Stores automatic texts for the Send Message page.

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

sub_tab_views

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

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

sr_tab_dependences

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

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

sr_sub_tab_populate

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

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

help_desk_routing

help_desk_due_date

help_desk_timers

category_keys

static_filter

Assets

asset_types

Contains the list of asset types.

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

computer, computer_history

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

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

computer_attributes, computer_attributes_history

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

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

computer_group

Defines all asset groups.

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

computer_lists

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

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

computer_users

List of computers and the users assigned to them.

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

computer_files

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

computer_links

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

computer_log

Stores entries to the asset Activity Log.

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

computer_changes

Stores additional information for entries to the asset Activity Log.

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

user2asset

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

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

software, software_history

Contains all fields on the Software form.

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

software_files

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

software_links

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

software2install_name

Records which install names are linked to which software products.

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

asset_catalog, asset_catalog_history

Contains all fields on the Catalog form.

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

asset_catalog_files

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

asset_catalog_links

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

supplier, supplier_history

Contains all fields on the Supplier form.

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

supplier_files

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

supplier_links

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

discovery_service

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

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

customized_snmp_oids

Stores entries to the list of Customized SNMP OIDs.

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

online_users

List of online users as reported by the SysAid Agent.

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

online_users_history

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

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

asset_offline_log

Records asset availability

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

online_assets

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

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

mdm_policy

Contains all MDM policies.

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

mdm_wifi_policy

Contains all Wi-Fi policies for MDM.

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

users_remote_assets

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

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

remote_active_sessions

Contains a list of all active My Desktop sessions.

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

account_attributes

Contains a list of all active My Desktop sessions.

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

asset_data_day_data

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

asset_data_week_data

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

asset_data_month_data

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

asset_data_year_data

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

patch

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

computer_patches

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

patch_policy

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

gfi_products

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

policy_gfi_products

	Field	Type	Description
	policy_id	int
	product_id	int
	approve_stat	int

patch_policy_status

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

patch_policy_event

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

credentials_list

	Field	Type	Description
	id	int
	name	varchar(256)
	user_name	varchar(256)
	password	varchar(256)
	authentication_protocol	varchar(256)	SNMP authentication protocol: MD5 SHA
	encryption_protocol	varchar(256)	SNMP encryption protocol: DES TRIPLE_DES AES_128_BIT AES_192_BIT AES_256_BIT
	encryption_key	varchar(256)	SNMP encryption key
	description	mediumtext
	validity	int
	created_by	varchar(255)
	created_date	date
	modified_by	varchar(255)
	modified_date	date

vendors_list

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

agent_settings

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

Users

sysaid_user, sysaid_user_history

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

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

sysaid_user_files

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

sysaid_user_links

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

sysaid_user_permissions

Stores permissions for users who are administrators.

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

user_groups

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

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

user2group

Lists which users are part of each group.

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

company, company_history

Contains all fields on the Company form.

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

user_settings

	Field	Type	Description
	account_id	nvarchar(32)	NOT NULL
	user_ref_id	(int11)	NOT NULL
	create_date	bigint
	settings_json	varchar(4000)	NOT NULL

account_settings

	Field	Type	Description
	account_id	nvarchar(32)	NOT NULL
	create_date	bigint
	settings_json	varchar(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
	id	int	ID# of the security question
	account_id	nvarchar(32)	SysAid account ID
	security_question	nvarchar(255)	The security question
	visible	nvarchar(1)	Whether the question is visible (Y) or not (N)
	mandatory	nvarchar(1)	Whether the question is mandatory (Y) or not (N)

user_questions

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

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

user_answer_attempts

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

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

user_settings_available_fields

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

uss_notif_events

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

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

ldap_list

	Field	Type	Description
	ldap_id	int(11)	auto_increment
	name	nvarchar(255)
	url	nvarchar(255)
	credentials_id	int(11)
	domain	nvarchar(255)
	domain_display_name	nvarchar(255)
	rds_name	nvarchar(255)
	disable_time	datetime
	enable_cashing	numeric(1,0)	default 1
	user_class_filter	varchar(400)
	user_filter	varchar(400)
	group_filter	varchar(400)
	import_groups	numeric(1,0)	default 0
	include_sub_ou	numeric(1,0)	default 0
	authentication_type	varchar(255)
	full_update	numeric(1,0)	default 0
	last_update_date	varchar(255)
	user_att_name	varchar(255)
	group_att_name	varchar(255)
	user_att_cn	varchar(255)
	user_att_dn	varchar(255)
	disable_non_exist_users	numeric(1,0)
	disable_non_exist_admins	numeric(1,0)
	last_login_user	varchar(255)
	last_login_date	datetime
	schedule
	next_run
	rerun_value
	ldap_enable	numeric(1,0)	Default:0
	save_date	datetime
	last_error	varchar(255)
	save_action	numeric(1,0)

ldap_user_dn_list

ldap_user_roots_list

	Field	Type	Description
	ldap_id	int(11)
	user_roots_id	int(11)
	user_roots

ldap_group_roots_list

	Field	Type	Description
	ldap_id	int(11)
	group_roots_id	int(11)
	group_roots

ldap_user_attribute_list

	Field	Type	Description
	ldap_id	int(11)
	user_attribute_id	int(11)
	sysaid_attribute	varchar(255)
	ldap_attribute	varchar(255)

Projects and Tasks

project, project_history

Contains all fields on the Project form.

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