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.
| 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; |
Parent Topic:Getting started with ServiceNow SQL API