Ticket (table: tickets)
Field Name | Data Type |
---|---|
agent_id | number |
agent_team_id | number |
auth | string (random auth code) |
category_id | number |
tickets.count_agent_replies | number |
tickets.count_user_replies | number |
creation_system | string - possible values:gateway.person (user email),gateway.agent (agent email),web.person.portal (user via portal),web.person.widget (user via embed),web.agent.portal (agent interface),web.api,web.agent (split ticket etc.) |
creation_system_option | string |
date_agent_waiting | datetime |
date_archived | datetime |
date_created | datetime |
date_feedback_rating | datetime |
date_first_agent_assign | datetime |
date_first_agent_reply | datetime |
date_last_agent_reply | datetime |
date_last_user_reply | datetime |
date_locked | datetime |
date_resolved(most recent resolution) | datetime |
date_status | datetime |
date_user_waiting | datetime |
department_id | number |
email_gateway_address_id | number |
email_gateway_id | number |
feedback_rating | number |
has_attachments | boolean |
hidden_status | string (deleted, validating, spam) |
id | number |
|
|
language_id | number |
linked_chat_id | number |
locked_by_agent | number |
notify_email | string |
notify_email_agent | string |
notify_email_name | string |
notify_email_name_agent | string |
organization_id | number |
person_email_id | number |
person_email_validating_id | number |
person_id | number |
priority_id | number |
product_id | number |
ref | string |
sent_to_address | string |
status | string:archived, awaiting_user, awaiting_agent, hidden, resolved |
subject | string |
ticket_hash | string |
total_to_first_reply | number (of seconds) |
total_to_first_reply_wh | number (of seconds) |
total_user_waiting | number (of seconds) |
urgency | number |
validating | string |
waiting_times | string |
workflow_id | number |
worst_sla_status | string |
access_codes | Ticket Access Code |
agent | Person |
agent_team | Agent Team |
attachments | Ticket Attachment |
category | Ticket Category |
charges | Ticket Charge |
custom_data | Custom Data Ticket |
custom_data[#] | Custom Data Ticket(data for ticket field with ID #) |
department | Department |
email_gateway | Email Gateway |
email_gateway_address | Email Gateway Address |
labels | Label Ticket |
language | Language |
linked_chat | Chat Conversation |
locked_by_agent | Person |
messages | Ticket Message |
organization | Organization |
participants | Ticket Participant |
person | Person |
person_email | Person Email |
person_email_validating | Person Email Validating |
priority | Ticket Priority |
product | Product |
ticket_slas | Ticket SLA |
ticket_slas[#] | Ticket SLA(ticket SLA data for SLA with ID #) |
workflow | Ticket Workflow |
* is_hold
records whether the ticket has been placed on hold. Note that a ticket that is on hold will still have status of awaiting_agent.
Snippet & Macro Use Logs (table: ticket_object_use_logs)
Field Name | Data Type |
---|---|
ticket_object_use_logs.macro.id | number |
ticket_object_use_logs.macro.title | string |
ticket_object_use_logs.macro.category.id | number |
ticket_object_use_logs.snippet.id | number |
ticket_object_use_logs.snippet.category.id | number |
ticket_object_use_logs.date_created | datetime |
ticket_object_use_logs.person | Person |
ticket_object_use_logs.ticket | Ticket |
ticket_object_use_logs.object_type | string:snippetmacro |
You need to use the OBJ_LANG function to look up a snippet category name or snippet title.
DPQL_OBJ_LANG(ticket_object_use_logs.snippet.id, 'text_snippets') AS 'Snippet Title' copy
DPQL_OBJ_LANG(ticket_object_use_logs.snippet.category.id, 'text_snippet_categories') AS 'Snippet Category' copy
Ticket Message (table: tickets_messages)
Field Name | Data Type |
---|---|
creation_system | string |
date_created | datetime |
string | |
email_source_id | number |
id | number |
ip_address | string |
hostname | string - requiresResolve User Hostname app |
is_agent_note | boolean |
message | string |
message_full | string |
message_hash | string |
message_raw | string |
person_id | number |
show_full_hint | boolean |
ticket_id | number |
visitor_id | number |
attachments | Ticket Attachment |
email_source | Email Source |
person | Person |
ticket | Ticket |
visitor | Visitor |
Ticket SLA (table: ticket_slas)
Field Name | Data Type |
---|---|
fail_date | datetime |
id | number |
is_completed | boolean |
sla_id | number |
sla_status* | string |
ticket_id | number |
warn_date | datetime |
sla | SLA |
ticket | Ticket |
Possible values of sla_status are:
ok
- SLA has been warning
fail
none
If multiple SLAs are applied to a single ticket, the worst status of any SLA is what will be returned if you query tickets.ticket_slas.sla_status
i.e. if a ticket has 3 SLAs applied, two of them in OK status but one of them has failed, the returned value will be fail
.
Resolving a ticket does not change SLA status i.e. if the SLA status becomes warning
or fail
, it will still be warning
or fail
even if the ticket is resolved.
Ticket Trigger (table: ticket_triggers)
Field Name | Data Type |
---|---|
actions | string |
date_created | datetime |
event_trigger | string |
event_trigger_options | string |
id | number |
is_enabled | boolean |
run_order | number |
sys_name | string |
terms | string |
terms_any | string |
title | string |
Ticket Priority (table: ticket_priorities)
Field Name | Data Type |
---|---|
id | number |
priority | number |
title | string |
Ticket Participant
This includes users CC’d on a ticket and agents who are followers.
Field Name | Data Type |
---|---|
access_code_id | number |
default_on | boolean |
id | number |
person_email_id | number |
person_id | number |
ticket_id | number |
access_code | Ticket Access Code |
person | Person |
person_email | Person Email |
ticket | Ticket |
Ticket Workflow (table: ticket_workflows)
Field Name | Data Type |
---|---|
display_order | number |
id | number |
title | string |
Ticket Access Code (table: ticket_access_codes)
Field Name | Data Type |
---|---|
auth | string |
id | number |
person_id | number |
ticket_id | number |
person | Person |
ticket | Ticket |
Ticket Attachment (table: ticket_attachments)
Field Name | Data Type |
---|---|
blob_id | number |
id | number |
is_agent_note | boolean |
is_inline | boolean |
message_id | number |
person_id | number |
ticket_id | number |
blob | Blob |
message | Ticket Message |
person | Person |
ticket | Ticket |
Ticket Category (table: ticket_categories)
Field Name | Data Type |
---|---|
display_order | number |
id | number |
parent_id | number |
title | string |
children | Ticket Category |
parent | Ticket Category |
Ticket Charge (table: ticket_charges)
Field Name | Data Type |
---|---|
agent_id | number |
amount | number (of helpdesk currency unit e.g. USD) |
charge_time | number (of seconds) |
custom_data[x] | comment or custom field * |
date_created | datetime |
id | number |
organization_id | number |
person_id | number |
ticket_id | number |
agent | Person |
organization | Organization |
person | Person |
ticket | Ticket |
Replace x with the ID of the custom field. You can find this out from Admin > Tickets > Time Log & Billing > Fields by clicking the small gear icon at the top right of the list.
Ticket Satisfaction (table: ticket_feedback)
Field Name | Data Type |
---|---|
date_created | datetime |
id | number |
message | string |
message_id | number |
person_id | number |
rating | number (positive = 1, neutral = 0, negative = -1) |
ticket_id | number |
person | Person |
ticket | Ticket |
ticket_message | Ticket Message |
Note that despite the name, this table relates to the user Satisfaction survey, not the Feedback section of the portal.
Ticket Log (table: tickets_logs)
This table is used to record logs of all actions applied to every ticket.
Field Name | Data Type |
---|---|
action_type* | string |
date_created | datetime |
details | string |
id | number |
id_after | number |
id_before | number |
id_object | number |
person_id | number |
sla_id | number |
sla_status | string |
ticket_id | number |
trigger_id | number |
escalation_id | number |
parent_id | number |
person | Person |
sla | SLA |
ticket | Ticket |
* action_type
describes the type of action that is logged. See the table below for possible values.
action_starter
is a special action_type
which denotes an action that caused multiple further actions to happen. Any actions it caused will have their parent_id
value set to the id
of theaction_starter
action.
For example, when a user submits a ticket through the portal, many actions result: the ticket is created, the person (user) is set, the ticket subject is set, the ticket department is set, the initial message is created etc.
When this happens, an initial action_starter
action is logged; suppose its id
value is 27.
Further actions of different types will be logged: ticket_created
, changed_person
,changed_subject
, changed_department
, message_created
etc., all with a parent_id
value of 27.
action_type | Notes |
---|---|
action_starter | Event whichcaused more actions |
app_message | |
attach_added | |
changed_agent | |
changed_agent_participants | |
changed_agent_team | |
changed_category | |
changed_custom_field | |
changed_department | |
changed_hidden_status | |
changed_labels | |
changed_language | |
changed_organization | |
changed_participants | |
changed_person | |
changed_priority | |
changed_product | |
changed_slas | |
changed_sla_status | |
changed_status | |
changed_subject | |
changed_urgency | |
changed_user_participants | |
changed_workflow | |
email_account | |
feedback_rating | |
free | Free text (added tolog by trigger) |
merged_from | |
message_created | |
message_edit | |
message_forwarded | |
message_removed | |
new_billing | |
person_email_changed | |
split_from | |
split_to | |
ticket_created | |
ticket_email | |
ticket_sla_status |
id_object
is used if the action logged refers to a particular object e.g. if a message is edited.
id_after
and id_before
are used if an object ID changes or an object is created.
For a changed_status
action, id_after
and id_before
values are used to represent the statuses with numerical codes:
Status | Code |
---|---|
Awaiting agent | 100 |
Awaiting user | 110 |
Pending | 400 |
Resolved | 200 |
Archived | 210 |
Validating | 300 |
Deleted | 310 |
Spam | 320 |
Моля, логнете се или се регистрирайте, за да оставите коментар.