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 |
|
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 |
|
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:
https://serviceaide.atlassian.net/wiki/spaces/CloudSMGoldfish/pages/2965091
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.
© 2019 Serviceaide 1-650-206-8988 http://www.serviceaide.com info@serviceaide.com