steampipe plugin install zendesk

Table: zendesk_ticket - Query Zendesk Tickets using SQL

Zendesk Tickets are a key component of Zendesk's customer service platform, representing a communication between a customer and an agent. They contain the entire communication thread and relevant data, offering a comprehensive view of the customer's issue and the steps taken to resolve it. They are instrumental in tracking, prioritizing, and solving customer's support interactions.

Table Usage Guide

The zendesk_ticket table offers insights into the tickets within Zendesk's customer service platform. As a customer support agent or manager, you can explore ticket-specific details through this table, including status, priority, and associated customer information. Use it to manage and prioritize your support interactions, gain a better understanding of customer issues, and track the steps taken towards resolution.

Examples

List open tickets

Explore which customer issues are unresolved by identifying open tickets in your customer support system. This allows for efficient prioritization and management of customer requests, ensuring timely responses and improved customer satisfaction.

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

Ticket status summary

Gain insights into the distribution of ticket statuses to understand which types are most common in your Zendesk system, aiding in resource allocation and customer service improvement efforts.

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

Stale tickets

Identify instances where tickets in 'open', 'pending', or 'hold' status have not been updated in the past week. This can help prioritize ticket resolution and improve customer service response times. 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');
select
id,
status,
updated_at,
subject
from
zendesk_ticket
where
updated_at < date('now', '-7 day')
and status in ('open', 'pending', 'hold');

Tickets assigned to Jane

Discover the segments that are assigned to a particular user, in this case, Jane. This can be useful to understand Jane's workload and the status of her assigned tasks.

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%';
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 like '%jane%';

Ticket aging reports

Explore which tickets are still open, pending, or on hold, and assess their age to understand the efficiency of your customer service. This query can help identify potential bottlenecks or delays in your ticket resolution process. 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;
select
julianday('now') - julianday(t.created_at) as age,
t.id,
t.status,
u.name as agent,
o.name as organization,
substr(t.subject, 1, 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;
with aging as (
select
id,
created_at,
status,
subject,
julianday('now') - julianday(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
date('now') 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;

Schema for zendesk_ticket

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
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 with 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 with 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_idtextAn 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.
idbigint=Automatically 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 with 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

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- zendesk

You can pass the configuration to the command with the --config argument:

steampipe_export_zendesk --config '<your_config>' zendesk_ticket