NetSupport DNA Helpdesk Database Structure

(1)

NetSupport DNA Helpdesk Database Structure

(2)

NETSUPPORT DNA HELPDESK DATABASE STRUCTURE 1 Table : HD_ADUpdates 4 Table Structure 4 Table : HD_Audit_Trail 5 Table Structure 5 Table HD_Category 5 Table Structure 5 Table HD_Comp 6 Table Structure 6 Table HD_Contact 7 Table Structure 7 Table : HD_CustomDataAssign 8 Table Structure 8 Table HD_CustomDataLists 8 Table Structure 8 Table : HD_CustomDataSections 8 Table Structure 8 Table : HD_CustomDataTabs 9 Table Structure 9 Table HD_CustomFields 9 Table Structure 9 Table HD_DashReports 10 Table Structure 10 Table HD_Dept 10 Table Structure 10 Table : HD_Email_Extract_Rules 11 Table Structure 11 Table : HD_Email_Template 11 Table Structure 11 Table HD_Es_Action 12 Table Structure 12 Table HD_Es_Filter 12

(3)

Table HD_Es_Ticket 13 Table Structure 13 Table HD_Files 14 Table Structure 14 Table : HD_Hols 14 Table Structure 14 Table : HD_Lic 14 Table Structure 14 Table HD_Notify 15 Table Structure 15 Table HD_Notify_Recip 15 Table Structure 15 Table HD_Options 16 Table Structure 16 Table HD_Permissions 16 Table Structure 16 Table HD_Pref 17 Table Structure 17 Table HD_Priority 17 Table Structure 17 Table HD_ReportFolders 18 Table Structure 18 Table HD_ReportPermissions 18 Table Structure 18 Table HD_Reports 18 Table Structure 18 Table HD_Resolution 19 Table Structure 19 Table : SavedSearchAssign 19 Table Structure 19 Table : HD_SavedSearches 19 Table Structure 19

(4)

Table : HD_Solution_Formated 20 Table Structure 20 Table : HD_SolutionFiles 21 Table Structure 21 Table HD_Status 21 Table Structure 21 Table HD_Ticket 22 Table Structure 22 Table HD_Ticket_detail 23 Table Structure 23 Table HD_TicketCustomData 23 Table Structure 23 Table HD_TicketFiles 23 Table Structure 23 Table : HD_WorkingHours 24 Table Structure 24

Table : HD_ADUpdates

Description:

This table holds the details of when each user in the Helpdesk Database was last updated by the Active Directory sync service

Table Structure

Field Name Description

NTUserName This is the NT Username that relates to a user in the HD_Contact Table

LastUpdate This is the Date and Time of the last update

DontSync This is a flag to control whether the user details are updated to match those defined in the Active Directory

(5)

Table : HD_Audit_Trail

Description:

This table holds the Audit Trail details for each Ticket

Table Structure

Table HD_Category

Description

Holds the list of Categories that helpdesk tickets can be raised against

Table Structure

TicketId This is the ID for the ticket that relates to the HD_Ticket Table TimeStamp This is the Date and Time of the event in the Audit Trail

FullName This is the Full Name of the user that initiated the event Event This is the text of the event detail

CategoryId This is the primary key and is an auto numbering integer

Parent This relates to a CategoryId in this table this enables the category list to hierarchical.

CatDesc This is a Text description of the Category

Assignee This contains the ContactId of the person would be the assignee of any tickets created by contact that belong to this Category

DeptAssignee This contains the DeptId of the Department that would be the assignee of any tickets created by contact that belong to this Category

Active If a category is deleted but it is assigned to a ticket or a solution then the category (ticket Type) is marked as inactive

0 – means that the Ticket Type is not active and is not displayed in any lists

1- means the Ticket Type is active

SortOrder This is the User Defined Sort Order based on the CategoryId DefaultPri This contains the Default Priority for tickets of this type and is used

when determining the priority for a new ticket ParentList A comma separated list of the parents of this Type

(6)

Table HD_Comp

Description

Holds the information relating to the companies that are configured in the system By Default at installation only one company will be created

Table Structure

CompId This is the primary key and is an auto numbering integer CompName This is the Name of the Company

DateCreated This is the date that this record was Created.

CompContact This contains a reference to a ContactId in Table HD_Contact that relates to the main contact person for this company

PhoneNo This contains the Main Phone number for the company PostalAddress This contains the Postal address of the company

Assignee This contains the ContactId of the person would be the assignee of any tickets created by contact that belong to this company

DeptAssignee This contains the DeptId of the Department that would be the assignee of any tickets created by contact that belong to this company

Active Default value is 1

If the Company is deleted and the company contains departments that are set to not active this is set to 0 and the company is no longer displayed in the helpdesk, if there are not department in the company then the record is deleted.

(7)

Table HD_Contact

Description

Contains Information about contacts

Table Structure

ContactId This is the primary key and is an auto numbering integer FullName This is the Fullname of the contact

DeptId This is an integer that relates to a DeptId in HD_Dept that defines the department and company that this contact belongs to.

EmailAddress This is the contacts Email Address PhoneNo This is the contacts Phone Number

CellNo This is the contacts Mobile Phone Number

TechStaff This is a Tiny Int that defines whether or not this contact is a member of Technical Staff responsible for managing Helpdesk Tickets

NTUserName This is an NT Username for integration with NT Security

Active Whether this user is a current user (ie tech staff that have left need still need to be recored on the system for historic Tickets

Set to 0 for deleted users Set to 1 for active users

Password An encypted Password

DateCreated The Date and time that this contact was created

LoggedOn This is set to 1 if the user is currently logged on to the helpdesk LastUpdate This is a time stamp of the last time the logged on user requested a

web page and is used to detect when a user has logged off the helpdesk

AllTickOpt This field only applies to Helpdesk Operators, when this is 1 the All Tickets option is available on the menu to this user.

DefaultPri This contains the Default Priority set for the user and is used to determine the priority of a new ticket

(8)

Table : HD_CustomDataAssign

Description:

This table contains the details of which custom data fields are displayed on tickets dependant on the selected contact and ticket type.

Table Structure

Table HD_CustomDataLists

Description

This table defines the list used for a combo type of custom field

Table Structure

Table : HD_CustomDataSections

Description

This table holds the definition of the sections that hold the custom data fields

Table Structure

ID This is a unquie identifier for the table

CustomFieldId This relates to the Id for a custom field definition in the HD_CustomFields Table

DeptId This is the ID of a Department for a contact that the field will be displayed for

CompId This is the ID of a Company for a contact that the field will be displayed for

ContactId This is the ID of a contact for which the custom field will be displayed for

CatId This is the ID of a Ticket Type that the field will be displayed for

ListId This is the primary key and is an auto numbering integer ListValue This is the value of the list entry

SectionId This is a unique identifier for the section

Title This is the text that is displayed in the title of the section

Saved This is a flag to say that the section is Saved an therefore displayed to users of the helpdesk, during the design phase this flag is not set SortOrder This is an integer that defines which order sections are displayed in TabId This relates to a TabId in the HD_CustomDataTabs table and

defines which tab the section is contained in. If this is null then the section is displayed on the main form of the Ticket

(9)

Table : HD_CustomDataTabs

Description

This table holds the definitions of the tabs for the custom data

Table Structure

Table HD_CustomFields

Description

Records the Customer Ticket Data created

Table Structure

TabId This is a unique identifier for the tab Title This is the text that is displayed on the tab

Saved This is a flag to say that the tab is Saved an therefore displayed to users of the helpdesk, during the design phase this flag is not set SortOrder This is an integer that defines which order tabs are displayed in

CustomFieldId This is the primary key and is an auto numbering integer CustomFieldName This is the Name of the Custom Field created

Field Type This is the Field Type of the Custom Field created such as Text Box, Tick Box,

Mandatory Whether the Custom Data is Mandatory for the Ticket

CustomListId This is the Integer relates to the ListId in HD_CustomDataList Numeric Whether the Custom Field Data will only contain Numeric data OperatorOnly This Custom data field is only displayed to Helpdesk Operators SectionId This is the Integer that relates to the SectionId in

HD_CustomDataSectio

DisplayCreate This Customer Field will be displayed on the New Ticket form Display Edit This Customer Field will be displayed on the edit Ticket form Saved When set to 0 the custom data field is only displayed in the

customer data editor,

When set to 1 the custom Data Field is available as defined by the DisplayCreate and Display Edit fields

SortOrder This is the User Defined Sort Order based on the CustomFieldId FileSize This is the maxium length of the fields data

HalfRow When set to 0 the field is displayed across the entire page Default Value This is the Default Value of the Custom Field if Specified

(10)

Table HD_DashReports

Description

Holds a list of Quick reports to be displayed on the dashboard

Table Structure

Table HD_Dept

Description

Holds the information relating to the Departments that are that are configured for each Company

Table Structure

ReportId This is the primary key and is an auto numbering integer Description A Description of the Report

SqlStatment The SQL Statement to run to produce the Table of information FormatOptions Reserved for future use

DeptId This is the primary key and is an auto numbering integer DeptName This is the name of the department

CompId This relates to a CompId in the table HD_Comp that defines the company that this department belongs to.

Manager This contains a Integer that relates to a ContactId in the table HD_Contact that defines the manager of this department. InitialAssignee This is a integer that relates to a ContactId in HD_Contact that

defines the Initial assignee for Tickets assigned to this Department TechDept This is a Tiny Int that defines whether or not this department is a

Technical department responsible for managing Helpdesk Tickets Assignee This contains the ContactId of the person would be the assignee of

any tickets created by contact that belong to this Department DeptAssignee This contains the DeptId of the Department that would be the assignee of any tickets created by contact that belong to this Department

Active Default value is 1

When a Department is deleted if it contains users that have been marked as not active this is set to 0 and the department is no longer displayed in the helpdesk.

If there are no users in the department then the record is deleted DefaultPri Reserved for future use

Parent The Parent Department

ParentList A comma separated list of the parents for this department FullDeptName The Full name of the Department Calculated from the Parents

(11)

Table : HD_Email_Extract_Rules

Description

This table holds the definition for the incoming email rules

Table Structure

Table : HD_Email_Template

Description:

This table holds the templates for all the emails that are sent by the server these can be notification or Escalation emails.

Table Structure

ExtractID This is the primary key and is an auto numbering integer

Description This is the description of the rule that is displayed in the helpdesk SourceField This is the field of the email in which to search

DestField This is the destination field

ExtractOperator This is how to extract the data from the email Data1 Value defined to extract data

Data2 Value defined to extract data

SortOrder This is an integer that defines which order the rules are processed in CaseSensitive This is a flag to indicate if the extraction rule should be case

sensitive

Enabled A flag to indicate if the rule is enabled

TemplateId This is the primary key and is an auto numbering integer FromAddress This is the Email address that the email will be sent from. Subject This is the Subject line of the email

Body This is the body of the email and may contain place holders for Variables

Description A friendly name for the template

HTML This field denotes whether the Email is to be sent in HTML format 0 means that the Email is sent as plain text

(12)

Table HD_Es_Action

Description

This table contains the list of defined Escalation Actions

Table Structure

Table HD_Es_Filter

Description

This table contains the list of defined Escalations

Table Structure

ActionId This is the primary key and is an auto numbering integer

ActionType This is an Integer that defines what action to take and will be one of 0 Increase the Priority of a Ticket

1 Send a Notification

NotifyId This is an integer that relates to an NotifyId from HD_Notify table that defines the Notification rule to use

ActionData Reserved for future use

FilterId This relates to the FilterId in the HD_Es_Filter table Description The Name of this action

FilterId This is the primary key and is an auto numbering integer FilterDesc This is a text Description of the escalation

DateCreated This is a Date and Time when the Escalation was Created

Owner This is an integer that relates to a ContactId In the HD_Contact table that defines the user that created this Escalation

Enabled 0 – this escalation will not be processed 1 – this escalation will be processed

(13)

Table HD_Es_FilterDetail

Description

This table contains the Criteria for an escalation

The combination of FieldName, Operator, Criteria should build a SQL where clause eg FieldName DueDate Operator < Criteria Getdate() DueDate<Getdate() Table Structure

Table HD_Es_Ticket

Description

Records what escalations have been performed on a ticket

Table Structure

FilterDetailId This is the primary key and is an auto numbering integer FilterId This relates to the FilterId in the HD_Es_Filter table

FieldName This is a name of field from the HD field in the HD_Ticket table that should be queried

Operatior This will be one of 0 = 1 > 2 < 3 Like etc

Criteria This is the criteria to match

ExecuteOrder This is the order in which the Criteria should be executed

JoinType Either a 0 or 1 and defines where to add a AND or OR to the end of the criteria

Description The Name of this condition

FilterId This relates to the FilterId in the HD_Es_Filter table TicketId This is an Integer that relates to a TicketId in HD_Ticket DateStamp The date and time the escalation was performed

(14)

Table HD_Files

Description

Holds the list of all Uploaded files

Table Structure

Table : HD_Hols

Description

This table holds the details of the Holidays that are defined for the Helpdesk, these holidays are used for the escalation and due date calculations

Table Structure

Table : HD_Lic

Description

This table holds the license details for the Helpdesk

Table Structure

FileId This is the primary key and is an auto numbering integer FileName This is the Name of the Uploaded File

Folder This is the folder in which the file is installed Description This is the Description of the Uploaded File OperatorOnly Reserved for future use

UploadedAt This is the Date Time when the File was Uploaded UploadedBy This is the ContactID of the Who Uploaded the File FileSize This is the File Size of the Uploaded File in Bytes

ID This is the primary key and is an auto numbering integer StartDate This is the Start Date of the holiday

EndDate This is the End Date of the holiday Description This is the text description of the holiday

CompanyName This is the Company Name defined for the license SerialNo This is the serial number defined for the license LicCode This is the authorisation code for the license

(15)

Table HD_Notify

Description

Holds a list of Notification rules

Table Structure

Table HD_Notify_Recip

Description

Defines which persons a notification is sent to

Table Structure

NotifyId This is the primary key and is an auto numbering integer Condition This is an integer that defines when this notification rule is run

1 – Ticket Created

2 – Ticket Status Changed 4 – Ticket Priority Changed 8 – Ticket reassigned 16 – Due Date Changed 32 – Ticket Updated 64 – Ticket Closed 128 – Ticket Escalated

TemplateId This is an integer that relates to an EmailTemplateId from

HD_Email_Template that defines the template to use when sending an Email

Enabled If set to 1 then this rule is processed If set to 0 then this rule is not processed Description A description of this rule

RecipId This is the primary key and is an auto numbering integer NotifyId This is an Integer that relates to a NotifyId in HD_Notify RecipType This is an integer that defines who to send the email to

1- The contact for the Ticket

2- The Contact for a tickets manager 3- The Assignee for the ticket

4- The manager of the Assignee for a ticket 5- The Creator of a ticket

6- The manager of the Creator of a ticket

7- The Default company contact for the Company that the Contact for a ticket belongs to

(16)

Table HD_Options

Description

Records what escalations have been performed on a ticket

Table Structure

Table HD_Permissions

Description

Holds the User Preferences for the default, Company, Department or Contact

Table Structure

OptionName The Name of the Option

sValue If the Option is a varchar this contains the value of the Option iValue If the Option is a Integer this contains the value of the Option sValue If the Option is a Text this contains the value of the Option

Id This is the primary key and is an auto numbering integer

Permission This is an integer that relates to one of the permission constants that defines the permission that this record grants or denies

ContactId This is the ContactId of the contact for which this record holds a permission, this may be null is this record defines a permission for a Department, Company or a Default premission.

DeptId This is the DeptId of the Department for which this record holds a permission, this may be null is this record defines a permission for a Contact, Company or a Default permission

CompId This is the CompId of the Company for which this record holds a permission, this may be null is this record defines a permission for a Contact, Department or a Default Permission

DenyPermission This is an integer if set to 1 then the Permission is denied else the permission is granted

(17)

Table HD_Pref

Description

Holds the User Preferences for the default, Company, Department or Contact

Table Structure

Table HD_Priority

Description

This table holds the details of the defined priority levels for the Helpdesk implementation

Table Structure

PrefId This is the primary key and is an auto numbering integer

ContactId This is the ContactId of the contact for which this record holds the preferences, this may be null is this record defines preferences for a Department, Company or the Default Preferences.

DeptId This is the DeptId of the Department for which this record holds the preferences, this may be null is this record defines preferences for a Contact, Company or the Default Preferences

CompId This is the CompId of the Company for which this record holds the preferences, this may be null is this record defines preferences for a Contact, Department or the Default Preferences

DateFormat This is the date format that this preference defines for the displaying and inputting of dates

StartPage This holds the name of the default startpage for a contact with this preference

TicketList This lists the fields to be displayed in the ticket list DisplayFilters 0 – the filters are not displayed on the ticket list

1 – the filters are displayed on the ticket list

ConmfirmMsg 0 – when a user closed a page without saving changes then a prompt is not displayed

1 – when a user closed a page without saving changes then a prompt is displayed

TicketlistRefesh The Time between automatic refreshes of ticket lists

PriId This is the primary key and is an auto numbering integer PriLevel This is the Level of the priority i.e. 0 is the most urgent

PriDesc This is a text description on the priority level e.g. Critical, Urgent, High etc

(18)

Table HD_ReportFolders

Description

Records what Folders exist and where the Reports are Located

Table Structure

Table HD_ReportPermissions

Description

Which Reports the Users have access to view

Table Structure

Table HD_Reports

Description

What reports are available

Table Structure

FolderId This is the primary key and is an auto numbering integer Name This is the Name of the Folder

Parent This relates to the FolderId in this table this enables the Folder list to be hierarchical.

DateStamp This is the Date Stamp of when the Folder was Created

ReportPermID This is the primary key and is an auto numbering integer ContactId This is the Integer that relates to the ContactId in HD_Contact DeptId This is the Integer that relates to the DeptId in HD_Dept CompId This is the Integer that relates to the CompId in HD_Comp

DenyAccess When set to 1 Access is explicitly denyed for the contact department or company

FolderId This is the Integer that relates to the FolderID in HD_ReportFolders

ReportId This is the primary key and is an auto numbering integer ReportFileName This is the filename of the RPT file

SQLStatment This is a SQL statement that defines the Record source for the report

(19)

Table HD_Resolution

Description

Contains the Resolution codes for tickets

Table Structure

Table : SavedSearchAssign

Description

This table stores the details of saved searches that are assigned to users

Table Structure

Table : HD_SavedSearches

Description

This table defines the details of a saved search

Table Structure

ResCodeId This is the primary key and is an auto numbering integer ResCodeDesc This is a Text description of the Resolution

SortOrder This Defines the Order that the Resolutions are displayed in Lists

SearchAssignId This is the primary key and is an auto numbering integer

SearchId This is the SearchId that relates to data in the HD_SavedSearches table

ContactId This is the Integer that relates to the ContactId in HD_Contact DeptId This is the Integer that relates to the DeptId in HD_Dept CompId This is the Integer that relates to the CompId in HD_Comp

SearchId This is the primary key and is an auto numbering integer SearchName This is the Name of the saved search

FieldList This is a comma separated list of the fields displayed in the search results

WhereClause This is the Where clause for the search

Menu This is a flag that determines if the search is displayed on the menu Owner This relates to a ContactId in the HD_Contact table and is the user

(20)

Table : HD_SavedSearchItems

Description

This table is used to store the Criteria for a saved search

Table Structure

Table HD_Solution

Description

Holds solutions to Problems

Table Structure

Table : HD_Solution_Formated

Description

This table contains the HTML Text of the solution

Table Structure

SearchCriteriaId This is the primary key and is an auto numbering integer

SearchId This is the SearchId that relates to data in the HD_SavedSearches table

OptionValue Value of the search Criteria

OptionText The text displayed in the User interface for this item

SortOrder This is an integer that defines which order search criteria items are displayed in

SolutionId This is the primary key and is an auto numbering integer

CategoryId This relates to a CategoryId in the table HD_Category table that defines the category of this problem

SolutionText A Text description of the solution

Creator This hold the ContactId of the Contact that Created this solution DateCreated This holds the date and time that this solution was created

Active If the Solution is deleted but is is associated with a ticket the solution is marked as inactive and is not displayed in any lists.

0 – means that this solution is deleted 1 – means the Solution is active

Published A Flag to indicate if this solution is published PublishDate The Date and time that the solution was published PublishedBy The Full name of the user that published the solution

(21)

Table : HD_SolutionFiles

Description

This table defines the files that are attached to a solution

Table Structure

Table HD_Status

Description

Holds the list of statuses that a Helpdesk Ticket can be assigned

Table Structure

SolutionId This relates to the SolutionId in the HD_Solution Table FileId This is the Integer that relates to the FileId in HD_Files

StatusId This is the primary key and is an auto numbering integer StatusDesc This is a Text description of the Status

SortOrder This Defines the Order that the statuses are displayed in Lists Closed 0 – this status is not a closed status

(22)

Table HD_Ticket

Description

This table contains the information about each Ticket

Table Structure

TicketId This is the primary key and is an auto numbering integer and is used as the Ticket ID

DateCreated This is the date and Time that the Ticket was Created DateClosed This is the date and time that the Ticket was Closed

DueDate This is the date that the Contact that logged the ticket entered as the due date.

LastModified This is the date and time that this Ticket was last saved or Updated. StatusChange This is the date and time that the Ticket last had a change of status. PriChange This is the date and time that the Ticket last had a change of Priority ContactId This is an Integer that relates to a ContactId in HD_Contact that is

the contact for the Ticket

PriId This is an Integer that relates to a PriId in the HD_Priority Table that defines the priority of this problem.

StatusId This is an integer that relates to a StatusId in the HD_Status Table that Defines the Current Status of the ticket.

Assignee This is an integer that relates to a ContactId in the HD_Contact Table that defines the contact that this ticket is assigned to Creator This is an integer that relates to a ContactId in the HD_Contact

Table that defines the contact that Created this Ticket.

DeviceId The ID from the DNA PC table of the device that this ticket relates to Solution This is an Integer that relates to a SolutionId in the table

HD_Solution that defines the solution for this Ticket

CategoryId This relates to a CategoryId in the table HD_Category table that defines the category of this problem

ArchiveDate This is the date that this Ticket was Archived.

Description This is the original description of the problem as entered when creating the Ticket

Summary This is the Summary field on a ticket

ResCode This Relates to a ResCodeId in the HD_Resolutions Table and defines the resolution code for a closed ticket

IPAddress This is the IP Address used for Remote Control when there is no DNA Integration set

(23)

Table HD_Ticket_detail

Description

Hold the detail of the Helpdesk Ticket

Table Structure

Table HD_TicketCustomData

Description

Records what Custom Ticket Data based on TicketId

Table Structure

Table HD_TicketFiles

Description

Holds the list of Uploaded files based on TicketId

Table Structure

DetailId This is the primary key and is an auto numbering integer TicketId This is an Integer that relates to a TicketId in HD_Ticket

DetailType This is an integer that defines the Type of Detail record and should be one of

0 Web Update by Tech Staff 1 Web Update by Contact

2.Telephone Update from Tech Staff 3 Telephone Update by Contact 4 Email Update form Tech Staff 5 Email Update from Contact

DetailText This is a text field that contains the detail

TimeStamp This is the date and time that this Detail was added

Updater This is an integer that relates to a contacted in HD_Contact that defines the person adding this update

TimeTaken This is the Amount of time entered when then note was added to the ticket.

TicketId This is the Integer that relates to the TicketId in HD_Ticket CustomFieldId This is the primary key and is an auto numbering integer TextValue This is the value of the field if the field is a textarea

(24)

Table : HD_WorkingHours

Description

This table contains the details of the defined working ours for the helpdesk

Table Structure

Dayofweek An integer defining the day of the week StartTime The working hours start time