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

[jas101]

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]

This update alone shouldn't cause anything to crash.  I'd guess what happened is that the 787 user records got updated and then some other business rule(s) on the 'sys_user' table ran and caused the crash.

This should be easy to prevent for this specific case though because I wouldn't think that it would be necessary to run business rules for user records when their time zone changes...at least from the location record.  Here is a modified version of your script that updates the user records without running any business rules in response to the user update.  You can give this a test in your dev instance to be sure.

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.setWorkflow(false); // Don't run business rules for this update
      gr.update();
   }
}
gs.log("Set Users Time Zone> END" + gs.nowDateTime());

[jas101]

Hi Mark, appreciate the quick reply. From research on other Community posts (including one linked below) it seems to be that the intention was not to use the above as a business rule (BR) but as a scheduled job (SJ) - is there anything inherently wrong with using it as a BR though?

It's been suggested here https://community.servicenow.com/community?id=community_question&sys_id=84b00765db98dbc01dcaf3231f961972

that this simple BR on change of user location may be best and that does make sense to me: current.time_zone = current.location.time_zone

Does it seem like a better approach to you? If so I guess we just need to establish the best way to update all the location records which don't have a time zone value (the majority of them currently), and subsequently update the user records that do not have a time zone value (two-thirds of all active user records). Best done by SJ?

As queried in above thread reply, does this line in our current BR:

 if (!gr.location.time_zone.nil())

not actually mean if the user's location's time zone does NOT = empty, make the update (match user timezone to user location timezone)? I.e. instead of looking for user's who do not have a time zone value it is actually doing the reverse by looking for user's who do have one?

Thanks for your input.

[Mark Stanger · Accepted solution]

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.

[jas101]

Thanks Mark, I will work through your suggestions in a sub-Prod instance when able.

Can I please check for now where you said:

"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."

Why is the above in bold true if the current BR is based on the sys_user table? I'm still struggling somewhat to understand why updating the Location.Time zone had such an adverse impact when in theory should it not have also been happening anytime a user record which had a location time zone was inserted or updated?

In terms of this part:

"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."

Would if 'User.Location changes' not be the ideal condition (providing Locations all have time zone values by then) as otherwise if we only set the User.Time zone when it isn't set already (as per above suggestion),  wouldn't this mean a User.Location update of a user record that has a User.Time zone already populated would not change to the correct time zone (of the new location)? Sorry if I am misunderstanding (most likely).