Setting user time zones in bulk / via location time zone (safely / without causing an instance outage!)

jas101 · ‎08-15-2018

Hi guys, need advice on the recommended method to set time zone values to users (in bulk and/or via location record time zone value). This is being asked in light of a Location.Time zone value update inadvertently causing a SN instance outage yesterday (not good!).

It appears that an insert/update sys_user business rule (implemented by consultants when we rolled out SN), 'Set Time Zone from Location', sets users' time zone values when a location time zone is updated.

gs.log("Set Users Time Zone> START " + gs.now());
var gr = new GlideRecord('sys_user');
gr.query();
while (gr.next()) {
   if (!gr.location.time_zone.nil()) {
      gr.time_zone = gr.location.time_zone;
      gr.update();
   }
}
gs.log("Set Users Time Zone> END" + gs.nowDateTime());

So yesterday when the 'New York' location record was updated with a time zone value, 'US/Eastern' (from not having one specified), the subsequent update of 787 user records with location 'New York' appears to have crashed SN.

We have noticed that many location records have no time zone set so we would like to know recommended (safe!) way of updating these / applying in bulk, the correct time zone values, to the relevant user records. Presumably the location records have a 'Time zone' field to allow easier management of user time zone values right.

Should the above script maybe be run as an on demand scheduled job initially and then the business rule should be modified to only run on change of user.location?

Thank-you for any help in advance.

Mark Stanger · ‎08-15-2018

There's a lot to unpack here, and much of the solution depends on what data you've got available.

You're correct that the above script would be better suited to be run as a background script for a one-time update of user time zones. If it's running as a business rule whenever a location record is inserted or updated, it will always try to update every single user in the system...regardless of whether or not they are associated to the location record that is changing.

The 'if' condition will be met if the user's location time zone is not empty (which I think is what you want - no sense in setting a time zone that isn't there).

The script I responded with should set the time zone for all users if they're associated with a location that has a time zone. It should be run as a background script.

A 'before' insert business rule with the following line in the script field should keep the time zone updated correctly.

current.time_zone = current.location.time_zone

You'll probably also want to make sure it only runs if the time zone isn't currently set by adding a condition.

The reason I mentioned that it depends largely on your data is that these scripts will only be effective for users associated to locations that have time zones set. Without that, you won't have anything change. If you're not seeing user time zones updated, you'll want to run a report against the user table to show users with no location and users with no location.time_zone so you can see where your data needs to be updated.

View solution in original post

jas101 · ‎08-30-2018

Thanks Mark and apologies for delay, so would the correct query so the background script is only confined to users who don't have the system time zone already (i.e. no time zone set) be as below?

var gr = new GlideRecord('sys_user');

gr.addQuery ('time_zone', nil);

Mark Stanger · ‎08-30-2018

You can just check for an empty string like this...

var gr = new GlideRecord('sys_user');
gr.addQuery ('time_zone', '');

Mark Stanger · ‎08-27-2018

Just checking on on this one. Has this question been answered or is there more information I can help you with? If it's been answered, please mark the answer above as the correct one so people know this has been taken care of. Thanks!