Reference

Endpoint Get Table Rows

Name

get_table_rows

Description

Get table rows [API reference]

Related Tables

[Dynamic Table]

Parameters

Parameter

Required

Options

Name: TableName

Label: TableName

Table name

YES

Name: PagingRowCount

Label: Page Size (Default=2000, Max=10000)

Max can be 10000 but it may decrease performance if you fetching less than 10K rows in some requests.

YES

Name: SysId

Label: SysId

Row 'sys_id'

Name: Fields

Label: Fields

Comma-separated field names you want to return in the response. E.g. sys_id,name,label,sys_name,sys_updated_on. Using this setting reduces the response size and speed up the processing.

Name: Query

Label: Query / Order By (Server Side Filter)

Server side search / filter condition

Option	Value
Equal Example	numberISINC0000060
AND Example	category=software^category=hardware
OR Example	category=software^ORcategory=hardware
IN Example	categoryINsoftware,hardware
STARTSWITH Example	categorySTARTSWITHsoftware
NOT EQUAL Example	category!=software
Filter Condition AND Order By	category!=software^ORDERBYname
Order By name column	ORDERBYname
Order By name column (Descending)	ORDERBYDESCname
Order By Multiple Fields (ASC + DESC)	ORDERBYcolumn1^ORDERBYDESCcolumn2

Output Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Description
sys_id	`DT_STR`	`varchar(64)`	64
[$parent.Pivot_Name$]	`DT_WSTR`	`nvarchar(2000)`	2000

If the column you are looking for is missing, consider customizing ServiceNow Connector.

Input Columns

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

Examples

SSIS

Use ServiceNow Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

This Endpoint belongs to the [Dynamic Table] table, therefore it is better to use it, instead of accessing the endpoint directly:

API Source - ServiceNow

Allows the reading and writing of incidents, attachments, and more in ServiceNow without coding! The ZappySys ServiceNow API Connector provides easy-to-use, high-performance API access.

ServiceNow

[Dynamic Table]

Required Parameters
TableName	Fill-in the parameter...
Optional Parameters
SysId
Fields
Fields to include in response (Keep blank to get all fields -OR- Enter comma separated list)
Query / Order By (Server Side Filter)
ContineOn404Error	True
DateFormatString	yyyy-MM-dd HH:mm:ss

SSIS API Source - Read from table or endpoint

API Destination

This Endpoint belongs to the [Dynamic Table] table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to get table rows:

API Destination - ServiceNow

Allows the reading and writing of incidents, attachments, and more in ServiceNow without coding! The ZappySys ServiceNow API Connector provides easy-to-use, high-performance API access.

ServiceNow

[Dynamic Table]

Select, Lookup

Required Parameters
TableName	Fill-in the parameter...
Optional Parameters
SysId
Fields
Fields to include in response (Keep blank to get all fields -OR- Enter comma separated list)
Query / Order By (Server Side Filter)
ContineOn404Error	True
DateFormatString	yyyy-MM-dd HH:mm:ss

SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Get a list of incidents

SELECT * FROM incident 

--Using Primary Key				
--SELECT * FROM any_table_here WHERE sys_id='109562a3c611227500a7b7ff98cc0dc7' --Primary Key in WHERE clause

--Using Filter Expression
--SELECT * FROM incident WITH(Query='number=INC0000001') --Equal condition
--SELECT * FROM incident WITH(Query='number!=INC0000001') --Not equal condition
--SELECT * FROM incident WITH(Query='numberININC0000001,INC0000002,INC0000003') --IN condition
--SELECT * FROM incident WITH(Query='number=INC0000001^state=7') --AND condition
--SELECT * FROM incident WITH(Query='number=INC0000001^ORnumber=INC0000002') --OR condition
--SELECT * FROM incident WITH(Query='numberLIKE0001') --LIKE condition
--SELECT * FROM incident WITH(Query='numberSTARTSWITHINC00') --StartWith condition
--SELECT * FROM incident WITH(Query='numberENDSWITH0001') --StartWith condition
--SELECT * FROM incident WITH(Query='number=INC0000001^state=7^NQORnumber=INC0000002') --AND / OR MIXED using NQ (NewQuery Operator)  -- (number=INC0000001 and state=7) OR (number=INC0000002)

--more information about filter here https://docs.servicenow.com/bundle/utah-platform-user-interface/page/use/common-ui-elements/reference/r_OpAvailableFiltersQueries.html


-- To read all available tables execute this query:
-- SELECT * FROM Tables

-- Other common tables:
-----------------------
-- SELECT * FROM sys_db_object
-- SELECT * FROM sys_dictionary
-- SELECT * FROM sys_user
-- SELECT * FROM sys_user_has_role
-- SELECT * FROM sys_user_grmember
-- SELECT * FROM task
-- SELECT * FROM task_sla
-- SELECT * FROM incident
-- SELECT * FROM incident_sla
-- SELECT * FROM change_request
-- SELECT * FROM cmdb_ci_computer
-- SELECT * FROM cmdb_ci_outage
-- SELECT * FROM cmdb_ci
-- SELECT * FROM sn_customerservice_case
-- SELECT * FROM kb_knowledge
-- SELECT * FROM kb_use
-- SELECT * FROM sc_req_item
-- SELECT * FROM sc_request
-- SELECT * FROM sc_task

Get only selected columns and speed up the query

By default all column values are returned in the result set. However, you can instruct the ServiceNow API to send only specific columns you care about by setting 'Fields' parameter in the WITH clause. This will speed up the response significantly.

SELECT number,name FROM incident WITH (Fields='number,name') --Setting Fields parameter will speed up the query

Query table using serverside filter expression

This example shows how to filter data from a table using server side filter expression. Its always faster to do this way rather than using in WHERE clause (Client Side Filter)

SELECT * FROM incident WITH(Query='number=INC0000001') --Equal condition

--syntax (single condition)
--sysparm_query=<col_name><operator><value>

--syntax (multiple conditions)
--sysparm_query=<col_name><operator><value>[<operator><col_name><operator><value>]

--more info check below links
--  https://docs.servicenow.com/bundle/rome-application-development/page/integrate/inbound-rest/concept/c_TableAPI.html#title_table-GET
--  https://docs.servicenow.com/bundle/utah-platform-user-interface/page/use/common-ui-elements/reference/r_OpAvailableFiltersQueries.html
--  Some More Advanced Params listed here
--  https://developer.servicenow.com/dev.do#!/learn/learning-plans/xanadu/servicenow_application_developer/app_store_learnv2_rest_xanadu_request_parameters  

--More examples				
--SELECT * FROM any_table_here WHERE sys_id='109562a3c611227500a7b7ff98cc0dc7' --Primary Key in WHERE clause
--SELECT * FROM incident WITH(Query='number=INC0000001') --Equal condition
--SELECT * FROM incident WITH(Query='number!=INC0000001') --Not equal condition
--SELECT * FROM incident WITH(Query='numberININC0000001,INC0000002,INC0000003') --IN condition
--SELECT * FROM incident WITH(Query='number=INC0000001^state=7') --AND condition
--SELECT * FROM incident WITH(Query='number=INC0000001^ORnumber=INC0000002') --OR condition
--SELECT * FROM incident WITH(Query='numberLIKE0001') --LIKE condition
--SELECT * FROM incident WITH(Query='numberSTARTSWITHINC00') --StartWith condition
--SELECT * FROM incident WITH(Query='numberENDSWITH0001') --StartWith condition
--SELECT * FROM incident WITH(Query='number=INC0000001^state=7^NQORnumber=INC0000002') --AND / OR MIXED using NQ (NewQuery Operator)  -- (number=INC0000001 and state=7) OR (number=INC0000002)

Get a list of system tables

SELECT * FROM SystemTables

Get a list of user tables

SELECT * FROM UserTables

Get an incident by sys_id

SELECT * FROM incident
WHERE sys_id = 'SYS_ID_GOES_HERE'

get_table_rows endpoint belongs to [Dynamic Table] table(s), and can therefore be used via those table(s).

SQL Server

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