Read incidents

Gets a list of incidents from the incident table. You can use this pattern to read from any other table by changing the table name.

Standard SQL query example

This is the base query accepted by the connector. To execute it in SQL Server, you have to pass it to the Data Gateway via a Linked Server. See how to accomplish this using the examples below.

SELECT * FROM incident 

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

--OR-- 

--Use below to query system / hidden tables (faster response - No Table name lookup needed in below)
SELECT * FROM get_table_rows WITH(TableName='incident', Query='number=INC0000001')

--=================================
--Examples: Using Filter Expression
--=================================
-- Incremental Load Pattern: Load only incidents updated in the last 24 hours (see FUN_TO_DATETIME function help for more information, you can use +/- d, m, y, min,h,s along with now, today, yesterday, weekstart, weekend, monthstart, monthend, yearstart, yearend functions)
--SELECT * FROM incident WITH(Query='sys_updated_on>=<<today-1d,FUN_TO_DATETIME>>')

--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

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_SERVICENOW_IN_GATEWAY], 'SELECT * FROM incident 

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

--OR-- 

--Use below to query system / hidden tables (faster response - No Table name lookup needed in below)
SELECT * FROM get_table_rows WITH(TableName=''incident'', Query=''number=INC0000001'')

--=================================
--Examples: Using Filter Expression
--=================================
-- Incremental Load Pattern: Load only incidents updated in the last 24 hours (see FUN_TO_DATETIME function help for more information, you can use +/- d, m, y, min,h,s along with now, today, yesterday, weekstart, weekend, monthstart, monthend, yearstart, yearend functions)
--SELECT * FROM incident WITH(Query=''sys_updated_on>=<<today-1d,FUN_TO_DATETIME>>'')

--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')

Using EXEC in SQL Server (handling larger SQL text)

The major drawback of OPENQUERY is its inability to incorporate variables within SQL statements. This often leads to the use of cumbersome dynamic SQL (with numerous ticks and escape characters).

Fortunately, starting with SQL 2005 and onwards, you can utilize the EXEC (your_sql) AT [LS_TO_SERVICENOW_IN_GATEWAY] syntax.

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

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

--OR-- 

--Use below to query system / hidden tables (faster response - No Table name lookup needed in below)
SELECT * FROM get_table_rows WITH(TableName=''incident'', Query=''number=INC0000001'')

--=================================
--Examples: Using Filter Expression
--=================================
-- Incremental Load Pattern: Load only incidents updated in the last 24 hours (see FUN_TO_DATETIME function help for more information, you can use +/- d, m, y, min,h,s along with now, today, yesterday, weekstart, weekend, monthstart, monthend, yearstart, yearend functions)
--SELECT * FROM incident WITH(Query=''sys_updated_on>=<<today-1d,FUN_TO_DATETIME>>'')

--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'
EXEC (@MyQuery) AT [LS_TO_SERVICENOW_IN_GATEWAY]