Security restricted: Calling gs.sql is not allowed

GlideSystem SQL was discontinued in ServiceNow Geneva. Learn why this functionality was permanently removed.

BEGINNER FRIENDLY?

gs.sql was a method in ServiceNow to run SQL queries without using standard Glide Record functionality. If you were familar with SQL, you could use gs.sql. Many of us who switched to ServiceNow knew SQL beforehand. So one of the first things we attempting doing was trying to write gs.sql queries.

Not only were we novices at ServiceNow, but also we used one of the most "dangerous" techniques in ServiceNow. Although warned it was not supported and was "dangerous", you would occasionally see developers throwing out a gs.sql query here and there.

Danger Zone

When used to truncate a table, gs.sql orphans records and causes data corruption. That is the main reason it was dangerous. The other "dangerous" thing is that it holds you back from learning Glide Record queries, which is a must learn. It is the most popular code in ServiceNow, might as well learn how to use them.

Personally didn't use gs.sql, but was interested when I first learned about it. However after hearing it wasn't supported, I just never got into using it in my code.

FOR HISTORY

Even though gs.sql is no longer used, here is my original post about gs.sql from February 2014:

Before I tell you about the ServiceNow GlideSystem SQL function, here are some guidelines for usage:

Test GlideSystem SQL in a sandbox environment first
Do not use GlideSystem SQL to replace standard GlideRecord queries
Use only for troubleshooting. Don't be tempted to use it in your code.

With all that being said, sql can be very useful in ServiceNow. With this GlideSystem function, you can run sql statements right within ServiceNow. If your ServiceNow system is running on a mySQL database, you can just browse mySQL documentation and build your own sql statements.

How to get to Background Scripts

1. Login as an admin with the security_admin role
2. Elevate your privilege to security_admin
3. Go to System Definition > Scripts - Background
4. Run your GlideSystem sql statements

Example GlideSystem sql statements

1. Find a table row count of all the tables in your ServiceNow system

gs.sql("SELECT TABLE_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=\"BASE TABLE\" ORDER BY TABLE_ROWS DESC");

2. Find a table row count from the incident table

gs.sql("select count(*) from incident");

3. Describe the task table

gs.sql("DESCRIBE task");

4. Join the task and incident tables for viewing

gs.sql("select * from incident a, task b where a.sys_id=b.sys_id");

5. Find information about a specific incident.

gs.sql("select * from incident a, task b where a.sys_id=b.sys_id AND number=\"INC0000061\"");

6. Types of CIs

gs.sql('SELECT sys_class_name AS \"CI Class\",count(*) AS \"Total\" FROM cmdb_ci GROUP BY sys_class_name ORDER BY total DESC');

7. Types of Tasks

gs.sql('SELECT sys_class_name AS \"Task Type\",count(*) AS \"Total\" FROM task GROUP BY sys_class_name ORDER BY total DESC');

8. Logged in Users

gs.sql('SELECT * from v_user_session');

9. Find Duplicate Serial Numbers

gs.sql('SELECT serial_number from cmdb_ci where serial_number IS NOT NULL GROUP BY serial_number HAVING COUNT(*) > 1');

10. Find Users with the same email address

gs.sql('SELECT user_name, email from sys_user where email IS NOT NULL GROUP BY email HAVING COUNT(*) > 1');

Mike