Skip to main content

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
is_hold boolean (on hold = 1)* DEPRECIATED
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_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'
DPQL_OBJ_LANG(ticket_object_use_logs.snippet.category.id, 'text_snippet_categories') AS 'Snippet Category'

Ticket Message (table: tickets_messages)

Field Name Data Type
creation_system string
date_created datetime
email 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_createdchanged_person,changed_subjectchanged_departmentmessage_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
Authors list

First published: 16/03/2017

Last updated: Aug 19, 2019 by James Godwin