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 |
Communication channel leading to ticket being created
| Origin | Comment |
|---|---|
| 0 | We just have no idea. Maybe it was too long ago |
| 1 | |
| 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.