Converting gs.sql statements to Glide

adamc1 · ‎03-21-2016

Hi All,

Recently we have discovered a business rule that has been running daily for many years has been running slower and slower and finally is causing operational issues and locking tables during execution. Long story short, we have found out by SerivceNow support that we shouldn't be using gs.sql() in the first place and should be using Glide script. I am comfortable with SQL but have been on a crash course learning about Glide to attempt a conversion. The trouble I've found is that it seems quite difficult to convert my SQL statements to use Glide.

Here is out SQL..looks long and complex, but essentially all its doing is:

1) clearing a table

2) populating the table with grouped by records (going through all tasks opened_at dates, converting them into the first of each month and distinctly selecting the record)

3) going back through and updating the open, closed and active count for each record

4) setting up some active buckets (eg. tasks opened longer than 30 days, 60 days, and 90 days)

My question is, where on earth do I start as I cant seem to grasp how Glide could take care of these nested selects, etc.. This is probably simple for someone who has experience in writing Glide queries but its not clicking with me at the moment.

Cheers,

Adam

================================================================

// All existing statistics in the table are removed
gs.sql("TRUNCATE TABLE u_task_trend")

// The table is populated with records for each combination of period, business unit, assignment group and task type
gs.sql("
INSERT INTO u_task_trend(sys_id, sys_created_by, sys_created_on, sys_updated_by,
sys_updated_on, sys_mod_count, u_period_start, u_business_unit, u_assignment_group, u_task_type)
SELECT
uuid(), 'admin', sysdate(), 'admin', sysdate(), 0, x.period_date, y.u_business_unit, z.assignment_group, c.value
FROM
(SELECT DISTINCT CAST(DATE_FORMAT(t.opened_at ,'%Y-%m-01') as DATE) period_date FROM task t) x,
(SELECT DISTINCT t.u_business_unit FROM task t WHERE u_business_unit IS NOT NULL) y,
(SELECT DISTINCT t.assignment_group FROM task t WHERE u_business_unit IS NOT NULL) z,
sys_choice c
WHERE c.name = 'u_task_trend' ORDER BY 1")

// The number of open tasks in a period is calculated
gs.sql("UPDATE u_task_trend a SET u_open_count = (SELECT count(t.number)
FROM task t
WHERE t.u_internal= 0
AND t.sys_class_name = a.u_task_type
AND t.assignment_group = a.u_assignment_group
AND t.u_business_unit = a.u_business_unit
AND t.opened_at >= a.u_period_start
AND t.opened_at < DATE_ADD(a.u_period_start, INTERVAL 1 MONTH))")

// The number of closed tasks in a period is calculated
gs.sql("UPDATE u_task_trend a SET u_close_count = (SELECT count(t.number) FROM task t WHERE t.u_internal= 0 AND t.sys_class_name = a.u_task_type AND t.assignment_group = a.u_assignment_group AND t.u_business_unit = a.u_business_unit AND IFNULL(t.u_resolved,t.closed_at) >= a.u_period_start AND IFNULL(t.u_resolved,t.closed_at) < DATE_ADD(a.u_period_start, INTERVAL 1 MONTH) AND t.state IN (6,7))")

// The number of active tasks in a period is calculated
gs.sql("UPDATE u_task_trend a SET u_active_count = (SELECT count(t.number) FROM task t WHERE t.u_internal= 0 AND t.sys_class_name = a.u_task_type AND t.assignment_group = a.u_assignment_group AND t.u_business_unit = a.u_business_unit AND t.opened_at < DATE_ADD(a.u_period_start, interval 1 month) AND (IFNULL(t.u_resolved,t.closed_at) >= DATE_ADD(a.u_period_start, INTERVAL 1 MONTH) OR t.state NOT IN (6,7)))")

// The number of active tasks opened for less than 30 days is calculated
gs.sql("UPDATE u_task_trend a SET u_active_bucket1 = (SELECT count(t.number) FROM task t WHERE t.u_internal= 0 AND t.sys_class_name = a.u_task_type AND t.assignment_group = a.u_assignment_group AND t.u_business_unit = a.u_business_unit AND IFNULL(t.u_target_start_date,t.opened_at) < DATE_ADD(a.u_period_start, interval 1 month) AND (IFNULL(t.u_resolved,t.closed_at) >= DATE_ADD(a.u_period_start, INTERVAL 1 MONTH) OR t.state NOT IN (6,7)) AND DATEDIFF(LEAST(DATE_ADD(a.u_period_start, INTERVAL 1 MONTH),SYSDATE()),IFNULL(t.u_target_start_date,t.opened_at)) <=30 )")

// The number of active tasks opened between 30 and 60 days is calculated
gs.sql("UPDATE u_task_trend a SET u_active_bucket2 = (SELECT count(t.number) FROM task t WHERE t.u_internal= 0 AND t.sys_class_name = a.u_task_type AND t.assignment_group = a.u_assignment_group AND t.u_business_unit = a.u_business_unit AND IFNULL(t.u_target_start_date,t.opened_at) < DATE_ADD(a.u_period_start, interval 1 month) AND (IFNULL(t.u_resolved,t.closed_at) >= DATE_ADD(a.u_period_start, INTERVAL 1 MONTH) OR t.state NOT IN (6,7)) AND DATEDIFF(LEAST(DATE_ADD(a.u_period_start, INTERVAL 1 MONTH),SYSDATE()),IFNULL(t.u_target_start_date,t.opened_at)) BETWEEN 31 AND 60 )")

// The number of active tasks opened between 60 and 90 days is calculated
gs.sql("UPDATE u_task_trend a SET u_active_bucket3 = (SELECT count(t.number) FROM task t WHERE t.u_internal= 0 AND t.sys_class_name = a.u_task_type AND t.assignment_group = a.u_assignment_group AND t.u_business_unit = a.u_business_unit AND IFNULL(t.u_target_start_date,t.opened_at) < DATE_ADD(a.u_period_start, interval 1 month) AND (IFNULL(t.u_resolved,t.closed_at) >= DATE_ADD(a.u_period_start, INTERVAL 1 MONTH) OR t.state NOT IN (6,7)) AND DATEDIFF(LEAST(DATE_ADD(a.u_period_start, INTERVAL 1 MONTH),SYSDATE()),IFNULL(t.u_target_start_date,t.opened_at)) BETWEEN 61 AND 90 )")

// The number of active tasks opened greater than 90 days is calculated
gs.sql("UPDATE u_task_trend a SET u_active_bucket4 = (SELECT count(t.number) FROM task t WHERE t.u_internal= 0 AND t.sys_class_name = a.u_task_type AND t.assignment_group = a.u_assignment_group AND t.u_business_unit = a.u_business_unit AND IFNULL(t.u_target_start_date,t.opened_at) < DATE_ADD(a.u_period_start, interval 1 month) AND (IFNULL(t.u_resolved,t.closed_at) >= DATE_ADD(a.u_period_start, INTERVAL 1 MONTH) OR t.state NOT IN (6,7)) AND DATEDIFF(LEAST(DATE_ADD(a.u_period_start, INTERVAL 1 MONTH),SYSDATE()),IFNULL(t.u_target_start_date,t.opened_at)) > 90 )")

ghsrikanth · ‎03-22-2016

Its pretty long requirement, I think you have to go by it one by one -

For 1) clearing a table

var grTask = new GlideRecord('u_task_trend');

grTask.query();

grTask.deleteMultiple();

But before changing the code, please revisit the requirement and design

For 2) populating the table with grouped by records (going through all tasks opened_at dates, converting them into the first of each month and distinctly selecting the record)

Please refer to GlideAggregate concept, try to convert and if you face any issue please post your script GlideAggregate, its better to develop first, then get help from community.

GlideAggregate - ServiceNow Wiki

3) going back through and updating the open, closed and active count for each record

4) setting up some active buckets (eg. tasks opened longer than 30 days, 60 days, and 90 days)

The above can be handled by simple GlideRecord queries.

Glide Record cheat sheet - GlideRecord Query Cheat Sheet - ServiceNow Guru

Hopefully it helps, welcome to servicenow scripting. I was also a sql resource two years ago and I can assure you Servicenow scripting is much easy and smooth.

adamc1 · ‎03-22-2016

Hi Srikanth,

I agree on taking it bite by bite.

So far, from looking at the initial SQL nesting, I decided to focus on the date formatting part. I came up with this, but after I loop through and obtain the new perioddate field, I guess my question is, how can I create a variable that contains all of the task fields, but also contains the new perioddate field?

var rec = new GlideRecord('task');

rec.query();

while (rec.next()) {

var gdt = new GlideDateTime(rec.opened_at);

var perioddate = gdt.getYear() + '-' + gdt.getMonth() + '-01';

gs.print(perioddate);

}

Looking at aggregate, isn't that for summing, averaging, etc.. I don't think that will help when trying to get distinct records from the task table. Again I suppose I don't know enough about Glide yet.

This is the piece of SQL that I want to concentrate on first:

SELECT

uuid(), 'admin', sysdate(), 'admin', sysdate(), 0, x.period_date, y.u_business_unit, z.assignment_group, c.value

FROM

(SELECT DISTINCT CAST(DATE_FORMAT(t.opened_at ,'%Y-%m-01') as DATE) period_date FROM task t) x,

(SELECT DISTINCT t.u_business_unit FROM task t WHERE u_business_unit IS NOT NULL) y,

(SELECT DISTINCT t.assignment_group FROM task t WHERE u_business_unit IS NOT NULL) z,

sys_choice c

WHERE c.name = 'u_task_trend' ORDER BY 1")