Table Tickets

Parameters

Supported Operations

Examples

SSIS

Use Zendesk Connector in API Source component to read data or in API Destination component to read/write data:

Read from Tickets (Row by Row) table using API Source

Zendesk

Tickets (Row by Row)

Read/write to Tickets (Row by Row) table using API Destination

Zendesk

Tickets (Row by Row)

Select

ODBC application

Use these SQL queries in your ODBC application data source:

Get all Tickets

Read all tickets from Zendesk

SELECT * FROM Tickets

Get Single Ticket information by ID

SELECT * from Tickets
WHERE ID=104861

Create a new ticket

Use Tickets_Bulk instead of Tickets table if you like to multiple tickets at once. Tickets_Bulk table supports BULK operation (using SOURCE clause).

INSERT INTO Tickets(
	subject
	,status
	,assignee_id
	,comment_body_html --(for html body)
	--,comment_body (for plain text)
	,comment_public
	,tags
	,custom_fields)
VALUES(
  'Test Ticket Subject - From email'
, 'new' --new, solved, closed
, 18590685428 --assign to agent id
, 'This is <b>html body</b>' --markup also supported
, 1 --1=public, 0=private 
, '["tag1","tag2"]' 
  --below json can be obtained using select custom_fields from tickets where id=1234
, '[
  {
    "id": 56608448,
    "value": "1122"
  },
  {
    "id": 57385967,
    "value": "ORD-12345"
  }
]'
)

Create a new ticket (behalf of user)

This example creates ticket behalf of user (i.e. requester_email and requester_name) and if user is missing then creates a new user for supplied username and email. Use Tickets_Bulk instead of Tickets table if you like to multiple tickets at once. Tickets_Bulk table supports BULK operation (using SOURCE clause).

INSERT INTO Tickets(
	subject
	,status
	,requester_email
	,requester_name
	,assignee_id 
	--,group_id --or assign to group
	,comment_body_html --(for html body)
	--,comment_body (for plain text)
	,comment_public
	,tags
	,custom_fields)
VALUES(
  'Test Ticket Subject - From email'
, 'new' --new, solved, closed
, 'fromsomeuser@abc.com' --from email
, 'Bob Smith' --submitter name needed if its new user
, 18590685428 --assign to agent id
--,123435454 --or use group id if not assignee 
, 'This is <b>html body</b>' --markup also supported
--, 'This is plain text'
, 1 --1=public, 0=private 
, '["tag1","tag2"]' 
  --below json can be obtained using select custom_fields from tickets where id=1234
, '[
  {
    "id": 56608448,
    "value": "1122"
  },
  {
    "id": 57385967,
    "value": "ORD-12345"
  }
]'
)

Delete a ticket by Id

DELETE FROM Tickets WHERE Id=111

Update ticket by Id

Update Tickets 
SET subject='Test ticket subject'
  , tags='["tag1","tag2"]' 
  --, additional_tags ='["tag1","tag2"]' --tags you like to remove
  --, remove tags='["tag1","tag2"]'
  , status='pending' --new, solved, closed
  --below json can be obtained using select custom_fields from tickets where id=1234
  , custom_fields='[
  {
    "id": 10000,
    "value": "some value for prop1"
  },
  {
    "id": 10001,
    "value": "some value for prop2"
  }
]'
Where Id = 1234

Update exising ticket with private comment

Update Tickets 
SET comment_body='Closing this ticket'
  , comment_public = 0 --or 1
  , status='solved' --pending, new, closed
  --, comment_body_html='Closing <b>this</b> ticket'
  --, comment_author_id=123545   
Where Id = 1234

Update exising ticket with HTML body

Update Tickets 
SET comment_body_html='Closing <b>this</b> ticket'
  , status='solved' --pending, new, closed
  --, comment_author_id=123545   
  --, comment_public = 0 --or 1  
Where Id = 1234

Create a new ticket using Raw JSON Body approach

INSERT INTO Tickets(_rawdoc_) 
VALUES(
  '{
      "subject": "Test ticket #1",    
      "comment": {
        "body": "Test ticket #1"
      },
      "priority": "urgent"
    }'
)

Update an existing ticket using Raw JSON Body approach

UPDATE Tickets
SET _rawdoc_ =
  '{
      "subject": "Test ticket #1",    
      "comment": {
        "body": "Test ticket #1"
      },
      "priority": "urgent"
    }'
WHERE Id=1234

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Get all Tickets

Read all tickets from Zendesk

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Tickets';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_ZENDESK_IN_DATA_GATEWAY];

Get Single Ticket information by ID

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from Tickets
WHERE ID=104861';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_ZENDESK_IN_DATA_GATEWAY];

Create a new ticket

Use Tickets_Bulk instead of Tickets table if you like to multiple tickets at once. Tickets_Bulk table supports BULK operation (using SOURCE clause).

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Tickets(
	subject
	,status
	,assignee_id
	,comment_body_html --(for html body)
	--,comment_body (for plain text)
	,comment_public
	,tags
	,custom_fields)
VALUES(
  ''Test Ticket Subject - From email''
, ''new'' --new, solved, closed
, 18590685428 --assign to agent id
, ''This is <b>html body</b>'' --markup also supported
, 1 --1=public, 0=private 
, ''["tag1","tag2"]'' 
  --below json can be obtained using select custom_fields from tickets where id=1234
, ''[
  {
    "id": 56608448,
    "value": "1122"
  },
  {
    "id": 57385967,
    "value": "ORD-12345"
  }
]''
)';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_ZENDESK_IN_DATA_GATEWAY];

Create a new ticket (behalf of user)

This example creates ticket behalf of user (i.e. requester_email and requester_name) and if user is missing then creates a new user for supplied username and email. Use Tickets_Bulk instead of Tickets table if you like to multiple tickets at once. Tickets_Bulk table supports BULK operation (using SOURCE clause).

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Tickets(
	subject
	,status
	,requester_email
	,requester_name
	,assignee_id 
	--,group_id --or assign to group
	,comment_body_html --(for html body)
	--,comment_body (for plain text)
	,comment_public
	,tags
	,custom_fields)
VALUES(
  ''Test Ticket Subject - From email''
, ''new'' --new, solved, closed
, ''fromsomeuser@abc.com'' --from email
, ''Bob Smith'' --submitter name needed if its new user
, 18590685428 --assign to agent id
--,123435454 --or use group id if not assignee 
, ''This is <b>html body</b>'' --markup also supported
--, ''This is plain text''
, 1 --1=public, 0=private 
, ''["tag1","tag2"]'' 
  --below json can be obtained using select custom_fields from tickets where id=1234
, ''[
  {
    "id": 56608448,
    "value": "1122"
  },
  {
    "id": 57385967,
    "value": "ORD-12345"
  }
]''
)';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_ZENDESK_IN_DATA_GATEWAY];

Delete a ticket by Id

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Tickets WHERE Id=111';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_ZENDESK_IN_DATA_GATEWAY];

Update ticket by Id

DECLARE @MyQuery NVARCHAR(MAX) = 'Update Tickets 
SET subject=''Test ticket subject''
  , tags=''["tag1","tag2"]'' 
  --, additional_tags =''["tag1","tag2"]'' --tags you like to remove
  --, remove tags=''["tag1","tag2"]''
  , status=''pending'' --new, solved, closed
  --below json can be obtained using select custom_fields from tickets where id=1234
  , custom_fields=''[
  {
    "id": 10000,
    "value": "some value for prop1"
  },
  {
    "id": 10001,
    "value": "some value for prop2"
  }
]''
Where Id = 1234';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_ZENDESK_IN_DATA_GATEWAY];

Update exising ticket with private comment

DECLARE @MyQuery NVARCHAR(MAX) = 'Update Tickets 
SET comment_body=''Closing this ticket''
  , comment_public = 0 --or 1
  , status=''solved'' --pending, new, closed
  --, comment_body_html=''Closing <b>this</b> ticket''
  --, comment_author_id=123545   
Where Id = 1234';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_ZENDESK_IN_DATA_GATEWAY];

Update exising ticket with HTML body

DECLARE @MyQuery NVARCHAR(MAX) = 'Update Tickets 
SET comment_body_html=''Closing <b>this</b> ticket''
  , status=''solved'' --pending, new, closed
  --, comment_author_id=123545   
  --, comment_public = 0 --or 1  
Where Id = 1234';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_ZENDESK_IN_DATA_GATEWAY];

Create a new ticket using Raw JSON Body approach

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Tickets(_rawdoc_) 
VALUES(
  ''{
      "subject": "Test ticket #1",    
      "comment": {
        "body": "Test ticket #1"
      },
      "priority": "urgent"
    }''
)';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_ZENDESK_IN_DATA_GATEWAY];

Update an existing ticket using Raw JSON Body approach

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Tickets
SET _rawdoc_ =
  ''{
      "subject": "Test ticket #1",    
      "comment": {
        "body": "Test ticket #1"
      },
      "priority": "urgent"
    }''
WHERE Id=1234';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_ZENDESK_IN_DATA_GATEWAY];