Zendesk Connector
Documentation
Version: 9
Documentation
Home » API Integration Hub » Zendesk Connector » EndPoints » Search Records - WITH Sorting and Extra Columns (Max 1000 rows)

EndPoint Search Records - WITH Sorting and Extra Columns (Max 1000 rows)

Name

get_search

Description

Search for ticket, user, organization, or group for specified search criteria. Returns max 1000 records based on search criteria (i.e. query) you specify [API reference]

Parameters

Parameter Label Required Options Description
query Search Criteria YES
Option Value
Example: Search by Ticket ID 112233
Example: Search by Ticket Status status
Example: Search by Some Text SomeText
Example: Search By Status SomeText
Example: Search by Type and Status type:ticket status:open
Example: Search by Type and Name type:user "Jane Doe"
Example: Search by Type and CreationDate type:organization created< 2015-05-01
Example: Search organizations updated after some date type:organization updated > 2015-05-01
Example: Search users updated after some date type:user updated > 2015-05-01
Example: Search By Type and Tag type:ticket tags:red tags:blue
The search query based on this syntax https://support.zendesk.com/hc/en-us/articles/203663226
sort_by Sort By NO
Option Value
updated_at updated_at
created_at created_at
priority priority
status status
ticket_type ticket_type
sort_order Sort Order NO
Option Value
desc desc
asc asc
include Extra Columns to Include NO
Option Value
Default
users users
You can list email CCs by side-loading users.

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Raw Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Raw Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

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

Search Records - WITH Sorting and Extra Columns (Max 1000 rows) using API Source

Zendesk
Search Records - WITH Sorting and Extra Columns (Max 1000 rows)
SSIS API Source - Read from table or endpoint

Search Records - WITH Sorting and Extra Columns (Max 1000 rows) using API Destination

Zendesk
Search Records - WITH Sorting and Extra Columns (Max 1000 rows)
SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Search for user with email id 

SELECT *
FROM get_search
WITH(
	  query='type:user email:bob@abc.com'
)

Search records using query expression (tickets, organization, users) 

SELECT *
FROM get_search --limited to 1000 rows max
--OR
--FROM get_search_export --returns more than 1000 rows but no sorting or extra column allowed
WITH(
       filtertype='', --can be ticket, organization, user, group. If you set here then no need to put in query (e.g. query='type:ticket ...'). This option is ignored for get_search_Export.
	  ,sort_by='updated_at' --created_at, status, priority, ticket_type. This option is ignored for get_search_Export.
	  ,sort_order='asc'  --desc (not valid for get_search_export). This option is ignored for get_search_Export.

    --read this for more information 
	--https://support.zendesk.com/hc/en-us/articles/4408883318554-Searching-users-groups-and-organizations
	--https://support.zendesk.com/hc/en-us/articles/4408886879258-Zendesk-Support-search-reference#topic_crj_yev_uc
	--https://support.zendesk.com/hc/en-us/articles/4408882086298-Searching-tickets
	
      ,query='type:ticket subject:"Upsert Destination"'   --search tickets where subject contains phrase "Upsert Destination" (special characters like comma / dash etc ignored in match)
	--,query='type:ticket 519872'   --search ticket with id 519872
	--,query='type:ticket tags:solved,odbc_powerpack updated>1months' --search tickets where two tags set solved,odbc_powerpack and updated in last one month
	--,query='type:ticket -tags:odbc_powerpack updated>1months' --search tickets where where tags not like odbc_powerpack and updated in last one month
	--,query='type:ticket status<solved created>1months' --search tickets with status not solved and created in last one month
	--,query='type:ticket created:2014-08-01' --search tickets created on 2014-08-01 (UTC)
	--,query='type:ticket created>2014-08-01T10:30:00Z created<2014-08-01T12:00:00Z' --search tickets created between two datetime (UTC time)
	--,query='type:ticket requester:amy@mondocam.com' --search tickets where requester is amy@mondocam.com
	--,query='type:ticket description:defective' --search tickets where decription contains word "defective"
	--,query='type:ticket description:"product is defective"' --search tickets where decription contains phrase  "product is defective"
	--,query='type:ticket priority>low' --search tickets where priority greater than low (e.g. medium or high)"
	
	--,query='ZappySys' --phrase match (no type): anywhere in ticket / organization / user contains ZappySys word - If filtertype parameter supplied then only those type included
	--,query='ZappySys*' --phrase match (no type): wildcard usage
	--,query='type:user "ZappySys"' --search users with term "ZappySys" anywhere in text properties
    --,query='type:user email:"@zappysys"' --search users with term "@ZappySys.com" in email field
	--,query='type:user name:"zappysys"' --search users with term "ZappySys" in name field
	--,query='type:user details:"zappysys"' --search users with term "ZappySys" in details field
    --,query='type:organization "ZappySys LLC"' --phrase match: organization name contains ZappySys LLC
)

SQL Server

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

Search for user with email id 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
FROM get_search
WITH(
	  query=''type:user email:bob@abc.com''
)';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_ZENDESK_IN_DATA_GATEWAY];

Search records using query expression (tickets, organization, users) 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
FROM get_search --limited to 1000 rows max
--OR
--FROM get_search_export --returns more than 1000 rows but no sorting or extra column allowed
WITH(
       filtertype='''', --can be ticket, organization, user, group. If you set here then no need to put in query (e.g. query=''type:ticket ...''). This option is ignored for get_search_Export.
	  ,sort_by=''updated_at'' --created_at, status, priority, ticket_type. This option is ignored for get_search_Export.
	  ,sort_order=''asc''  --desc (not valid for get_search_export). This option is ignored for get_search_Export.

    --read this for more information 
	--https://support.zendesk.com/hc/en-us/articles/4408883318554-Searching-users-groups-and-organizations
	--https://support.zendesk.com/hc/en-us/articles/4408886879258-Zendesk-Support-search-reference#topic_crj_yev_uc
	--https://support.zendesk.com/hc/en-us/articles/4408882086298-Searching-tickets
	
      ,query=''type:ticket subject:"Upsert Destination"''   --search tickets where subject contains phrase "Upsert Destination" (special characters like comma / dash etc ignored in match)
	--,query=''type:ticket 519872''   --search ticket with id 519872
	--,query=''type:ticket tags:solved,odbc_powerpack updated>1months'' --search tickets where two tags set solved,odbc_powerpack and updated in last one month
	--,query=''type:ticket -tags:odbc_powerpack updated>1months'' --search tickets where where tags not like odbc_powerpack and updated in last one month
	--,query=''type:ticket status<solved created>1months'' --search tickets with status not solved and created in last one month
	--,query=''type:ticket created:2014-08-01'' --search tickets created on 2014-08-01 (UTC)
	--,query=''type:ticket created>2014-08-01T10:30:00Z created<2014-08-01T12:00:00Z'' --search tickets created between two datetime (UTC time)
	--,query=''type:ticket requester:amy@mondocam.com'' --search tickets where requester is amy@mondocam.com
	--,query=''type:ticket description:defective'' --search tickets where decription contains word "defective"
	--,query=''type:ticket description:"product is defective"'' --search tickets where decription contains phrase  "product is defective"
	--,query=''type:ticket priority>low'' --search tickets where priority greater than low (e.g. medium or high)"
	
	--,query=''ZappySys'' --phrase match (no type): anywhere in ticket / organization / user contains ZappySys word - If filtertype parameter supplied then only those type included
	--,query=''ZappySys*'' --phrase match (no type): wildcard usage
	--,query=''type:user "ZappySys"'' --search users with term "ZappySys" anywhere in text properties
    --,query=''type:user email:"@zappysys"'' --search users with term "@ZappySys.com" in email field
	--,query=''type:user name:"zappysys"'' --search users with term "ZappySys" in name field
	--,query=''type:user details:"zappysys"'' --search users with term "ZappySys" in details field
    --,query=''type:organization "ZappySys LLC"'' --phrase match: organization name contains ZappySys LLC
)';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_ZENDESK_IN_DATA_GATEWAY];