SQL Server guide

Read record labels (tags)

This example demonstrates how to retrieve and filter UI labels (tags) applied to a specific record in ServiceNow using the get_record_labels endpoint.

The endpoint queries the label_entry table and scopes results using:

  • ParentTableName – the name of the parent table (for example incident or problem)
  • ParentSysId – the sys_id of the parent record

Additional filtering is applied using the Query parameter. Filtering must use real database fields such as label.name. Runtime-only fields like label.display_value exist only in the API response and cannot be used in query conditions; such filters will be ignored by the ServiceNow query engine.

The API response includes human-readable label values because sysparm_display_value=true is always applied by this endpoint.

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. 

-- 1. Get ALL labels (tags) for a specific Problem record
SELECT *
FROM get_record_labels
WITH(
	ParentTableName='problem',
	ParentSysId='62304320731823002728660c4cf6a7e8'
)

-- 2. Check whether the record has a specific label
SELECT *
FROM get_record_labels
WITH(
	ParentTableName='problem',
	ParentSysId='62304320731823002728660c4cf6a7e8',
	Query='label.name=trial'
)

-- 3. Get labels where the name starts with a prefix (e.g. odbc*)
SELECT *
FROM get_record_labels
WITH(
	ParentTableName='problem',
	ParentSysId='62304320731823002728660c4cf6a7e8',
	Query='label.nameSTARTSWITHodbc'
)

-- 4. Get labels where the name contains a keyword
SELECT *
FROM get_record_labels
WITH(
	ParentTableName='problem',
	ParentSysId='62304320731823002728660c4cf6a7e8',
	Query='label.nameLIKEpower'
)

-- 5. Get labels added by a specific user
SELECT *
FROM get_record_labels
WITH(
	ParentTableName='problem',
	ParentSysId='62304320731823002728660c4cf6a7e8',
	Query='sys_created_by=admin'
)

-- 6. Get labels added in the last 7 days
SELECT *
FROM get_record_labels
WITH(
	ParentTableName='problem',
	ParentSysId='62304320731823002728660c4cf6a7e8',
	Query='sys_created_on>=<<today-7d,FUN_TO_DATETIME>>'
)

-- 7. Combine multiple filters and order by most recent label
SELECT *
FROM get_record_labels
WITH(
	ParentTableName='problem',
	ParentSysId='62304320731823002728660c4cf6a7e8',
	Query='label.nameLIKEtrial^sys_created_by=admin^ORDERBYDESCsys_created_on'
)

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_SERVICENOW_IN_GATEWAY], '-- 1. Get ALL labels (tags) for a specific Problem record
SELECT *
FROM get_record_labels
WITH(
	ParentTableName=''problem'',
	ParentSysId=''62304320731823002728660c4cf6a7e8''
)

-- 2. Check whether the record has a specific label
SELECT *
FROM get_record_labels
WITH(
	ParentTableName=''problem'',
	ParentSysId=''62304320731823002728660c4cf6a7e8'',
	Query=''label.name=trial''
)

-- 3. Get labels where the name starts with a prefix (e.g. odbc*)
SELECT *
FROM get_record_labels
WITH(
	ParentTableName=''problem'',
	ParentSysId=''62304320731823002728660c4cf6a7e8'',
	Query=''label.nameSTARTSWITHodbc''
)

-- 4. Get labels where the name contains a keyword
SELECT *
FROM get_record_labels
WITH(
	ParentTableName=''problem'',
	ParentSysId=''62304320731823002728660c4cf6a7e8'',
	Query=''label.nameLIKEpower''
)

-- 5. Get labels added by a specific user
SELECT *
FROM get_record_labels
WITH(
	ParentTableName=''problem'',
	ParentSysId=''62304320731823002728660c4cf6a7e8'',
	Query=''sys_created_by=admin''
)

-- 6. Get labels added in the last 7 days
SELECT *
FROM get_record_labels
WITH(
	ParentTableName=''problem'',
	ParentSysId=''62304320731823002728660c4cf6a7e8'',
	Query=''sys_created_on>=<<today-7d,FUN_TO_DATETIME>>''
)

-- 7. Combine multiple filters and order by most recent label
SELECT *
FROM get_record_labels
WITH(
	ParentTableName=''problem'',
	ParentSysId=''62304320731823002728660c4cf6a7e8'',
	Query=''label.nameLIKEtrial^sys_created_by=admin^ORDERBYDESCsys_created_on''
)')

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) = '-- 1. Get ALL labels (tags) for a specific Problem record
SELECT *
FROM get_record_labels
WITH(
	ParentTableName=''problem'',
	ParentSysId=''62304320731823002728660c4cf6a7e8''
)

-- 2. Check whether the record has a specific label
SELECT *
FROM get_record_labels
WITH(
	ParentTableName=''problem'',
	ParentSysId=''62304320731823002728660c4cf6a7e8'',
	Query=''label.name=trial''
)

-- 3. Get labels where the name starts with a prefix (e.g. odbc*)
SELECT *
FROM get_record_labels
WITH(
	ParentTableName=''problem'',
	ParentSysId=''62304320731823002728660c4cf6a7e8'',
	Query=''label.nameSTARTSWITHodbc''
)

-- 4. Get labels where the name contains a keyword
SELECT *
FROM get_record_labels
WITH(
	ParentTableName=''problem'',
	ParentSysId=''62304320731823002728660c4cf6a7e8'',
	Query=''label.nameLIKEpower''
)

-- 5. Get labels added by a specific user
SELECT *
FROM get_record_labels
WITH(
	ParentTableName=''problem'',
	ParentSysId=''62304320731823002728660c4cf6a7e8'',
	Query=''sys_created_by=admin''
)

-- 6. Get labels added in the last 7 days
SELECT *
FROM get_record_labels
WITH(
	ParentTableName=''problem'',
	ParentSysId=''62304320731823002728660c4cf6a7e8'',
	Query=''sys_created_on>=<<today-7d,FUN_TO_DATETIME>>''
)

-- 7. Combine multiple filters and order by most recent label
SELECT *
FROM get_record_labels
WITH(
	ParentTableName=''problem'',
	ParentSysId=''62304320731823002728660c4cf6a7e8'',
	Query=''label.nameLIKEtrial^sys_created_by=admin^ORDERBYDESCsys_created_on''
)'
EXEC (@MyQuery) AT [LS_TO_SERVICENOW_IN_GATEWAY]