Tickets

Create, update, delete, and query Tickets.

Table Specific Information

Select

The following queries are processed server side.

SELECT * FROM Tickets WHERE Id = '123'

SELECT * FROM Tickets WHERE Id IN ('123', '456')

SELECT * FROM Tickets WHERE UserId = '123'

SELECT * FROM Tickets WHERE OrganizationId = '123'

SELECT * FROM Tickets WHERE ViewId = '123'

The following ticket property keywords (columns/pseudo-columns) from the Search API are supported in the WHERE clause: created (CreatedAt), updated (UpdatedAt), due_date (DueAt), assignee, submitter, requester, via, subject, description, status, priority, ticket_type (Type), GroupId, GroupName, tags, commenter, cc, has_attachment. The supported search operators are: =, <, >, <=, >=. For example:

SELECT * FROM Tickets WHERE CreatedAt = '2017-02-15'

SELECT * FROM Tickets WHERE CreatedAt > '2017-02-15 10:15:00 AM' AND Status = 'open'

Other filters are processed client side within the 本製品. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any other search criteria will be ignored and an unfiltered response will be returned.

Insert

The Description or Comment fields are required to insert. Any other Custom Field of the ticket may be required to insert. Allowed for Agents.

INSERT INTO Tickets (Subject, Description) VALUES ('Sample Task', 'Details regarding the task.')

To insert multiple tickets, see Batch Processing for an example.

Update

You must specify the Id of the ticket to update it. All fields that are not read-only (readonly="false" in the table) are optional. Allowed for Agents.

UPDATE Tickets SET Subject = 'updated', Status = 'Open' WHERE Id = '123'

You can create a new ticket comment by updating the ticket:

UPDATE Tickets SET Comment = 'this is a new comment' WHERE Id = '123'

To make the same change to multiple tickets, use the following SQL statement:

UPDATE Tickets SET Status = 'Open' WHERE Id IN ('123', '456')

To make different changes to multiple tickets, see Batch Processing for an example.

Delete

You must specify the Id of the ticket to delete it. Allowed for Admins.

DELETE FROM Tickets WHERE Id = '123'

You can delete many tickets simultaneously by providing their ids:

DELETE FROM Tickets WHERE Id IN ('123', '456')

You can also delete many tickets simultaneously by using Batch Processing.

To get the Ids of deleted tickets run this query:

GETDELETED FROM Tickets

To delete a ticket permanently use the DeleteTicketsPermanently stored procedure.

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	False		Automatically assigned when the ticket is created.
Subject	String	False		The value of the subject field for this ticket. When used in WHERE clause: The text in the ticket's subject.
RawSubject	String	False		The dynamic content placeholder, if present, or the subject value, if not.
Description	String	False		The first comment on the ticket. When used in WHERE clause: The text in the ticket's description and comments.
Type	String	False		The type of this ticket. Possible values: problem, incident, question or task.
Priority	String	False		The urgency with which the ticket should be addressed. Possible values: urgent, high, normal, low.
Status	String	False		The state of the ticket. Possible values: new, open, pending, hold, solved, closed.
Recipient	String	False		The original recipient e-mail address of the ticket.
HasIncidents	Boolean	False		Is true of this ticket has been marked as a problem, false otherwise.
DueAt	Datetime	False		If this is a ticket of type task it has a due date. Due date format uses ISO 8601 format.
RequesterId	Long	False	Users.Id	The user who requested this ticket.
SubmitterId	Long	False	Users.Id	The user who submitted the ticket. The submitter always becomes the author of the first comment on the ticket.
AssigneeId	Long	False	Users.Id	The agent currently assigned to the ticket.
OrganizationId	String	False	Organizations.Id	The organization of the requester. You can only specify the ID of an organization associated with the requester.
GroupId	Long	False	Groups.Id	The ID of the group, to which the Ticket is assigned.
CollaboratorIds	String	False		The ids of users currently cc-ed on the ticket.
FollowerIds	String	False		Agents currently following the ticket.
ForumTopicId	Long	False	Topics.Id	The topic this ticket originated from, if any.
ProblemId	Integer	False	Tickets.Id	For tickets of type incident, the ID of the problem the incident is linked to.
ExternalId	String	False		An id you can use to link Zendesk Support tickets to local records.
FollowupIds	String	False		Closed tickets only. The ids of the followups created from this ticket.
MacroIds	String	False		POST requests only. List of macro IDs to be recorded in the ticket audit.
TicketFormId	Long	False	TicketForms.Id	Enterprise only. The id of the ticket form to render for the ticket.
BrandId	Long	False	Brands.Id	Enterprise only. The id of the brand this ticket is associated with.
Tags	String	False		The array of tags applied to this ticket.
ViaChannel	String	False		This tells you how the ticket or event was created. Examples: web, mobile, rule, system.
ViaSource	String	False		The ticket's source.
SatisfactionRating	String	False		The satisfaction rating of the ticket, if it exists, or the state of satisfaction, 'offered' or 'unoffered'.
SharingAgreementIds	String	False		The ids of the sharing agreements used for this ticket.
AllowChannelback	Boolean	False		Is false if channelback is disabled, true otherwise. Only applicable for channels framework ticket.
IsPublic	Boolean	False		Is true if any comments are public, false otherwise.
CreatedAt	Datetime	False		When this record was created.
UpdatedAt	Datetime	False		When this record last got updated.
Url	String	False		The API url of this ticket.
View_Id	Long	False	Views.Id	The view that tickets belong to.
User_Id	Long	False	Users.Id	The user that tickets belong to.
Assignee	String	False		The assigned agent or other entity. You can specify 'none', 'me', user name (full name or partial), email address, user ID, or phone number.
GroupName	String	False		The name of the group to which the Ticket is assigned. If it is not used as a query filter, a search on this column results in null.
Requester	String	False		The ticket requester. You can specify 'none', 'me', user name (full name or partial), email address, user ID, or phone number.
Submitter	String	False		The ticket submitter. This may be different than the requester if the ticket was submitted by an agent on behalf of the requester. You can specify 'none', 'me', user name (full name or partial), email address, user ID, or phone number. .
Brand	String	False		Search for a specific Brand on a ticket. If it is not used as a query filter, a search on this column results in null.
Commenter	String	False		People who have added comments to tickets. You can specify 'none', 'me', user name (full name or partial), email address, user ID, or phone number.
Cc	String	False		People who have been CC'd on tickets. You can specify 'none', 'me', user name (full name or partial), email address, user ID, or phone number.
HasAttachment	Boolean	False		Search for all tickets with or without attachments using 'true' or 'false'.
AllowAttachments	Boolean	False		Permission for agents to add add attachments to a comment. Defaults to true.
CustomStatusId	Long	False		The custom ticket status id of the ticket.
EmailCcIds	String	False		An array of objects that represent agent or end users email CCs to add or delete from the ticket.
FromMessagingChannel	Boolean	False		If true, the ticket's via type is a messaging channel.
ResultType	String	False		Result type.

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。


Name	Type	Description
Via	String	The ticket's source, which can be any of the following:mail, api, phone, etc.
ViaFollowupSourceId	String	POST requests only. The id of a closed ticket when creating a follow-up ticket.
Solved	Date	The date the ticket was set to solved.
Comment	String	Used to create a comment on the ticket, when creating or updating a ticket.
IsPublicComment	Boolean	Used when creating a comment on the ticket. Set this to true to add a public comment and false to add an internal note.
TicketComments	String	Ticket comments object.
ReferenceNumber	String	Reference number which can be use while doing temp table insertion.

Power BI Connector for Zendesk