turbot/zendesk

steampipe plugin install zendesksteampipe plugin install zendesk

Table: zendesk_ticket

Work with tickets in Zendesk.

Examples

List open tickets

select
id,
created_at,
assignee_id,
organization_id,
subject
from
zendesk_ticket
where
status = 'open';

Ticket status summary

select
status,
count(*)
from
zendesk_ticket
group by
status
order by
count desc;

Stale tickets

Unsolved tickets that haven't had any update for 7 days.

select
id,
status,
updated_at,
subject
from
zendesk_ticket
where
updated_at < current_date - interval '7 days'
and
status in ('open', 'pending', 'hold');

Tickets assigned to Jane

select
u.name,
t.status,
t.subject
from
zendesk_ticket as t,
zendesk_user as u
where
t.assignee_id = u.id
and
u.name ilike '%jane%';

Ticket aging reports

All unsolved tickets, sorted by age in days.

select
date_part('day', now() - t.created_at) as age,
t.id,
t.status,
u.name as agent,
o.name as organization,
substring(t.subject for 40) as ticket
from
zendesk_ticket as t,
zendesk_user as u,
zendesk_organization as o
where
t.assignee_id = u.id
and
t.organization_id = o.id
and
t.status in ('open', 'pending', 'hold')
order by
t.id asc;

Summary of ticket age, used for daily snapshots of progress.

with aging as (
select
id,
created_at,
status,
subject,
date_part('day', now() - created_at) as age
from
zendesk.zendesk_ticket
where
status in ('open', 'pending', 'hold')
order by
id asc
),
stats as (
select
status,
sum(age)
from
aging
group by
status
)
select
current_date as date,
(
select
sum
from
stats
where
status = 'open'
) as open,
(
select
sum
from
stats
where
status = 'pending'
) as pending,
(
select
sum
from
stats
where
status = 'hold'
) as hold,
(
select
sum(sum)
from
stats
) as total;

.inspect zendesk_ticket

Tickets are the means through which your end users (customers) communicate with agents in Zendesk Support. Tickets can originate from a number of channels, including email, Help Center, chat, phone call, Twitter, Facebook, or the API. All tickets have a core set of properties.

NameTypeDescription
allow_attachmentsbooleanPermission for agents to add add attachments to a comment. Defaults to true
allow_channelbackbooleanIs false if channelback is disabled, true otherwise. Only applicable for channels framework ticket
assignee_idbigintThe agent currently assigned to the ticket
brand_idbigintEnterprise only. The id of the brand this ticket is associated with
collaborator_idsjsonbThe ids of users currently CC'ed on the ticket
created_attimestamp without time zoneWhen this record was created
custom_fieldsjsonbCustom fields for the ticket.
descriptiontextRead-only first comment on the ticket. When creating a ticket, use comment to set the description.
due_attimestamp without time zoneIf this is a ticket of type "task" it has a due date. Due date format uses ISO 8601 format.
email_cc_idsjsonbThe ids of agents or end users currently CC'ed on the ticket.
external_idbigintAn id you can use to link Zendesk Support tickets to local records
follower_idsjsonbThe ids of agents currently following the ticket.
followup_idsjsonbThe ids of the followups created from this ticket. Ids are only visible once the ticket is closed
forum_topic_idbigintThe topic in the Zendesk Web portal this ticket originated from, if any. The Web portal is deprecated
group_idbigintThe group this ticket is assigned to
has_incidentsbooleanIs true if a ticket is a problem type and has one or more incidents linked to it. Otherwise, the value is false.
idbigintAutomatically assigned when the ticket is created
is_publicbooleanIs true if any comments are public, false otherwise
macro_idsjsonbPOST requests only. List of macro IDs to be recorded in the ticket audit
organization_idbigintThe organization of the requester. You can only specify the ID of an organization associated with the requester.
prioritytextThe urgency with which the ticket should be addressed. Allowed values are "urgent", "high", "normal", or "low".
problem_idbigintFor tickets of type "incident", the ID of the problem the incident is linked to
raw_subjecttextThe dynamic content placeholder, if present, or the "subject" value, if not.
recipienttextThe original recipient e-mail address of the ticket
requester_idbigintThe user who requested this ticket
satisfaction_rating_commenttextThe comment received with this rating, if available
satisfaction_rating_idbigintUnique identifier for the satisfaction rating on this ticket
satisfaction_rating_scoretextThe rating "offered", "unoffered", "good" or "bad"
sharing_agreement_idsjsonbThe ids of the sharing agreements used for this ticket
statustextThe state of the ticket. Allowed values are "new", "open", "pending", "hold", "solved", or "closed".
subjecttextThe value of the subject field for this ticket
submitter_idbigintThe user who submitted the ticket. The submitter always becomes the author of the first comment on the ticket
tagsjsonbThe array of tags applied to this ticket
ticket_form_idbigintEnterprise only. The id of the ticket form to render for the ticket
typetextThe type of this ticket. Allowed values are "problem", "incident", "question", or "task".
updated_attimestamp without time zoneWhen this record last got updated
urltextThe API url of this ticket
via_channeltextHow the ticket or event was created. Examples: "web", "mobile", "rule", "system"
via_followup_source_idtextThe id of a closed ticket when creating a follow-up ticket.
via_source_fromjsonbSource the ticket was sent to
via_source_reftextMedium used to raise the ticket
via_source_tojsonbTarget that received the ticket