GlideRecord Mass update

[dmfranko]

I need to got back and recalculate a field for all resolved and closed incidents in my system, which is around 70k.   I've got something like the script below.   The problem is when I try to run it, takes a long time as somewhat expected and it also complains that it's a large dataset.   Are there any other options besides chunking this out via query?

r = new GlideRecord('incident')
r.addEncodedQuery("state=6^ORstate=7^opened_at<javascript:gs.dateGenerate('2016-01-14','20:00:00')")
r.query();
while(r.next()){
   var opened = r.opened_at;
   var resolved = r.resolved_at;
   var dc = new DurationCalculator();
   dc.setSchedule(gs.getProperty('incident.schedule'));
   var durSched = dc.calcScheduleDuration(opened,resolved);
   r.business_duration.setDateNumericValue(durSched*1000)
   r.business_stc = durSched;
   r.update();
}

[poyntzj]

not really, if they are closed look to add

r.autoSysFields(false);

r.setWorlflow(false);

so that the autosysfields and any work flows do not get updated / processed

[Stijn Verhulst3]

Hi Dan,

this should do the trick:

var runQuery = true;
var numbRec   = 0;
var totalRec = 0;


// Search for incidents. A limited number of records is set to keep the process under control.
// IMPORTANT NOTE: In case this script throws up the error "Compacting large row block", lower the 'setLimit()' below.
var inc = new GlideRecord('incident');


inc.setLimit(5000);
inc.addEncodedQuery("state=6^ORstate=7^opened_at<javascript:gs.dateGenerate('2016-01-14','20:00:00')");


inc.query();


// Start looping through the incidents (one loop covers the set limit above).
while (runQuery) {

   // Take the appropriate action on each of the found incidents and add 1 to the total number of processed records for each incident.
   while (inc.next()) {

   var opened = inc.opened_at;
   var resolved = inc.resolved_at;

   var dc = new DurationCalculator();
   dc.setSchedule(gs.getProperty('incident.schedule'));
   var durSched = dc.calcScheduleDuration(opened,resolved);

   inc.business_duration.setDateNumericValue(durSched*1000);
   inc.business_stc = durSched;
   inc.update();

   totalRec += 1;
   }

   // Search for the next limited number of incidents which met the same criteria.
   // In case at least 1 new has been found, restart the loop. If none has been found, stop the loop and print out debugging/logging information.
   inc.query();
   numbRec = inc.getRowCount();

   if (numbRec > 0) {
   runQuery = true;
   } else {
   runQuery = false;
   if (totalRec > 0) {
   gs.log("Script execution ::: " + totalRec + " incidents have been processed.");
   } else {
   gs.log("Script execution ::: no incidents have been processed.");
   }
   }

}

We make use of the setLimit functionality: GlideRecord - ServiceNow Wiki

It allows us to walk through subsets of data while manipulating the latter. This way you normally shouldn't receive any timeouts or update / removal problems executed by the script.

This script can be applied by copy-pasting it into the Scripts - Background module which you can access if you've activated the elevated privilege security_admin via the lock icon in the banner frame. (Or in case the security_admin user role is not an elevated privilege and you already have this role assigned to you.)

Kind regards,

Stijn

[Maheshwar Elang]

Hi Stijn/Dan,

If its such a huge mass update, I would suggest move all your code under Script Actions of an event.

And trigger just the event from your side.

So your script logic can be the same. Just move it to events for the records to be updated asynchronously.

You can monitor your triggered events under "All Active Transactions".

This ought to do the job.

Let me know if it works.

[Stijn Verhulst3]

Hi

Working with a Script Action of an event can also be done, but than you have to create a new event as well as a Scripit Action which would be from a practical perspective handy in case you have to perform the same action multiple times or on a scheduled base in the future.

I assume Dan is talking about a 1-time operation and in that case you can apply the script I've posted here once via the Scripts - Background module which will work fine as well. Applied this multiple times for various operations in customoer environments. Also I'd like to do it in 1 move as I keep track straight away whether the actions are actually applied.

Stijn