Read table using server-side filter

Server-side filtering using ServiceNow native query language

This example demonstrates how to filter ServiceNow table data using the native server-side filter expression (sysparm_query) via the WITH(Query='...') clause.

Why this matters:
Server-side filtering is executed inside ServiceNow before data is returned. This dramatically improves performance, reduces network traffic, and avoids unnecessary client-side processing.

Using a SQL WHERE clause without WITH(Query=...) forces the driver to retrieve more data first and then filter locally, which is slower. Always prefer server-side filters first. Use client-side filtering only when a condition cannot be expressed using ServiceNow query syntax.

ServiceNow query operators (mapped to SQL)

Important:

• ^ always means AND
• ^OR applies OR within the same query group
• NQ starts a new OR group (similar to adding parentheses in SQL)

Example:

priority=1^state=2^NQpriority=2^state=3

(priority = 1 AND state = 2) OR (priority = 2 AND state = 3)

Date and time filtering using ZappySys Placeholder Engine

You can dynamically generate date values using placeholders. These are evaluated at runtime before the query is sent to ServiceNow.

• <<today,FUN_TO_DATE>> – today
• <<yesterday,FUN_TO_DATE>> – yesterday
• <<today-1d,FUN_TO_DATE>> – today minus 1 day
• <<today-1m,FUN_TO_DATE>> – today minus 1 month
• <<monthstart,FUN_TO_DATE>> – current month start
• <<monthend,FUN_TO_DATE>> – current month end
• <<yearstart,FUN_TO_DATE>> – year start
• <<yearend,FUN_TO_DATE>> – year end

See full placeholder documentation here:
ZappySys Placeholder Engine – FUN_TO_DATETIME

Reference documentation

• ServiceNow Table API
• ServiceNow Filter Operators
• Advanced Query Parameters

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.

-- Basic server-side filter
SELECT * FROM incident WITH(Query='state=2')

-- Primary key lookup (fastest)
SELECT * FROM incident WHERE sys_id='109562a3c611227500a7b7ff98cc0dc7'

-- Not equal
SELECT * FROM incident WITH(Query='state!=6')

-- IN condition
SELECT * FROM incident WITH(Query='priorityIN1,2,3')

-- AND condition
SELECT * FROM incident WITH(Query='priority=1^state=2')
-- SQL: WHERE priority = 1 AND state = 2

-- OR condition (same group)
SELECT * FROM incident WITH(Query='priority=1^ORstate=2')
-- SQL: WHERE priority = 1 OR state = 2

-- AND / OR mixed using NQ
SELECT * FROM incident WITH(Query='priority=1^state=2^NQpriority=2^state=3')
-- SQL: (priority = 1 AND state = 2) OR (priority = 2 AND state = 3)

-- LIKE / StartsWith / EndsWith
SELECT * FROM incident WITH(Query='short_descriptionLIKEemail')
SELECT * FROM incident WITH(Query='numberSTARTSWITHINC')
SELECT * FROM incident WITH(Query='numberENDSWITH001')

-- Date filter using placeholders (last 1 month)
SELECT * FROM incident WITH(Query='opened_at>=<<today-1m,FUN_TO_DATE>>')

-- From month start
SELECT * FROM incident WITH(Query='opened_at>=<<monthstart,FUN_TO_DATE>>')

-- Between dates
SELECT * FROM incident WITH(Query='opened_at>=<<monthstart,FUN_TO_DATE>>^opened_at<=<<monthend,FUN_TO_DATE>>')

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_SERVICENOW_IN_GATEWAY], '-- Basic server-side filter
SELECT * FROM incident WITH(Query=''state=2'')

-- Primary key lookup (fastest)
SELECT * FROM incident WHERE sys_id=''109562a3c611227500a7b7ff98cc0dc7''

-- Not equal
SELECT * FROM incident WITH(Query=''state!=6'')

-- IN condition
SELECT * FROM incident WITH(Query=''priorityIN1,2,3'')

-- AND condition
SELECT * FROM incident WITH(Query=''priority=1^state=2'')
-- SQL: WHERE priority = 1 AND state = 2

-- OR condition (same group)
SELECT * FROM incident WITH(Query=''priority=1^ORstate=2'')
-- SQL: WHERE priority = 1 OR state = 2

-- AND / OR mixed using NQ
SELECT * FROM incident WITH(Query=''priority=1^state=2^NQpriority=2^state=3'')
-- SQL: (priority = 1 AND state = 2) OR (priority = 2 AND state = 3)

-- LIKE / StartsWith / EndsWith
SELECT * FROM incident WITH(Query=''short_descriptionLIKEemail'')
SELECT * FROM incident WITH(Query=''numberSTARTSWITHINC'')
SELECT * FROM incident WITH(Query=''numberENDSWITH001'')

-- Date filter using placeholders (last 1 month)
SELECT * FROM incident WITH(Query=''opened_at>=<<today-1m,FUN_TO_DATE>>'')

-- From month start
SELECT * FROM incident WITH(Query=''opened_at>=<<monthstart,FUN_TO_DATE>>'')

-- Between dates
SELECT * FROM incident WITH(Query=''opened_at>=<<monthstart,FUN_TO_DATE>>^opened_at<=<<monthend,FUN_TO_DATE>>'')')

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) = '-- Basic server-side filter
SELECT * FROM incident WITH(Query=''state=2'')

-- Primary key lookup (fastest)
SELECT * FROM incident WHERE sys_id=''109562a3c611227500a7b7ff98cc0dc7''

-- Not equal
SELECT * FROM incident WITH(Query=''state!=6'')

-- IN condition
SELECT * FROM incident WITH(Query=''priorityIN1,2,3'')

-- AND condition
SELECT * FROM incident WITH(Query=''priority=1^state=2'')
-- SQL: WHERE priority = 1 AND state = 2

-- OR condition (same group)
SELECT * FROM incident WITH(Query=''priority=1^ORstate=2'')
-- SQL: WHERE priority = 1 OR state = 2

-- AND / OR mixed using NQ
SELECT * FROM incident WITH(Query=''priority=1^state=2^NQpriority=2^state=3'')
-- SQL: (priority = 1 AND state = 2) OR (priority = 2 AND state = 3)

-- LIKE / StartsWith / EndsWith
SELECT * FROM incident WITH(Query=''short_descriptionLIKEemail'')
SELECT * FROM incident WITH(Query=''numberSTARTSWITHINC'')
SELECT * FROM incident WITH(Query=''numberENDSWITH001'')

-- Date filter using placeholders (last 1 month)
SELECT * FROM incident WITH(Query=''opened_at>=<<today-1m,FUN_TO_DATE>>'')

-- From month start
SELECT * FROM incident WITH(Query=''opened_at>=<<monthstart,FUN_TO_DATE>>'')

-- Between dates
SELECT * FROM incident WITH(Query=''opened_at>=<<monthstart,FUN_TO_DATE>>^opened_at<=<<monthend,FUN_TO_DATE>>'')'
EXEC (@MyQuery) AT [LS_TO_SERVICENOW_IN_GATEWAY]