Month: July 2023

Periodic Review, Part VII

Posted on | by snhackery

“Whether you want to uncover the secrets of the universe, or you just want to pursue a career in the 21st century, basic computer programming is an essential skill to learn.”
— Stephen Hawking

Last time, we got started on our script that will run the review process by building and testing the primary function that finds all of the configurations that will need to run. Today we need to continue on with the script, beginning with the process for a single configuration’s execution. Before we get started, though, we need to talk a little bit about making sure that the notice will go to somebody.

Both the configuration record and the system as a whole have a default recipient/email address. The system default email address is contained in a System Property and the configuration default recipient is on the configuration record. There is a field on the source record for the recipient, and if that recipient record is valid and active and contains an email address, then that will be the address that we will want to use. If not, then we will want to use the configuration fallback recipient, and if there is some issue in the notice process with that recipient’s email address, then we will end up using the system fallback address. This is to ensure that the notice will actually get to a real person who can take action on the review request.

There is also another optional System Property that can be used to limit notifications to a specific email domain. If that value is present, and the recipient email is not an email address of that domain, then the fallback recipient will be used as well. All of this should be documented in the notice item record. So the first thing that we will want to do will be to pull those System Properties.

// fetch system properties
var systemFallbackAddress = gs.getProperty('x_11556_periodic_r.fallback_email_address');
var systemEmailDomain = gs.getProperty('x_11556_periodic_r.email_domain');

The next thing that we will want to do will be to create a record in the Review Execution table to memorialize this execution.

// create execution record
var executionGR = new GlideRecord('x_11556_periodic_r_review_execution');
executionGR.configuration = configuration;
executionGR.run_date = new GlideDate();
executionGR.run_start = new GlideDateTime();
executionGR.state = 'Running';
executionGR.short_description = executionGR.getDisplayValue('run_date') + ' review notices';
executionGR.total_items = 0;
executionGR.total_notices = 0;
executionGR.completion_code = 0;
executionGR.insert();

This is standard GlideRecord stuff, so we don’t really need to go into a lot of detail here other than to say that we link it back to the configuration that is being executed and initialize all of the other values. Once we have our execution record created, we can pull the configuration record from the reference, and once we have it, we can set up the fallback recipient for this configuration.

var configurationGR = executionGR.configuration.getRefRecord();
var fallbackRecipient = configurationGR.getDisplayValue('fallback_recipient');

Now we need to go fetch all of the items that need to be reviewed this cycle, but because we want to consolidate all of the items for any given recipient into a single notice, we will first need to fetch them all, and then once we have the full list, group them by recipient. To facilitate that, we can create a temporary notice record and assign all of the items to that notice, and once we run through all of the query results, we can then fetch them back by recipient. So let’s create that temporary notice record now.

// create temporary notice record
var noticeGR = new GlideRecord('x_11556_periodic_r_review_notice');
noticeGR.review_execution = executionGR.getUniqueValue();
noticeGR.recipient = configurationGR.fallback_recipient;
noticeGR.short_description = 'Temporary notice record';
noticeGR.insert();
var tempNotice = noticeGR.getUniqueValue();

Once we have our notice record, we can use the table and filter from the configuration record to find all of the items to be reviewed.

// create notice item records from source table data
var noticeItemGR = new GlideRecord('x_11556_periodic_r_review_notice_item');
var itemCt = 0;
var sourceGR = new GlideRecord(table);
if (sourceGR.isValid()) {
	if (filter) {
		sourceGR.addEncodedQuery(filter);
	}
	sourceGR.orderBy(configurationGR.short_description_column);
	sourceGR.query();
	while (sourceGR.next()) {
		...
	}
	...
} else {
	...
}

Then, inside the query loop, we can build a record in the Review Notice Item table for each item.

noticeItemGR.initialize();
noticeItemGR.review_notice = tempNotice;
noticeItemGR.id = sourceGR.getUniqueValue();
noticeItemGR.short_description =  sourceGR.getDisplayValue(configurationGR.short_description_column);
noticeItemGR.description = sourceGR.getDisplayValue(configurationGR.description_column);
noticeItemGR.recipient = sourceGR.getValue(configurationGR.recipient_column);
noticeItemGR.insert();
itemCt++;

Once the notice item record has been created, we can inspect the recipient and come up with a valid recipient to which we will send the notice.

var recipientGR = new GlideRecord('sys_user');
recipientGR.get(noticeItemGR.getValue('recipient'));
var notes = '';
if (recipientGR.isValid()) {
	if (recipientGR.getValue('active')) {
		var email = recipientGR.getDisplayValue('email');
		if (email) {
			if (systemEmailDomain && !email.endsWith(systemEmailDomain)) {
				notes = 'Recipient email address is not an authoized email address; reverting to fallback recipient';
			}
		} else {
			notes = 'Specified recipient has no email address; reverting to fallback recipient';
		}
	} else {
		notes = 'Specified recipient is not active; reverting to fallback recipient';
	}
} else {
	notes = 'Recipient column empty on source record; reverting to fallback recipient';
}
if (notes) {
	noticeItemGR.notes = notes;
	noticeItemGR.recipient = fallbackRecipient;
	noticeItemGR.update();
}

Once we complete the loop, we will want to see if the query returned any items, and either process the items or close out this execution.

if (itemCt > 0) {
	executionGR.total_items = itemCt;
	executionGR.update();
	this.sendNotices(configurationGR, executionGR, noticeGR, noticeItemGR, tempNotice);
} else {

	// finalize the execution
	executionGR.total_items = 0;
	executionGR.total_notices = 0;
	executionGR.run_end = new GlideDateTime();
	executionGR.state = 'Complete';
	executionGR.completion_code = 0;
	executionGR.description = 'No items matched the filter criteria during this run, so no notices were sent out.';
	executionGR.update();
}

And once that is done, whether we had any items returned or not, we will want to delete that temporary notice record.

// delete the temporary notice
noticeGR.get(tempNotice);
noticeGR.deleteRecord();

One last thing: we need to do something in the event that we find ourselves in the else branch of the if (sourceGR.isValid()) { conditional. This basically means that the source table specified on the configuration record is not valid and so we cannot look for any items there. This is basically a failure of the execution, so we need to update the execution record to reflect that.

// finalize the execution
executionGR.total_items = 0;
executionGR.total_notices = 0;
executionGR.run_end = new GlideDateTime();
executionGR.state = 'Failed';
executionGR.completion_code = 1;
executionGR.description = 'The specified source table in the configuration record is not valid; execution cannot proceed; aborting execution.';
executionGR.update();

Failure is not an option that we originally set up for that field, so we will have to go back into the dictionary at some point and fix that to make that code work. Finally, regardless of how the execution turned out, we need to update the configuration record with the next run date, which is calculated in the function that we added for that purpose earlier.

// set the next run date
configurationGR.setValue('next_scheduled_date', this.calculateNextRunDate(configurationGR));
configurationGR.update();

With the addition of this completed function, our utility Script Include now looks like this.

var PeriodicReviewUtils = Class.create();
PeriodicReviewUtils.prototype = {
    initialize: function() {
    },

	dailyProcess: function() {
		var toRun = [];
		var configurationGR = new GlideRecord('x_11556_periodic_r_review_configuration');
		var today = new GlideDate();
		configurationGR.addQuery('next_scheduled_date', today);
		configurationGR.orderBy('number');
		configurationGR.query();
		while (configurationGR.next()) {
			var execution = {};
			execution.configuration = configurationGR.getUniqueValue();
			execution.table = configurationGR.getDisplayValue('table');
			execution.filter = configurationGR.getDisplayValue('filter');
			toRun.push(execution);
		}
		if (toRun.length > 0) {
			gs.info('PeriodicReviewUtils.dailyProcess: Running ' + toRun.length + ' execution(s) today.');
			for (var i in toRun) {
				var thisRun = toRun[i];
				this.processExecution(thisRun.configuration, thisRun.table, thisRun.filter);
			}
			gs.info('PeriodicReviewUtils.dailyProcess: ' + toRun.length + ' execution(s) completed.');
		} else {
			gs.info('PeriodicReviewUtils.dailyProcess: Nothing scheduled to run today.');
		}
	},

	processExecution: function(configuration, table, filter) {
		gs.info('PeriodicReviewUtils.dailyProcess: ' + configuration + '; ' + table + '; ' + filter);

		// fetch system properties
		var systemFallbackAddress = gs.getProperty('x_11556_periodic_r.fallback_email_address');
		var systemEmailDomain = gs.getProperty('x_11556_periodic_r.email_domain');

		// create execution record
		var executionGR = new GlideRecord('x_11556_periodic_r_review_execution');
		executionGR.configuration = configuration;
		executionGR.run_date = new GlideDate();
		executionGR.run_start = new GlideDateTime();
		executionGR.state = 'Running';
		executionGR.short_description = executionGR.getDisplayValue('run_date') + ' review notices';
		executionGR.total_items = 0;
		executionGR.total_notices = 0;
		executionGR.completion_code = 0;
		executionGR.insert();
		var configurationGR = executionGR.configuration.getRefRecord();
		var fallbackRecipient = configurationGR.getDisplayValue('fallback_recipient');

		// create temporary notice record
		var noticeGR = new GlideRecord('x_11556_periodic_r_review_notice');
		noticeGR.review_execution = executionGR.getUniqueValue();
		noticeGR.recipient = configurationGR.fallback_recipient;
		noticeGR.short_description = 'Temporary notice record';
		noticeGR.insert();
		var tempNotice = noticeGR.getUniqueValue();

		// create notice item records from source table data
		var noticeItemGR = new GlideRecord('x_11556_periodic_r_review_notice_item');
		var itemCt = 0;
		var sourceGR = new GlideRecord(table);
		if (sourceGR.isValid()) {
			if (filter) {
				sourceGR.addEncodedQuery(filter);
			}
			sourceGR.orderBy(configurationGR.short_description_column);
			sourceGR.query();
			while (sourceGR.next()) {
				noticeItemGR.initialize();
				noticeItemGR.review_notice = tempNotice;
				noticeItemGR.id = sourceGR.getUniqueValue();
				noticeItemGR.short_description =  sourceGR.getDisplayValue(configurationGR.short_description_column);
				noticeItemGR.description = sourceGR.getDisplayValue(configurationGR.description_column);
				noticeItemGR.recipient = sourceGR.getValue(configurationGR.recipient_column);
				noticeItemGR.insert();
				itemCt++;
				var recipientGR = new GlideRecord('sys_user');
				recipientGR.get(noticeItemGR.getValue('recipient'));
				var notes = '';
				if (recipientGR.isValid()) {
					if (recipientGR.getValue('active')) {
						var email = recipientGR.getDisplayValue('email');
						if (email) {
							if (systemEmailDomain && !email.endsWith(systemEmailDomain)) {
								notes = 'Recipient email address is not an authoized email address; reverting to fallback recipient';
							}
						} else {
							notes = 'Specified recipient has no email address; reverting to fallback recipient';
						}
					} else {
						notes = 'Specified recipient is not active; reverting to fallback recipient';
					}
				} else {
					notes = 'Recipient column empty on source record; reverting to fallback recipient';
				}
				if (notes) {
					noticeItemGR.notes = notes;
					noticeItemGR.recipient = fallbackRecipient;
					noticeItemGR.update();
				}
			}
			if (itemCt > 0) {
				executionGR.total_items = itemCt;
				executionGR.update();
				this.sendNotices(configurationGR, executionGR, noticeGR, noticeItemGR, tempNotice);
			} else {

				// finalize the execution
				executionGR.total_items = 0;
				executionGR.total_notices = 0;
				executionGR.run_end = new GlideDateTime();
				executionGR.state = 'Complete';
				executionGR.completion_code = 0;
				executionGR.description = 'No items matched the filter criteria during this run, so no notices were sent out.';
				executionGR.update();
			}

			// delete the temporary notice
			noticeGR.get(tempNotice);
			noticeGR.deleteRecord();
		} else {
			// finalize the execution
			executionGR.total_items = 0;
			executionGR.total_notices = 0;
			executionGR.run_end = new GlideDateTime();
			executionGR.state = 'Failed';
			executionGR.completion_code = 1;
			executionGR.description = 'The specified source table in the configuration record is not valid; execution cannot proceed; aborting execution.';
			executionGR.update();
		}

		// set the next run date
		configurationGR.setValue('next_scheduled_date', this.calculateNextRunDate(configurationGR));
		configurationGR.update();
	},

	calculateNextRunDate: function(configurationGR) {
		var runDate = new Date(configurationGR.getDisplayValue('next_scheduled_date'));
		var frequency = configurationGR.getValue('frequency');
		var days = 0;
		var months = 0;
		if (frequency == 'daily') {
			days = 1;
		} else if (frequency == 'weekly') {
			days = 7;
		} else if (frequency == 'biweekly') {
			days = 14;
		} else if (frequency == 'monthly') {
			months = 1;
		} else if (frequency == 'bimonthly') {
			months = 2;
		} else if (frequency == 'quarterly') {
			months = 3;
		} else if (frequency == 'semiannually') {
			months = 6;
		} else if (frequency == 'annually') {
			months = 12;
		} else if (frequency == 'biannually') {
			months = 24;
		}
		if (days > 0) {
			runDate.setDate(runDate.getDate() + days);
		} else {
			runDate.setMonth(runDate.getMonth() + months);
		}
		return JSON.stringify(runDate).substring(1, 11);
	},

    type: 'PeriodicReviewUtils'
};

Next time, we will stub out that sendNotices function and give things another test or two, and then actually build out that function to group the items by recipient and send out the notices.

Periodic Review, Part VI

Posted on | by snhackery

“One man’s crappy software is another man’s full time job.”
— Jessica Gaston

Last time, we wrapped up the work on creating all of the tables that we will need to support the review process. Now it is time to create that process using those tables. Before we jump into that, though, we should talk a little bit about how the process should work.

There are a couple of different ways to go about things that occur on a periodic basis. One way is to handle things the way most US states handle the renewals for driver’s licenses: each driver has their own renewal date based on something like their birthday or the day on which they originally got their license. Another way would be to handle things like the IRS does for federal income taxes: every person has the same date each year, sometime in the middle of April. One way spreads out the work across the entire year and the other results in all of the work coming at once at a particular time. Our process should be able to handle both approaches, and between the frequency value and the filter value in the configuration record, there should be a way to set things out to work out for either one.

Speaking of the frequency field, we should probably set up a list of specific choices for that guy so that our code knows what to expect. We can do that in the Choices tab of the Related Lists on the Dictionary Entry form of the frequency field.

Choice list for configuration frequency field

We may come up with other options in the future, but this list will do for now. The main purpose of this field is so that the process can establish the date of the next run for this particular configuration. Now that we know the values, we can create a simple function to set the next run date based on the current run date and the specified frequency.

calculateNextRunDate: function(configurationGR) {
	var runDate = new Date(configurationGR.getDisplayValue('next_scheduled_date'));
	var frequency = configurationGR.getValue('frequency');
	var days = 0;
	var months = 0;
	if (frequency == 'daily') {
		days = 1;
	} else if (frequency == 'weekly') {
		days = 7;
	} else if (frequency == 'biweekly') {
		days = 14;
	} else if (frequency == 'monthly') {
		months = 1;
	} else if (frequency == 'bimonthly') {
		months = 2;
	} else if (frequency == 'quarterly') {
		months = 3;
	} else if (frequency == 'semiannually') {
		months = 6;
	} else if (frequency == 'annually') {
		months = 12;
	} else if (frequency == 'biannually') {
		months = 24;
	}
	if (days > 0) {
		runDate.setDate(runDate.getDate() + days);
	} else {
		runDate.setMonth(runDate.getMonth() + months);
	}
	return JSON.stringify(runDate).substring(1, 11);
}

Of course, we won’t need that until the end of the run, as that will be one of the last things that we will do before we move on to the next configuration that we will be running today. So let’s back things up a bit and look at things from a little higher perspective.

To run the review process, we will set up a Scheduled Job that will run once a day, presumably at some time where the demand on the system is lower, and that job will call a Script Include function that will do all of the heavy lifting. That function will then query the database for any configuration records where the next run date is the current date. For each returned record, the process will use the filter field on the configuration record to query the table specified on the configuration record to obtain all of the items up for review. Next we will want to organize all of the items by recipient and send each recipient a notice that contains all of the items up for review for which that recipient is responsible. To house all of the functions required to perform all of thee tasks, we can create a single utility Script Include and call it PeriodicReviewUtils.

New utility Script Include

We’ll call our first function dailyProcess set up an object array called toRun and populate it with data from the returned configuration records. Then we will spin through the array and pass the values from that object to another function called processExecution that will handle the process for a single configuration.

dailyProcess: function() {
	var toRun = [];
	var configurationGR = new GlideRecord('x_11556_periodic_r_review_configuration');
	var today = new GlideDate();
	configurationGR.addQuery('next_scheduled_date', today);
	configurationGR.orderBy('number');
	configurationGR.query();
	while (configurationGR.next()) {
		var execution = {};
		execution.configuration = configurationGR.getUniqueValue();
		execution.table = configurationGR.getDisplayValue('table');
		execution.filter = configurationGR.getDisplayValue('filter');
		toRun.push(execution);
	}
	if (toRun.length > 0) {
		gs.info('PeriodicReviewUtils.dailyProcess: Running ' + toRun.length + ' execution(s) today.');
		for (var i in toRun) {
			var thisRun = toRun[i];
			this.processExecution(thisRun.configuration, thisRun.table, thisRun.filter);
		}
		gs.info('PeriodicReviewUtils.dailyProcess: ' + toRun.length + ' execution(s) completed.');
	} else {
		gs.info('PeriodicReviewUtils.dailyProcess: Nothing scheduled to run today.');
	}
}

I like to test things as I go along, just to make sure that I didn’t fat finger something along the way, and we can stop and do that here if we temporarily stub out the processExecution function to just spit out the values that were passed to it. That would make it look something like this:

processExecution: function(configuration, table, filter) {
	gs.info('PeriodicReviewUtils.dailyProcess: ' + configuration + '; ' + table + '; ' + filter);
}

We only have the one configuration record that we set up for Service Accounts right now, but that should be enough to validate the code that we have so far, which currently looks like this.

var PeriodicReviewUtils = Class.create();
PeriodicReviewUtils.prototype = {
    initialize: function() {
    },

	dailyProcess: function() {
		var toRun = [];
		var configurationGR = new GlideRecord('x_11556_periodic_r_review_configuration');
		var today = new GlideDate();
		configurationGR.addQuery('next_scheduled_date', today);
		configurationGR.orderBy('number');
		configurationGR.query();
		while (configurationGR.next()) {
			var execution = {};
			execution.configuration = configurationGR.getUniqueValue();
			execution.table = configurationGR.getDisplayValue('table');
			execution.filter = configurationGR.getDisplayValue('filter');
			toRun.push(execution);
		}
		if (toRun.length > 0) {
			gs.info('PeriodicReviewUtils.dailyProcess: Running ' + toRun.length + ' execution(s) today.');
			for (var i in toRun) {
				var thisRun = toRun[i];
				this.processExecution(thisRun.configuration, thisRun.table, thisRun.filter);
			}
			gs.info('PeriodicReviewUtils.dailyProcess: ' + toRun.length + ' execution(s) completed.');
		} else {
			gs.info('PeriodicReviewUtils.dailyProcess: Nothing scheduled to run today.');
		}
	},

	processExecution: function(configuration, table, filter) {
		gs.info('PeriodicReviewUtils.dailyProcess: ' + configuration + '; ' + table + '; ' + filter);
	},

	calculateNextRunDate: function(configurationGR) {
		var runDate = new Date(configurationGR.getDisplayValue('next_scheduled_date'));
		var frequency = configurationGR.getValue('frequency');
		var days = 0;
		var months = 0;
		if (frequency == 'daily') {
			days = 1;
		} else if (frequency == 'weekly') {
			days = 7;
		} else if (frequency == 'biweekly') {
			days = 14;
		} else if (frequency == 'monthly') {
			months = 1;
		} else if (frequency == 'bimonthly') {
			months = 2;
		} else if (frequency == 'quarterly') {
			months = 3;
		} else if (frequency == 'semiannually') {
			months = 6;
		} else if (frequency == 'annually') {
			months = 12;
		} else if (frequency == 'biannually') {
			months = 24;
		}
		if (days > 0) {
			runDate.setDate(runDate.getDate() + days);
		} else {
			runDate.setMonth(runDate.getMonth() + months);
		}
		return JSON.stringify(runDate).substring(1, 11);
	},

    type: 'PeriodicReviewUtils'
};

To run a quick test, we can navigate over to Scripts – Background and use this little test script.

var pru = new PeriodicReviewUtils();
pru.dailyProcess();
gs.info('Done!');

Then all we need to do is to click on the Run script button and see what happens.

First test run results

Well, as usual, there is good news and bad news here. The good news is that it didn’t crash and it made all the way through without an error. The bad news is that I forgot to set the next run date field on the lone configuration record, so the process didn’t get very far. On the bright side, though, we did successfully test the scenario where there is nothing to run, so we can check that off of the list. Now let’s update the configuration and try this again.

Second test run results

There, that’s better! And once again, we ran through without any errors, so I think we are off to a good start. Next time, we will build out the code for that processExecution function and give that guy a little test.

Periodic Review, Part V

Posted on | by snhackery

“I find that the best way to do things is to constantly move forward and to never doubt anything and keep moving forward.”
— John Frusciante

Last time, we wrapped up the initial testing of our first table and related form, so now it is time to move on to the other tables on our list. The next table that we will want to deal with is the table that contains the details for each run of the review process, which we will call Review Execution.

New Review Execution table

The first field that we will want to have is a reference back to the associated configuration record. We will call that one configuration. Then we will need a short and long description field, the run date, start and end times, some counters, a current state, and a completion code.

Fields for the new Review Execution table

Once we save all of the fields, we can pull up the form and arrange everything to suit our needs.

Form layout for the new Review Execution table

One last thing that we will want to do with this table is to set up a couple of state values, one for running and one for completed, and set the default value to running so that whenever a new record is created, it is automatically set to the running state.

Choices and default for the state field

That should take care of the Review Execution table. Now we will need to build tables for every notice that comes out of the review process and every item that appears on each notice. Let’s start with the notice table first, which we can call Review Notice.

Review Notice table

This table will have several reference fields, the first one being a link back to the associated execution. Other references will link to the email that was sent out, the recipient of the email, and the user who responded to the notice.

Fields for the new Review Notice table

With that completed, we can then lay out the form in the way that we would like it to appear.

Form layout for the Review Notice table

The last table that we will need to build before we can start looking at the actual process of sending out the notices is the table of items associated with each notice. We will call that one Review Notice Item.

Review Notice Item table

This one will contain reference fields as well, including a reference to the associated notice, but the reference to the actual item being reviewed will be a little bit different. Because we are setting this up as a generic process that can review virtually any item, for the link to the item we will be using a Document ID field for the reference. More on that a little later, but for now, here are the fields for this table.

Fields for the new Review Notice Item table

Both Reference type fields and Document ID type fields contain sys_ids, but on a reference field, the table containing the record with that sys_id is defined as a part of the field definition. On a Document ID field, the record could potentially be on any table in the system, so you need a second, dependent field of type Table to specify which table contains the record referenced. Fortunately for our purposes, the field does not need to be on the same table as the one that contains the Document ID. The table field that we need is actually a part of the Review Configuration where we define what items are to be reviewed. To set that up, we need to go into the field definition and select that field in the Dependent field section of the form.

Document ID dependent field configuration

With that out of the way, we can once again lay out the fields as we like on the associated form.

Form Layout for the new Review Notice Item table

That should be all we need to get started on building the actual process that performs the review. There will eventually be one or more tables yet to define, but we can save that until they are needed. For now, let’s set that aside so that next time we can start working on the actual review process itself.