turbot/zendesk

steampipe plugin install zendesksteampipe plugin install zendesk
On This Page
Get Involved

Table: zendesk_ticket_audit

Each update to a ticket creates a row in the zendesk_ticket_audit table. Each update can also cause a number of events, stored as a jsonb array in the events column.

It's common to expand the ticket audit data with the events when working on this table. Event details are outlined here.

Examples

List all ticket audit rows

select
ticket_id,
id,
created_at,
author_id,
jsonb_array_length(events)
from
zendesk_ticket_audit;

List all events associated with ticket audits

select
ta.ticket_id,
ta.id,
e ->> 'id' as event_id,
e ->> 'type' as type,
e ->> 'field_name' as field_name,
e ->> 'previous_value' as previous_value,
e ->> 'value' as value
from
zendesk_ticket_audit as ta,
lateral
jsonb_array_elements(ta.events) as e;

List all events that changed tags

select
ta.ticket_id,
ta.id,
e ->> 'id' as event_id,
e ->> 'type' as type,
e ->> 'field_name' as field_name,
e ->> 'previous_value' as previous_value,
e ->> 'value' as value
from
zendesk_ticket_audit as ta,
lateral
jsonb_array_elements(ta.events) as e
where
e ->> 'type' = 'Change'
and
e ->> 'field_name' = 'tags';

List all satisfaction rating events

select
ta.ticket_id,
ta.id,
e ->> 'id' as event_id,
e ->> 'type' as type,
e ->> 'score' as score
from
zendesk_ticket_audit as ta,
lateral
jsonb_array_elements(ta.events) as e
where
e ->> 'type' = 'SatisfactionRating'

.inspect zendesk_ticket_audit

Audits are a read-only history of all updates to a ticket. When a ticket is updated in Zendesk Support, an audit is stored. Each audit represents a single update to the ticket. An update can consist of one or more events.

NameTypeDescription
author_idbigintThe user who created the audit.
created_attimestamp without time zoneThe time the audit was created.
eventsjsonbAn array of the events that happened in this audit.
idbigintUnique identifier for the ticket update.
metadatajsonbMetadata for the audit, custom and system data.
ticket_idbigintThe ID of the associated 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.