• Share
    • Twitter
    • LinkedIn
    • Facebook
    • Email
  • Feedback
  • Edit
Show / Hide Table of Contents

ticket Table (262)

• 11 minutes to read
 • 11 minutes to read

This table contains the tickets (requests) of the system. Its purpose should be evident.

Fields

Name Description Type Null
id The primary key (auto-incremented) PK
connect_id If a ticket is connected to another ticket, this field is set to the id of the 'master' ticket. FK ticket
title The title of the ticket. String(255)
created_at When the ticket was created. DateTime
last_changed The last time the ticket was modified. DateTime ●
activate When the ticket should be activated, if it is postponed. DateTime ●
closed_at When the ticket was closed. DateTime ●
created_by The id of the user who created the ticket. 1 (system user) if the ticket was created externally FK ejuser
author A string representing the author of the ticket (same as author of first message). String(255) ●
owned_by The id of the user who owns the ticket. 1 (system user) if the ticket is unassigned. FK ejuser
category The id of the category a ticket is in. FK ej_category
orig_category The id of the category the ticket was posted to. FK ej_category ●
slevel The securitylevel of the ticket. Enum TicketSecurityLevel ●
status The status of the ticket. I.e. active/closed/postponed/deleted Enum TicketBaseStatus
ticket_status User defined ticket status FK ticket_status
cust_id The reference to the customer. NULL or -1 if ticket is not connected to customer. FK person
priority The reference to the ticket_priority table. FK ticket_priority
orig_priority The reference to the initial priority for the ticket. FK ticket_priority ●
alert_level The alert level for the ticket. Matches the level value of the ticket_alert table. Short ●
alert_timeout The datetime for when the ticket should jump to the next alert_level. DateTime ●
alert_stop If the esclatation was stopped, this fields indicates how many seconds left before the next escalation level. -1 if escalation is running. Int
read_by_owner The datetime for when the ticket last was read by the owner. DateTime ●
first_read_by_owner The datetime for when the ticket first was read by the current owner. DateTime ●
first_read_by_user The datetime for when the ticket first was read by a user. DateTime ●
read_by_customer The datetime for when the ticket was read by the customer. DateTime ●
filter_id The reference to the mailbox from which the ticket was created. NULL or -1 if the ticket was not created by a mailbox. FK mail_in_filter
display_filter The name of the mailbox from which the ticket was created. Only for displaypurposes String(255) ●
replied_at The datetime for when the ticket was replied to. I.e. the first external message added to the ticket. DateTime ●
time_to_reply The time (minutes) between when the ticket was created and when it was replied to. Calculated based on priority's timeframe. Int ●
time_to_close The time (minutes) between when the ticket was created and when it was closed. Calculated based on priority's timeframe. Int ●
real_time_to_reply Same as time_to_reply, but not calculated based on priority. Int ●
real_time_to_close Same as time_to_close, but not calculated based on priority. Int ●
read_status Whether the owner has read the ticket or not (red, yellow, green). Enum TicketReadStatus
has_attachment Boolean indicating if this ticket has one or more attachments. Bool ●
deadline Deadline for ticket. DateTime ●
filter_address Address of receiving filter (mail box) String(255) ●
dbi_agent_id Integration agent (eJournal) FK dbi_agent
dbi_key The primary key for the integrated entry in the external datasource. String(255)
dbi_last_syncronized Last external synchronization. DateTime
dbi_last_modified When the entry was last modified. DateTime
origin What is the origin of this ticket Enum TicketOrigin ●
time_spent_internally The total time (seconds) within the priority's office hours the ticket has been in an open status (configurable), not including current state Int ●
time_spent_externally The total time (seconds) within the priority's office hours the ticket has been in a external waiting status (configurable), not including current state Int ●
time_spent_queue The total time (seconds) within the priority's office hours the ticket has been in a queue status, not including current state Int ●
real_time_spent_internally The total time (seconds) within 24x7 the ticket has been in an open status (configurable), not including current state Int ●
real_time_spent_externally The total time (seconds) within 24x7 the ticket has been in a external waiting status (configurable), not including current state Int ●
real_time_spent_queue The total time (seconds) within 24x7 hours the ticket has been in a queue status, not including current state Int ●
time_spent_calculated When the time_spent value was last calculated. DateTime ●
num_replies The number of replies (messages) to the customer for this request. Int ●
num_messages The total number of messages for this request. Int ●
ticket_type Ticket type FK ticket_type ●
from_address The from-address used when this ticket got created, e.g. by email String(4000) ●
tags Array of references to the Tags records FKArray ●
contact_id The company of the person in the cust_id field, if that person belongs to a company FK contact ●
language The language of the first external message String(10) ●
sentiment The sentiment index of the last external message Int ●
sentimentConfidence The sentiment confidence of the last external message Int ●
form_submission_id If this ticket was created from a form submission, this field will point to that record FK form_submission ●
suggestedCategory_id Suggestion for categorization, based on the text of the message (AI) FK ej_category
origHumanCategory_id Will contain the category id selected by the user, when having the choice of using the suggested category or manually selecting a category FK ej_category

ticket table relationship diagram

Communication channel leading to ticket being created

Origin Comment
0 We just have no idea. Maybe it was too long ago
1 Email
2 SMS
3 Telefacsimile
4 Phone call to human operator
5 Facebook wall
6 Tweet tweet
7 Internal, by human operator
8 Direct by customer through Customer Centre web pages
9 Auto-generated from e-marketing link
10 Automatic processes in Service

Status of a ticket / request

This is the internal value. The user-defined ticket status is saved to ticket.ticket_status

Status Comment
0 Unknown / uninitialized
1 Request is currently active
2 Request has been closed
3 Request has been postponed
4 Deleted
5 Request has been merged with another request. See connect_id

Indexes

Fields Types Description
id PK Clustered, Unique
connect_id FK Index
created_at DateTime Index
created_by FK Index
owned_by FK Index
category FK Index
orig_category FK Index
status Enum Index
ticket_status FK Index
cust_id FK Index
priority FK Index
orig_priority FK Index
alert_timeout DateTime Index
filter_id FK Index
read_status Enum Index
dbi_agent_id FK Index
dbi_key String(255) Index
dbi_last_syncronized DateTime Index
dbi_last_modified DateTime Index
ticket_type FK Index
tags FKArray Full text

Relationships

Table Description
chat_session This table contains chat sessions.
contact Companies and Organizations. This table features a special record containing information about the contact that owns the database.
dbi_agent DBI agent settings
ej_category This table contains categories, in which tickets are categorized. The categories are organized in a hierarchial manner.
ej_message This table contains the messages listed under tickets.
ejuser This table contains entries for the users of the system.
form_submission A form submission
hotlist The table is used by the hotlist to store the relationship between tickets and users
mail_in_filter This table contains entries for the mailboxes the eJournal system is fetching mail from (POP3 or IMAP).
notify This table contains the pop-up messages displayed for users for various events, such as 'new ticket', etc.
outbox Outgoing emails with sending status and other info
person Persons in a company or an organizations. All associates have a corresponding person record
s_shipment_addr Addresses that are ready to be sent in a shipment.
Tags MDO List of tags for Service entities
ticket This table contains the tickets (requests) of the system. Its purpose should be evident.
ticket_customers This table allows several customers to be connected to several tickets (many-to-many)
ticket_log This table contains log entries for the tickets.
ticket_log_action This table contains actions for the tickets.
ticket_log_change This table contains log entries for the tickets.
ticket_priority This table contains the ticket priorities.
ticket_relation Contains ticket relations, used to drive processing logic
ticket_status This table user defined ticket status values.
ticket_status_history This table contains the history of a tickets statuses. Each time a ticket changes status a copy of the previous status of the record will be saved
ticket_type Contains ticket types, used to drive processing logic together with ticket relations

Replication Flags

  • None

Security Flags

  • Sentry controls access to items in this table using user's Role and data rights matrix.
© SuperOffice. All rights reserved.
SuperOffice |  Community |  Release Notes |  Privacy |  Site feedback |  Search Docs |  About Docs |  Back to top