Fix Script Help - updating multiple HR Case Records

[Rob Sestito]

Hello SN Comm!

I am working on script a Fix Script, in order to update many, many records for HR Cases.

Here is what I have as of right now:

var gr = new GlideRecord('sn_hr_core_case');
gr.addQuery('topic_category', 'system issue');
gr.addQuery('topic_detail', 'system issue');
gr.addQuery('hr_service', 'legacy_misc');
gr.addQuery('short_description', 'CONTAINS', 'Workday Password Reset');

gr.query();

while (gr.next()) {
	
	gs.info('fixLegacyMiscCases: I would fix ' + gr.getRowCount() + " records."); //Check script before running - does it return correct number of records?
	
	//gr.topic_category = 'Accounts / Access Control';
	//gr.topic_detail = 'Password Reset';
	//gr.hr_service = 'workday_password_reset';
	
	gr.autoSysFields(false);
	gr.setWorkflow(false);
	gr.update();
}

 However, when I run the script, I just get a message on how long it took to run, but no record count shows up.

And the time ran returns 00:00:04. So, I know it didn't do a thing.

Anyone able to help out what is wrong? I have tried a couple of ways with the format of the script. For example, in my addQueries - I had gr.addQuery("topic_category", 'system_issue'); - and so on for all of them. But the results have been the same no matter what.

Thanks in advance if anyone can help me!

-Rob

[Ron K2]

Hi Rob,

I thought I'd give this a go...

I was able to get better results by querying using the sys_id of the topic category (rather than using the name).

So, it would look something like: 

gr.addQuery('topic_category=9fd9f60d9f321200d9011977677fcf55');
/* Use the sys_id for topic category "system issue" - or you could go to the 
hr case list and run your query and then copy the query into your script */

At least using this method, I'm able to get an accurate row count displayed after the first step of your code. I hope this helps!

- Ron

[Rob Sestito]

Hey Ron,

Thanks for the reply and helping out!

So, I went and grabbed a query from the list view as suggested - and the query comes up with this:

active=false^hr_service=20dfddafdb7a57403171dd1b5e9619c7^short_descriptionLIKEWorkday Password Reset^topic_category=8234dff4dbcd53403171dd1b5e961955^topic_detail=fd445778dbcd53403171dd1b5e961966

So now my script looks like this:

var gr = new GlideRecord('sn_hr_core_case');
gr.addQuery('active=false');
gr.addQuery('topic_category=8234dff4dbcd53403171dd1b5e961955');
gr.addQuery('topic_detail=fd445778dbcd53403171dd1b5e961966');
gr.addQuery('hr_service=20dfddafdb7a57403171dd1b5e9619c7');
gr.addQuery('short_description', 'CONTAINS', 'Workday Password Reset');

gr.query();
gs.info('fixLegacyMiscCases: I would fix ' + gr.getRowCount() + " records.");

while (gr.next()) {
	
	//Check script before running - return correct number of records?
	//gs.info('fixLegacyMiscCases: I would fix ' + gr.getRowCount() + " records.");
	
	//gr.topic_category = 'Accounts / Access Control';
	//gr.topic_detail = 'Password Reset';
	//gr.hr_service = 'workday_password_reset';
	
	gr.autoSysFields(false);
	gr.setWorkflow(false);
	gr.update();
}

And it seems to do something, then I get this as a message (which I have no clue what it is telling me)

[0:00:00.017] Compacting large row block (file.write: sn_hr_core_case 10000 rows 160000 saveSize)
[0:00:00.016] Compacting large row block (file.write: sn_hr_core_case 10000 rows 160000 saveSize)
[0:00:00.003] Compacting large row block (file.write: sn_hr_core_case 1666 rows 26656 saveSize)
[0:00:00.003] Expanding large row block (file.read: sn_hr_core_case, 10000 rows, 160000 dataSize)
Starting: SMTP Sender.d8e56792c0a80064007e86b882a6acc7, Trigger Type: Interval, Priority: 25, Upgrade Safe: true, Repeat: 1 Minute
Name: SMTP Sender
Job Context: 
#Tue May 14 11:45:13 PDT 2019

Script: 

Starting: report view events process.0fb2c110871321004ebe19fa84e3ecf8, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: report view events process
Job Context: 
#Tue May 14 11:45:53 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('report_view').process();
Starting: PA clean scores events process.a51441d1d70022004cd2a3b20e61039a, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: PA clean scores events process
Job Context: 
#Tue May 14 11:45:53 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('pa_clean_scores').process();
Starting: metric update events process.edabc2e10a0a0b6000c2d4f10e8e67e0, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: metric update events process
Job Context: 
#Tue May 14 11:45:53 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('metric_update').process();
		
Completed: SMTP Sender in 0:00:00.002, next occurrence is 05-14-2019 14:46:23
Completed: report view events process in 0:00:00.005, next occurrence is 05-14-2019 14:46:00
Completed: PA clean scores events process in 0:00:00.005, next occurrence is 05-14-2019 14:46:00
Completed: metric update events process in 0:00:00.005, next occurrence is 05-14-2019 14:46:00
Starting: SMTP Sender 2.2808aeb1dbf21300305efb051d9619f5, Trigger Type: Interval, Priority: 25, Upgrade Safe: true, Repeat: 1 Minute
Name: SMTP Sender 2
Job Context: 
#Tue May 14 11:46:03 PDT 2019

Script: 

Starting: Embedded Help Trigger.170722bddbb21300305efb051d961983, Trigger Type: Repeat, Priority: 100, Upgrade Safe: false, Repeat: 1 Minute
Name: Embedded Help Trigger
Job Context: 
#Tue May 14 11:45:04 PDT 2019
fcDocumentClass=sysauto_script
fcDocumentKey=23c59485e72132002d5084f903f6a956

Script: 

Starting: report view events process.0fb2c110871321004ebe19fa84e3ecf8, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: report view events process
Job Context: 
#Tue May 14 11:46:03 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('report_view').process();
Starting: PA clean scores events process.a51441d1d70022004cd2a3b20e61039a, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: PA clean scores events process
Job Context: 
#Tue May 14 11:46:03 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('pa_clean_scores').process();
Completed: SMTP Sender 2 in 0:00:00.002, next occurrence is 05-14-2019 14:46:20
Starting: metric update events process.edabc2e10a0a0b6000c2d4f10e8e67e0, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: metric update events process
Job Context: 
#Tue May 14 11:46:03 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('metric_update').process();
		
Completed: Embedded Help Trigger in 0:00:00.002, next occurrence is 05-14-2019 14:47:04
Completed: report view events process in 0:00:00.006, next occurrence is 05-14-2019 14:46:10
Completed: PA clean scores events process in 0:00:00.005, next occurrence is 05-14-2019 14:46:10
Completed: metric update events process in 0:00:00.005, next occurrence is 05-14-2019 14:46:10
Starting: Push.4ed15122ff2331009738ffffffffff06, Trigger Type: Interval, Priority: 25, Upgrade Safe: true, Repeat: 10 Seconds
Name: Push
Job Context: 
#Tue May 14 11:46:13 PDT 2019

Script: 

Starting: report view events process.0fb2c110871321004ebe19fa84e3ecf8, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: report view events process
Job Context: 
#Tue May 14 11:46:13 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('report_view').process();
Starting: PA clean scores events process.a51441d1d70022004cd2a3b20e61039a, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: PA clean scores events process
Job Context: 
#Tue May 14 11:46:13 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('pa_clean_scores').process();
Starting: metric update events process.edabc2e10a0a0b6000c2d4f10e8e67e0, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: metric update events process
Job Context: 
#Tue May 14 11:46:13 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('metric_update').process();
		
Completed: Push in 0:00:00.003, next occurrence is 05-14-2019 14:46:26
Completed: report view events process in 0:00:00.005, next occurrence is 05-14-2019 14:46:20
Completed: PA clean scores events process in 0:00:00.004, next occurrence is 05-14-2019 14:46:20
Completed: metric update events process in 0:00:00.005, next occurrence is 05-14-2019 14:46:20
[0:00:00.020] Compacting large row block (file.write: sn_hr_core_case 10000 rows 160000 saveSize)
[0:00:00.003] Expanding large row block (file.read: sn_hr_core_case, 10000 rows, 160000 dataSize)
Starting: SMTP Sender.d8e56792c0a80064007e86b882a6acc7, Trigger Type: Interval, Priority: 25, Upgrade Safe: true, Repeat: 1 Minute
Name: SMTP Sender
Job Context: 
#Tue May 14 11:45:55 PDT 2019

Script: 

Starting: report view events process.0fb2c110871321004ebe19fa84e3ecf8, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: report view events process
Job Context: 
#Tue May 14 11:46:23 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('report_view').process();
Starting: PA clean scores events process.a51441d1d70022004cd2a3b20e61039a, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: PA clean scores events process
Job Context: 
#Tue May 14 11:46:23 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('pa_clean_scores').process();
Starting: metric update events process.edabc2e10a0a0b6000c2d4f10e8e67e0, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: metric update events process
Job Context: 
#Tue May 14 11:46:23 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('metric_update').process();
		
Completed: SMTP Sender in 0:00:00.002, next occurrence is 05-14-2019 14:47:33
Completed: report view events process in 0:00:00.006, next occurrence is 05-14-2019 14:46:30
Completed: PA clean scores events process in 0:00:00.006, next occurrence is 05-14-2019 14:46:30
Completed: metric update events process in 0:00:00.006, next occurrence is 05-14-2019 14:46:30
Starting: report view events process.0fb2c110871321004ebe19fa84e3ecf8, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: report view events process
Job Context: 
#Tue May 14 11:46:33 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('report_view').process();
Starting: PA clean scores events process.a51441d1d70022004cd2a3b20e61039a, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: PA clean scores events process
Job Context: 
#Tue May 14 11:46:33 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('pa_clean_scores').process();
Starting: metric update events process.edabc2e10a0a0b6000c2d4f10e8e67e0, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: metric update events process
Job Context: 
#Tue May 14 11:46:33 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('metric_update').process();
		
Completed: PA clean scores events process in 0:00:00.006, next occurrence is 05-14-2019 14:46:40
Completed: report view events process in 0:00:00.007, next occurrence is 05-14-2019 14:46:40
Completed: metric update events process in 0:00:00.005, next occurrence is 05-14-2019 14:46:40
Starting: Push.4ed15122ff2331009738ffffffffff06, Trigger Type: Interval, Priority: 25, Upgrade Safe: true, Repeat: 10 Seconds
Name: Push
Job Context: 
#Tue May 14 11:46:43 PDT 2019

Script: 

Starting: events process 0.cdf76ab1dbf21300305efb051d9619c3, Trigger Type: Interval, Priority: 25, Upgrade Safe: true, Repeat: 10 Seconds
Name: events process 0
Job Context: 
#Tue May 14 11:46:43 PDT 2019
fcScriptName=javascript\:gs.processDelegatedEvents();

Script: 

Starting: events process 0.db414010db131300305efb051d961966, Trigger Type: Interval, Priority: 25, Upgrade Safe: true, Repeat: 10 Seconds
Name: events process 0
Job Context: 
#Tue May 14 11:46:43 PDT 2019
fcScriptName=javascript\:gs.processDelegatedEvents();

Script: 

Starting: Flow Engine Event Handler.0327aabddbb21300305efb051d961989, Trigger Type: Repeat, Priority: 100, Upgrade Safe: true, Repeat: 16 Seconds
Name: Flow Engine Event Handler
Job Context: 
#Tue May 14 11:46:33 PDT 2019

Script: 

Completed: Push in 0:00:00.003, next occurrence is 05-14-2019 14:46:56
Starting: report view events process.0fb2c110871321004ebe19fa84e3ecf8, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: report view events process
Job Context: 
#Tue May 14 11:46:43 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('report_view').process();
Completed: events process 0 in 0:00:00.005, next occurrence is 05-14-2019 14:46:54
Completed: events process 0 in 0:00:00.004, next occurrence is 05-14-2019 14:46:55
Completed: Flow Engine Event Handler in 0:00:00.005, next occurrence is 05-14-2019 14:47:01
Completed: report view events process in 0:00:00.005, next occurrence is 05-14-2019 14:46:50
Starting: Flow Engine Event Handler.4727aabddbb21300305efb051d961989, Trigger Type: Repeat, Priority: 100, Upgrade Safe: true, Repeat: 16 Seconds
Name: Flow Engine Event Handler
Job Context: 
#Tue May 14 11:46:33 PDT 2019

Script: 

Cache synchronize catalog @ 05-14-2019 14:46:43, for catalog: rw_cache, key: <none>:<p>:delete:f2f27cfcdb616b808f7b5df3ce9619fc, triggered by system_id: app130169.ytz3.service-now.com:cswgtest004
Cache synchronize catalog @ 05-14-2019 14:46:44, for catalog: jrobin_databases, key: client_transaction_response_time.603afa131b13230000af33fccd4bcbac, triggered by system_id: app130169.ytz3.service-now.com:cswgtest004
Completed: Flow Engine Event Handler in 0:00:00.005, next occurrence is 05-14-2019 14:47:01
Starting: Flow Engine Event Handler.4f27aabddbb21300305efb051d961989, Trigger Type: Repeat, Priority: 100, Upgrade Safe: true, Repeat: 16 Seconds
Name: Flow Engine Event Handler
Job Context: 
#Tue May 14 11:46:33 PDT 2019

Script: 

Starting: Flow Engine Event Handler.4b27aabddbb21300305efb051d961989, Trigger Type: Repeat, Priority: 100, Upgrade Safe: true, Repeat: 16 Seconds
Name: Flow Engine Event Handler
Job Context: 
#Tue May 14 11:46:33 PDT 2019

Script: 

Starting: Flow Engine Event Handler.c327aabddbb21300305efb051d961989, Trigger Type: Repeat, Priority: 100, Upgrade Safe: true, Repeat: 16 Seconds
Starting: PA clean scores events process.a51441d1d70022004cd2a3b20e61039a, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: Flow Engine Event Handler
Job Context: 
#Tue May 14 11:46:33 PDT 2019

Script: 

Name: PA clean scores events process
Job Context: 
#Tue May 14 11:46:43 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('pa_clean_scores').process();
Starting: Flow Engine Event Handler.c727aabddbb21300305efb051d961989, Trigger Type: Repeat, Priority: 100, Upgrade Safe: true, Repeat: 16 Seconds
Name: Flow Engine Event Handler
Job Context: 
#Tue May 14 11:46:33 PDT 2019

Script: 

Completed: Flow Engine Event Handler in 0:00:00.005, next occurrence is 05-14-2019 14:47:01
Completed: Flow Engine Event Handler in 0:00:00.005, next occurrence is 05-14-2019 14:47:01
Completed: Flow Engine Event Handler in 0:00:00.004, next occurrence is 05-14-2019 14:47:01
Completed: PA clean scores events process in 0:00:00.005, next occurrence is 05-14-2019 14:46:50
Starting: Flow Engine Event Handler.cf27aabddbb21300305efb051d961989, Trigger Type: Repeat, Priority: 100, Upgrade Safe: true, Repeat: 16 Seconds
Name: Flow Engine Event Handler
Job Context: 
#Tue May 14 11:46:33 PDT 2019

Script: 

Completed: Flow Engine Event Handler in 0:00:00.005, next occurrence is 05-14-2019 14:47:01
Starting: Flow Engine Event Handler.cb27aabddbb21300305efb051d961989, Trigger Type: Repeat, Priority: 100, Upgrade Safe: true, Repeat: 16 Seconds
Name: Flow Engine Event Handler
Job Context: 
#Tue May 14 11:46:33 PDT 2019

Script: 

Completed: Flow Engine Event Handler in 0:00:00.005, next occurrence is 05-14-2019 14:47:01
Starting: metric update events process.edabc2e10a0a0b6000c2d4f10e8e67e0, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: metric update events process
Job Context: 
#Tue May 14 11:46:43 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('metric_update').process();
		
Completed: Flow Engine Event Handler in 0:00:00.005, next occurrence is 05-14-2019 14:47:01
Completed: metric update events process in 0:00:00.005, next occurrence is 05-14-2019 14:46:50
[0:00:00.016] Compacting large row block (file.write: sn_hr_core_case 10000 rows 160000 saveSize)
[0:00:00.001] Expanding large row block (file.read: sn_hr_core_case, 1666 rows, 26656 dataSize)
Starting: events process 0.cdf76ab1dbf21300305efb051d9619c3, Trigger Type: Interval, Priority: 25, Upgrade Safe: true, Repeat: 10 Seconds
Name: events process 0
Job Context: 
#Tue May 14 11:46:46 PDT 2019
fcScriptName=javascript\:gs.processDelegatedEvents();

Script: 

Starting: events process 0.db414010db131300305efb051d961966, Trigger Type: Interval, Priority: 25, Upgrade Safe: true, Repeat: 10 Seconds
Name: events process 0
Job Context: 
#Tue May 14 11:46:46 PDT 2019
fcScriptName=javascript\:gs.processDelegatedEvents();

Script: 

Starting: Push.4ed15122ff2331009738ffffffffff06, Trigger Type: Interval, Priority: 25, Upgrade Safe: true, Repeat: 10 Seconds
Name: Push
Job Context: 
#Tue May 14 11:46:46 PDT 2019

Script: 

Starting: report view events process.0fb2c110871321004ebe19fa84e3ecf8, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: report view events process
Job Context: 
#Tue May 14 11:46:52 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('report_view').process();
Starting: PA clean scores events process.a51441d1d70022004cd2a3b20e61039a, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: PA clean scores events process
Job Context: 
#Tue May 14 11:46:52 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('pa_clean_scores').process();
Completed: events process 0 in 0:00:00.005, next occurrence is 05-14-2019 14:47:03
Starting: metric update events process.edabc2e10a0a0b6000c2d4f10e8e67e0, Trigger Type: Interval, Priority: 100, Upgrade Safe: false, Repeat: 5 Seconds
Name: metric update events process
Job Context: 
#Tue May 14 11:46:52 PDT 2019
fcScriptName=in the schedule record

Script: 
GlideEventManager('metric_update').process();
		
Completed: Push in 0:00:00.003, next occurrence is 05-14-2019 14:47:06
Completed: events process 0 in 0:00:00.004, next occurrence is 05-14-2019 14:47:05
Completed: report view events process in 0:00:00.006, next occurrence is 05-14-2019 14:47:00
Completed: PA clean scores events process in 0:00:00.005, next occurrence is 05-14-2019 14:47:00
Completed: metric update events process in 0:00:00.005, next occurrence is 05-14-2019 14:47:00
[0:01:07.844] Total Time

Does the above mean, it would work -or is this something different?

Thanks,

-Rob

[Ron K2]

That is a lot of output! I'm not sure what this means either...

I believe you can just place that whole listed filter

("active=false^hr_service=20dfddafdb7a57403171dd1b5e9619c7^short_descriptionLIKEWorkday Password Reset^topic_category=8234dff4dbcd53403171dd1b5e961955^topic_detail=fd445778dbcd53403171dd1b5e961966" )

in one query statement (or you can use it with addEncodedQuery).

I think it would help to place a row count statement right after the query to just see if you are pulling the correct cases and go from there. 

I'm glad to help if I can!

Ron

[Rob Sestito]

Hey Ron!

I used the query within an addEncodedQuery - I believe I do have a rowCount line code in here already gs.info('fixLegacyMiscCases: I would fix ' + gr.getRowCount() + " records."); which is coded right after the gr.query.

I am still getting that output I showed you earlier - not as large, but still decent amount.

Not sure why this isn't working.. I have done a fix script similar (but only used one field to update) that worked just fine. This one is giving us a run for its money LOL!

-Rob

[Rob Sestito · Accepted solution]

I learned that the output I was getting (while large), was simply just telling me that the script was running. I attacked a small number of Cases within my fix script, and it did what I needed - updated those Cases as I needed them to be. Hit the right Cases and changed what I wanted corrected within my script.

Thanks everyone for the help here!

Closing post as the script is working correctly.

-Rob