Supported SQL functions

Common SQL functions used in SQL API for querying and analyzing incident data.

ServiceNow supports a variety of SQL functions for querying and analyzing data in tables such as the incident table. This abbreviated list covers common SQL functions organized by category, with sample use cases and queries focused on incident management scenarios.

Note:

The query engine only supports INNER and LEFT OUTER joins currently.

Table 1. Supported SQL functions

Category	Function Name	Sample Use Case	Sample Query
Aggregate Fn	AVG	Calculate average priority level of resolved incidents to measure severity trends	SELECT AVG(CAST(priority AS FLOAT)) AS avg_priority_level FROM incident WHERE state = 7;
Aggregate Fn	COUNT	Count total open incidents by priority level	SELECT priority, COUNT (*) AS incident_count FROM incident WHERE state IN (1,2,3) GROUP BY priority;
Aggregate Fn	SUM	Calculate total number of updates across all P1 incidents	SELECT SUM(sys_mod_count) AS total_updates FROM incident WHERE priority = 1;
Aggregate Fn	MAX	Find the highest priority value in open incidents	SELECT MAX(priority) AS highest_priority FROM incident WHERE state IN (1,2,3);
Clauses	CASE	Categorize incidents by priority for dashboard visualization	SELECT number, CASE WHEN priority = 1 THEN 'Critical' WHEN priority = 2 THEN 'High' WHEN priority = 3 THEN 'Medium' ELSE 'Low' END AS priority_label FROM incident WHERE state IN (1,2,3);
Clauses	TOP	Identify top 10 most modified incidents for quality review	SELECT TOP 10 number, sys_mod_count FROM incident ORDER BY sys_mod_count DESC;
Clauses	GROUP BY	Analyze incident volume by category for trending	SELECT category, COUNT(*) AS count FROM incident WHERE state IN (1,2,3) GROUP BY category;
Clauses	HAVING	Find assignment groups with more than 50 open incidents for capacity planning	SELECT assignment_group , COUNT(*) AS open_count FROM incident WHERE state IN (1,2,3) GROUP BY assignment_group HAVING COUNT(*) > 50;
Clauses	LEFT JOIN	List incidents with assigned user details for team performance report	SELECT i.number, i.priority, u.name AS assigned_to, u.department FROM incident i LEFT JOIN sys_user u ON i.assigned_to = u.sys_id WHERE i.state IN (1,2,3);
Clauses	ORDER BY ASC	Retrieve oldest incidents by opened date for escalation review	SELECT number, short_description, opened_at FROM incident WHERE state IN (1,2,3) ORDER BY opened_at ASC;
Clauses	DISTINCT	Get unique list of categories from recent incidents	SELECT DISTINCT category FROM incident WHERE state IN (1,2,3);
Clauses	SUBQUERY (FROM)	Calculate average incident count per user from active assignments	SELECT AVG(incident_count) AS avg_per_user FROM (SELECT assigned_to, COUNT(*) AS incident_count FROM incident WHERE assigned_to IS NOT NULL GROUP BY assigned_to) AS user_stats;
Clauses	SUBQUERY (WHERE)	Find incidents assigned to active IT Support users	SELECT number, short_description, assigned_to FROM incident WHERE assigned_to IN (SELECT sys_id FROM sys_user WHERE department = 'IT Support' AND active = 1) AND state IN (1,2,3);
Clauses	UNION	Combine high priority and unassigned incidents for urgent action list	SELECT number, 'P1-Critical' AS reason, assigned_to, priority, state FROM incident WHERE priority = 1 AND state IN (1,2,3) UNION SELECT number, 'Unassigned' AS reason, assigned_to, priority, state FROM incident WHERE assigned_to IS NULL AND state IN (1,2,3);
DateTime FN	date_part	Analyze incident creation patterns by year for historical trending	SELECT date_part('year', opened_at) AS year, COUNT(*) AS incidents FROM incident GROUP BY date_part('year', opened_at) ORDER BY date_part('year', opened_at);
DateTime FN	date_trunc	Group incidents by month for executive monthly trend report	SELECT date_trunc('month', opened_at) AS month, COUNT(*) AS total FROM incident GROUP BY month ORDER BY month;
Numeric FN	ABS	Calculate absolute difference between priority values for normalization	SELECT number, ABS(priority - 3) AS priority_deviation FROM incident WHERE state IN (1,2,3);
Numeric FN	CEILING	Round up priority division for weighted scoring calculations	SELECT number, priority, CEILING(CAST(sys_mod_count AS FLOAT) / 3) AS update_score FROM incident WHERE state IN (1,2,3);
Numeric FN	FLOOR	Calculate floored average of modification counts for baseline metrics	SELECT assignment_group, FLOOR(AVG(CAST(sys_mod_count AS FLOAT))) AS avg_updates FROM incident GROUP BY assignment_group;
Operators	IN	Filter incidents for business-critical categories for executive dashboard	SELECT number, category, priority, state FROM incident WHERE category IN ('Network', 'Database', 'Security', 'Application');
Operators	IS NOT NULL	Find incidents with configuration item assignments for CMDB analysis	SELECT number, cmdb_ci, category, assignment_group FROM incident WHERE cmdb_ci IS NOT NULL;
Operators	LIKE	Search for password reset and access-related incidents for self-service analysis	SELECT number, short_description, caller_id FROM incident WHERE short_description LIKE '%password%' OR short_description LIKE '%login%';
Operators	NOT BETWEEN	Identify incidents with unusual priority values for data quality audit	SELECT number, priority, short_description FROM incident WHERE priority NOT BETWEEN 2 AND 4;
ServiceNow Specific FN	DV	Display human-readable reference field values in reports	SELECT number, DV(assignment_group) AS group_name, DV(assigned_to) AS assignee_name FROM incident WHERE state IN (1,2,3);
String FN	CONCAT_WS	Create formatted incident summaries for external ticketing systems	SELECT concat_ws(' - ', number, category, short_description) AS formatted_summary FROM incident WHERE state = 7;
String FN	LOWER	Standardize category names for case-insensitive grouping and analysis	SELECT LOWER(category) AS category_normalized FROM incident;
String FN	REPLACE	Transform incident numbers for external system integration	SELECT REPLACE(number, 'INC', 'TICKET-') AS external_id, short_description FROM incident WHERE state = 7;
String FN	SUBSTR	Extract incident prefix for categorization and reporting	SELECT number, SUBSTR(number, 1, 3) AS prefix, SUBSTR(number, 4, 20) AS sequence FROM incident;
String FN	TRIM	Clean white space from descriptions for data quality improvement	SELECT number, TRIM(short_description) AS clean_description FROM incident WHERE short_description IS NOT NULL;
Windows FN	RANK()	Rank incidents by number of updates to identify most frequently modified tickets	SELECT number, sys_mod_count, assignment_group, RANK() OVER (ORDER BY sys_mod_count DESC) AS modification_rank FROM incident WHERE assignment_group IS NOT NULL;