Database Model Document for Ticket

Introduction

Intelligent Service Management (ISM) contains Sample workflow processes for different ticket types such as Incident management and Service Request management. It provides a simplified interface to record requests for assistance and information.

This application can be used to:

Access the knowledge base and look up suitable solutions for your IT support needs.
Log user requests, modify the request, track time that the analysts spent on each activity.
Set up Workflow Actions to automate processes.
Set up SLA Targets.
Manage and control the actions that are visible and available to different sets of users.
Monitor and manage IT systems and services using features such as searches, reports, charts, and dashboards.
Manage IT assets such as servers, laptops, virtual machines, operating systems and other assets that are deployed on the network.
Automate routine and complex IT tasks such as send email notifications.

This document describes how a Ticket information is stored in the Data base so that Customers can generate their own reports with ease.

It also explains the Best Practices and Guidelines for creation of the Reports. It is recommended to follow these practices and guidelines while executing a query, generation of reports.

ISM Data base consists of many Tables and Views. However, there are 19 main Tables / Views based on which Customers can generate reports related to Tickets.

S.No.	View / Table	Description
1.	VAPP_ITEM / DM_VAPP_ITEM	Displays the details related to common ticket details.
2.	VAPP_INCIDENT / DM_VAPP_INCIDENT	Displays the details relating to Incident Tickets
3.	VAPP_SERVICE _REQUEST / DM_VAPP_SERVICE_REQUEST	Displays the details relating to Service Request Tickets
4.	VAPP_TASK / DM_VAPP_TASK	Displays the details relating to Task Tickets
5.	VAPP_PROBLEM / DM_VAPP_PROBLEM	Displays the details relating to problem tickets
6.	VAPP_CHANGE / DM_VAPP_CHANGE	Display the details related to change tickets
7.	VAPP_ITEM_ATTRIBUTES	Displays a list of service desk ticket attributes which are an extension of the default attributes
8.	VAPP_INCIDENT_ATTRIBUTES_PIVOT / DM_VAPP_INCIDENT_ATTRIBUTES_PIVOT	Returns the custom attributes for incident tickets.
9.	VAPP_TICKET_ATTRIBUTES_PIVOT / DM_VAPP_TICKET_ATTRIBUTES_PIVOT	Returns the custom attributes common for all tickets
10.	VAPP_SERVICE_REQUEST_ATTRIBUTES_PIVOT / DM_VAPP_SERVICE_REQUEST_ATTRIBUTES_PIVOT	Returns the custom attributes for service requests
11.	VAPP_TASK_ATTRIBUTES_PIVOT / DM_VAPP_TASK_ATTRIBUTES_PIVOT	Returns the custom attributes for task tickets
12.	VAPP_PROBLEM_ATTRIBUTES_PIVOT / DM_VAPP_PROBLEM_ATTRIBUTES_PIVOT	Returns the custom attributes for problem tickets
13.	VAPP_CHANGE_ATTRIBUTES_PIVOT / DM_VAPP_CHANGE_ATTRIBUTES_PIVOT	Returns the custom attributes for change requests
14.	VORG_CONTACTS / DM_VORG_CONTACTS	Users Information
15.	VSLA_AGREEMENT_COMPLIANCE / DM_VSLA_AGREEMENT_COMPLIANCE	To Store SLA agreement
16.	AM_ASSET_TICKET	Displays the tickets that are associated to the assets
17.	SCR_CI_ITEM_REL	Stores the actual relationships between the configuration items and tickets
18.	VAM_ASSET	Primary information of the Assets
19.	VSCR_CI	Displays the primary details for each configuration item

VAPP_ITEM Table is the base table for all the ticketing information. Whenever it is required to join VAPP_ITEM with any table, Slice ID and ROW_ID of VAPP_ITEM should be joined with ITEM_ID column of respective Join table.

ER Diagram of the above tables is depicted below

Ticket Information

Service Level Info

UI Field Name

View Name

Column Name

Remarks

Join Key

SLA Status

VAPP_ITEM

sla_compliance_status_indicator

Breached SLA, SLA Not Applied, Within SLA

Summary

UI Field Name	View Name	Column Name,	Remarks	Join Key
Opened	VAPP_ITEM	Created_date,created_by_last_name,created_by_name, created_by_group_name	Date in milliseconds when the ticket is created,
Updated	VAPP_ITEM	Modified_date, modified_by_last_name, modified_by_name, modified_by_group_name
Owner	VAPP_ITEM	assigned_to_last_name, assigned_to_name, assigned_to_group_name
Phase	VAPP_ITEM	TICKET_PHASE	Acceptance Assigned to Analyst Assigned to Group Categorization Closure Diagnosis Fulfilment Initial Diagnosis Investigation opening Raise Change Submitted Task
Status	VAPP_ITEM	TICKET_STATUS	Active Closed New Queued
Reason Code	VAPP_ITEM	TICKET_REASON_CODE	Assigned to Analyst Assigned to Group Assignment Auto Closed after 5 days Closed on Creation In Progress Incident Created new New Change None Pending Assignment Under Support
Total Time	VAPP_ITEM	work_time_spent	Total time Spent on Ticket

Ticket Information

This section describes how a ‘Service Request’ data can be retrieved from underlying Views.

Requester

UI Field Name	View Name	Column Name	Remarks	Join Key
Name	VAPP_ITEM	person1_first_name, person1_last_name	Last name, First name
Phone	VAPP_ITEM	person1_phone1	Phone number of contact
Email	VAPP_ITEM	person1_e_mail	Email address of the contact
Organization	VAPP_ITEM	Person1_org_name	Organization Name
Organization Path	VAPP_ITEM	person1_hierarchical_path	Organization Hierarchical Path

Requested For

At times, Analyst can capture the Ticket details for Customers. In such cases, requested for section will be filled with details of the customer for whom the details are captured.

For Self service requests, Requested For details will automatically be populated from Requester info.

UI Field Name	View Name	Column Name	Remarks	Join Key
Name	VAPP_ITEM	Person2_first_name, Person2_last_name	Last name, First name
Phone	VAPP_ITEM	Person2_phone1	Phone number of contact
Email	VAPP_ITEM	Person2_e_mail	Email address of the contact
Organization	VAPP_ITEM	Person2_org_name	Organization Name
Organization Path	VAPP_ITEM	Person2_hierarchical_path	Organization Hierarchical Path

About

UI Field Name	View Name	Column Name	Remarks	Join Key
Description	VAPP_ITEM	TICKET_DESCRIPTION	Ticket Description
Details	VAPP_ITEM	ticket_details	Description for the ticket
Affected Service	VAPP_ITEM	affected_ci_name
Categorization	VAPP_ITEM	CCTI_CLASS>> CCTI_CATEGORY>> CCTI_TYPE>> CCTI_ITEM	The value of the CLASS field The value of the CATEGORY field The value of the TYPE field The value of the ITEM field

UI Field Name	View Name	Column Name	Remarks	Join Key
Source	VAPP_ITEM	ticket_source	The enumeration code for the ticket source i.e. from where the ticket originated.
Impact	VAPP_ITEM	ticket_impact	The enumeration code for the ticket impact.
Urgency	VAPP_ITEM	ticket_urgency	The enumeration code for the ticket urgency.
Priority	VAPP_ITEM	ticket_priority	The enumeration code for the ticket priority.

Worklog

UI Field Name	View Name	Column Name	Remarks	Join Key
Worklog type	VAPP_WORK_LOG	work_type	Client Note First Contact Resolved Update Work	VAPP_WORK_LOG.SLICE=VAPP_ITEM.SLICE AND VAPP_WORK_LOG.ITEM_ID=VAPP_ITEM.ROW_ID
Worklog	VAPP_WORK_LOG	work_description	The description of the work done
Time Spent	VAPP_WORK_LOG	work_time_spent	The time spent in completing the work
Client Viewable	VAPP_WORK_LOG	work_view_type	Worklog Type

Additional Information

Additional Custom Attributes can be attached to a Ticket under additional information section. This information will be stored in following tables:

· VAPP_INCIDENT_ATTRIBUTES_PIVOT (Ticket type: Incident)

· VAPP_SERVICE_REQUEST_ATTRIBUTES_PIVOT (Ticket type: Service Request)

· VAPP_TASK_ATTRIBUTES_PIVOT (Ticket type: Task)

· VAPP_PROBLEM_ATTRIBUTES_PIVOT (Ticket type: Problem)

· VAPP_CHANGE_ATTRIBUTES_PIVOT (Ticket type: Change)

Custom attributes synching for reports

VAPP_TICKET_ATTRIBUTES_PIVOT: This is an important Pivot View which returns the custom attributes common for all tickets. Maximum 99 attributes can be defined.

Example:

Sometimes, more fields are needed to capture information about a ticket. Attributes are the fields that are used with configuration items and ticket forms to store more information.

In this example, City, Country, State Custom attributes are defined. This data has been stored in VAPP_SERVICE_REQUEST_ATTRIBUTES_PIVOT table under city, country, state fields.

For more information on to how to create Custom attributes, please refer below link:

Create and Manage Attributes

Solutions

For Service Request

UI Field Name	View Name	Column Name	Remarks	Join Key
Resolved using	VAPP_SERVICE_REQUEST	TICKET_SOLUTION_ID		IC_SOLUTIONS.SLICE=VAPP_SERVICE_REQUEST.SLICE AND IC_SOLUTIONS.ROW_ID=VAPP_SERVICE_REQUIEST.TICKET_SOLUTION_ID
Cause	VAPP_SERVICE_REQUEST	CAUSE
Resolution	VAPP_SERVICE_REQUEST	RESOLUTION

For Incident Ticket

UI Field Name	View Name	Column Name	Remarks	Join Key
Resolved using	VAPP_INCIDENT	TICKET_SOLUTION_ID		IC_SOLUTIONS.SLICE= VAPP_INCIDENT.SLICE AND IC_SOLUTIONS.ROW_ID= VAPP_INCIDENT.TICKET_SOLUTION_ID
Cause	VAPP_INCIDENT	CAUSE	The cause for requesting the service
Resolution	VAPP_INCIDENT	RESOLUTION	The resolution for resolving the requested service

For Problem

UI Field Name	View Name	Column Name	Remarks	Join Key
Resolved using	VAPP_PROBLEM	TICKET_SOLUTION_ID		IC_SOLUTIONS.SLICE= VAPP_PROBLEM.SLICE AND IC_SOLUTIONS.ROW_ID= VAPP_PROBLEM.TICKET_SOLUTION_ID
Cause	VAPP_PROBLEM	CAUSE	The cause for requesting the Problem
Resolution	VAPP_PROBLEM	RESOLUTION	The resolution for resolving the requested Problem

Approval

UI Field Name	View Name	Column Name	Remarks	Join Key
Phase	VAPP_HISTORY_APPROVALS	APPR_PHASE		VAPP_HISTORY_APPROVALS.SLICE=VAPP_ITEM.SLICE AND VAPP_HISTORY_APPROVALS.ITEM_ID=VAPP_ITEM.ROW_ID
Approver Type	VAPP_ITEM_APPROVERS	appr_role		VAPP_ITEM_APPROVERS.SLICE=VAPP_ITEM.SLICE AND VAPP_ITEM_APPROVERS.appr_item_id=VAPP_ITEM.ROW_ID
Status	VAPP_HISTORY_APPROVALS	APPR_STATUS
Action on approval	VAPP_ITEM_APPROVER_COMMENTS	COMMENT_TEXT	The review decisions, observations, suggestions, or additional information requirements regarding the submitted change
Action on rejection	VAPP_ITEM_APPROVER_COMMENTS	COMMENT_TEXT	The review decisions, observations, suggestions, or additional information requirements regarding the submitted change
In approval state ?	VAPP_ITEM_APPROVER_COMMENTS	COMMENT_TYPE	0 – Approved 1- Rejected
Approver Type	VAPP_ITEM_APPROVERS	appr_role
Name	VAPP_ITEM_APPROVERS	last_name. first_name
Group	VAPP_ITEM_APPROVERS	group_name
Decision	VAPP_ITEM_APPROVER_COMMENTS	decision
Decision date	VAPP_ITEM_APPROVER_COMMENTS	decision_date
Comments	VAPP_ITEM_APPROVER_COMMENT_LIST	COMMENT_TEXT		VAPP_ITEM_APPROVER_COMMENT_LIST.SLICE=VAPP_ITEM.SLICE AND VAPP_ITEM_APPROVER_COMMENT_LIST.APPR_ITEM_ID=VAPP_ITEM.ROW_ID

Related Assets / CIs

CI- Ticket Relationship

UI Field Name	View Name	Column Name	Remarks	Join Key
Name	VSCR_CI_ITEM_REL	ci_name	The configuration item name
Identifier	VSCR_CI_ITEM_REL	ci_identifier	The configuration item identifier
Function	VSCR_CI_ITEM_REL	ci_function	The configuration item function descriptor
Description
Status	VSCR_CI_ITEM_REL	ci_status	The current status of the configuration item
Impact	VSCR_CI_ITEM_REL	ci_impact
Categorization	VSCR_CI_ITEM_REL	ccti_class
Organizaion	VSCR_CI_ITEM_REL	ci_org_name
Owner	VSCR_CI_ITEM_REL	Ci_contact_name

Service Level Activity

SLA Compliance

UI Field Name	View Name	Column Name	Remarks	Join Key
SLA Name	VSLA_AGREEMENT_COMPLIANCE_LIST_UX	agreement_name
Assigned Group	VSLA_AGREEMENT_COMPLIANCE_LIST_UX	assigned_to_group
Started	VSLA_AGREEMENT_COMPLIANCE_LIST_UX	Sla_start_time
Due by	VSLA_AGREEMENT_COMPLIANCE_LIST_UX	sla_due_by	The due date determined by the value of the SLA Threshold (time commitment) defined in the applicable service level agreement i.e. SLA Target configuration and the starting date timer. The timers are based on the business operating scheduler (working-hour) calendars.
Updated	VSLA_AGREEMENT_COMPLIANCE_LIST_UX	Modified_Date
Threshold	VSLA_AGREEMENT_COMPLIANCE_LIST_UX	Threshold_value	The service metric threshold value to be monitored
Measured	VSLA_AGREEMENT_COMPLIANCE_LIST_UX	target_value_hhmmss
Compliance Notes	VSLA_AGREEMENT_COMPLIANCE_LIST_UX	compliance_notes

Activity History

Discussion → Add Work Log

UI Field Name	View Name	Column Name	Remarks	Join Key
Work Log Type	VAPP_WORK_LOG	WORK _TYPE
Work Log	VAPP_WORK_LOG	WORK_DESCRIPTION
Time Spent	VAPP_WORK_LOG	WORK_TIME_SPENT

Ticket Type: Incident

Ticket Information -> Global Issue

UI Field Name	View Name	Column Name	Remarks	Join Key
IssueType	VAPP_INCIDENT	THIS_TICKET_IS	Gets set if the ticket is global
Global Issue ID	VAPP_INCIDENT	Related_to_global_issue_id	Related Global Issue ID

Ticket Type : Change Request

About / Additional Information

UI Field Name	View Name	Column Name	Remarks	Join Key
Change Type	VAPP_CHANGE	CHANGE_SUBTYPE	Standard, Break-Fix, Normal, Emergency
Change Classification	VAPP_CHANGE	CHANGE_TYPE	Major,Minor, Significant

UI Field Name	View Name	Column Name	Remarks	Join Key
Article title	VSCR_CI	ci_name	The configuration item name
Symptom	VSCR_CI	ci_description
Function	VSCR_CI	ci_function	The configuration item function descriptor
Identifier	VSCR_CI	ci_identifier	Configuration item identifier
Status	VSCR_CI	ci_status	The current status of the configuration item such as Active New
Class	VSCR_CI	ccti_class

UI Field Name	View Name	Column Name	Remarks	Join Key
Name	VSCR_CI	ci_name
Description	VSCR_CI	ci_description
Function	VSCR_CI	ci_function
Identifier	VSCR_CI	ci_identifier
Status	VSCR_CI	ci_status
Class	VSCR_CI	ccti_class
Category	VSCR_CI	ccti_category

Ticket Type : KB Article

UI Field Name	View Name	Column Name	Remarks	Join Key
Article title	VIC_SOLUTIONS
Symptom	VIC_SOLUTIONS	sol_symptom
Expires on	VIC_SOLUTIONS	Sol_expiry_date
Article Owner	VIC_SOLUTIONS	sol_owner_last_name, sol_owner_first_name
Compliance owner	VIC_SOLUTIONS	compliance_owner_last_name, compliance_owner_first_name
Article category	VIC_SOLUTIONS	sol_category	Categorization FAQ Outage
Set as sticky	VIC_SOLUTIONS	sol_is_stikcy

Best Practices / Guidelines

Do’s

Views on Tables and Pivot Tables have been created for Custom reports and these are to be used for generating your own reports
Slice ID should be used as filter in queries or reports for optimal performance
VAPP_ITEM Table is the base table for all the ticketing information. Whenever it is require to join VAPP_ITEM with any table, Slice ID and ROW_ID of VAPP_ITEM should be joined with ITEM_ID column of respective Join table.
Always use Slice ID in joins
Derived tables should be used with caution and only used when necessary.
Whenever SQL functions are used, these will give new names to newly generated columns by default. However, appropriate names can be given
Always use table aliases wherever SQL statement involves more than one source.
Use DM_ tables for all reporting needs (if they are available in Jasper domain or VPN Database)
Create joins with INNER JOIN not with WHERE clause
Create calculated fields at domain level wherever possible. For more information on this, please refer article

Don’ts

Do not use SELECT * in your queries, Use only required columns in the derived tables or reports, instead of all columns.
Do not use column numbers in the ORDER BY clause.
Avoid extensive sub queries, use not more than 2 level sub queries or nested queries.
Avoid using multiple and IF and Else conditions.
Avoid SELECT DISTINCT usage, instead use SELECT DISTINCT, select more fields to create unique results.