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]

The script you originally posted should never be run as a business rule against the user table...and really not in a business rule anywhere.  It should only be run as a background script.  This single line could be run in a business rule against the user table.

current.time_zone = current.location.time_zone

Based on your original post, I assumed that the script you were running had to be against the location table (since that's where you described the update taking place).

The following condition should work to initiate a time zone change from the location record any time the location field changes and is not empty.

current.location.changes() && !current.location.nil()

[jas101]

Hi Mark. No sorry, the 'rogue' BR (since made inactive) was being run against the sys_user table (insert/update)! Which is why I'm not sure why we hadn't experienced the issue/outage before tbh (?).

I'm continuing with some testing but so far using the below as a background script to bulk update users when a Time zone is added to a Location record:

gs.log("Set Users Time Zone> START " + gs.now());
var gr = new GlideRecord('sys_user');
gr.addQuery('active',true);  
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());

and the following as a before insert/update sys_user business rule (to account for subsequent updates) seems to be working OK.

Please let me know if you see anything out of place with either (I just added an 'Active' query to the background script) and added conditions to the BR to account for updates if a Time zone is not set that could be or when user location gets updated (and a time zone for that location is available).

[Mark Stanger]

Nice.  It looks like you've got some good conditions added in that business rule and your background script.  I don't see any problems with either of those.

So, to summarize, I think there are 2 reasons why your previous configuration could cause issues with your system.

1)  Your script was running on updates to user records...and then going through and attempting to update all user records in your system with a new time zone.  That script should never be run as a business rule...let alone a business rule responding to updates on the 'sys_user' table.

2)  Even with the problem above, the system should never have an issue with updating a few hundred records unless there are some other things being triggered.  Adding a 'setWorkflow(false)' flag to your GlideRecord update ensures that you don't end up with any other business rule (or system engine) running in response to that update (including an endless loop from #1).

I'm glad you've got this working!  Please mark this answer as correct if I've answered your question.

[jas101]

Thanks Mark, just a quick follow-up question on the background script please.

Would it make sense to modify it so the query/command is only confined to users who don't have the system time zone already (i.e. no time zone set)?

In other words is the current version (below) set to update all user records where the user's location record has a time zone value regardless of whether they already have one?

I'm interpreting the code as if this is the case but I notice when testing if I set a user's time zone manually and then update the relevant Location.Time zone and then run the script, the updated time on the manually updated user remains as it is (and not the Updated time of those that were updated by the script) but I thought I would check this with you?

This is obviously all with a view to minimizing load on the system each time the script is run. I've found that currently an update involving e.g. 1400 user records is taking approx. 20 seconds which seems reasonable to me but I wonder if I was to update many location records (we have a lot) and then run the script would I potentially run into problems?

gs.log("Set Users Time Zone> START " + gs.now());
var gr = new GlideRecord('sys_user');
gr.addQuery('active',true);  
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());

[Mark Stanger]

It would probably optimize it quite a bit since I'd guess most people don't take the time to adjust their own time zone in their user preferences.  If that's a setup that works process and data-wise for your organization I'd add the additional condition.  As long as the 'setWorkflow' flag is in there though you should be kept from any significant performance issue.  Your import sets are probably handling updates to thousands of records a day without even breaking a sweat so this is really a very marginal increase as long as you isolate it correctly.

Please mark my answer above as correct if I've answered your question.