Script Include – snhackery

Periodic Review, Part X

Posted on September 19, 2023 | by snhackery

“A person with a clear purpose will make progress on even the roughest road. A person with no purpose will make no progress on even the smoothest road.”
— Thomas Carlyle

Last time, we brought our process far enough along to send out some empty notices, and now we need to create the content for those notices that will inform the recipient of the actions required of them. Before we jump into that, though, we still need to add a little bit more code to our Script Include to wrap the process and update the execution record with the results. At the end of our sendNotices function, let’s add the following code:

// finalize the execution
var itemLabel = 'item';
if (executionGR.total_items > 1) {
	itemLabel = 'items';
}
var noticeLabel = 'notice was';
if (noticeCt > 1) {
	noticeLabel = 'notices were';
}
executionGR.total_notices = noticeCt;
executionGR.run_end = new GlideDateTime();
executionGR.state = 'Complete';
executionGR.completion_code = 0;
executionGR.description = noticeCt + ' ' + noticeLabel + ' generated and sent out covering a total of ' + executionGR.total_items + ' ' + itemLabel + '.';
executionGR.update();

This will update the state of the execution and provide some statistics and a description of the execution. We also need to do one more thing to link the notice records to their corresponding email records, but before we can do that, we have to give the event time to fire and the process time to react to the event firing. We can use a gs.sleep command to do that, but since this is a scoped application, we will have to use a little workaround to get things to work.

// update the references to the sent email
var sleeper = new global.Sleeper();
sleeper.sleep(10000);

Once we know that the notice has been sent out, we can use the sys_watermark table to locate the information that we need to link the notice record to the associated email record.

// update the references to the sent email
var sleeper = new global.Sleeper();
sleeper.sleep(10000);
noticeGR.initialize();
noticeGR.addQuery('review_execution', executionGR.getUniqueValue());
noticeGR.query();
while (noticeGR.next()) {
	var watermark = noticeGR.getValue('email_watermark');
	if (watermark) {
		var watermarkGR = new GlideRecord('sys_watermark');
		if (watermarkGR.get('number', watermark.substring(4))) {
			noticeGR.setValue('email', watermarkGR.getValue('email'));
			noticeGR.update();
		}
	}
}

That should wrap up the process for sending out the requests to review the artifacts. At this point our entire 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.processExecution: ' + 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();
	},

	sendNotices: function(configurationGR, executionGR, noticeGR, noticeItemGR, tempNotice) {
		gs.info('PeriodicReviewUtils.sendNotices: ' + configurationGR.getUniqueValue() + '; ' + executionGR.getUniqueValue() + '; ' + noticeGR.getUniqueValue() + '; ' + noticeItemGR.getUniqueValue() + '; ' + tempNotice);
		var noticeCt = 0;

		var noticeItemGA = new GlideAggregate('x_11556_periodic_r_review_notice_item');
		noticeItemGA.addQuery('review_notice', tempNotice);
		noticeItemGA.addAggregate('COUNT');
		noticeItemGA.groupBy('recipient');
		noticeItemGA.orderBy('recipient');
		noticeItemGA.query();
		while (noticeItemGA.next()) {
			var recipient = noticeItemGA.getValue('recipient');
			noticeGR.initialize();
			noticeGR.review_execution = executionGR.getUniqueValue();
			noticeGR.recipient = recipient;
			noticeGR.short_description = executionGR.getDisplayValue('run_date') + ' review notice for ' + configurationGR.getDisplayValue('short_description');
			noticeGR.insert();
			noticeItemGR.initialize();
			noticeItemGR.addQuery('recipient', recipient);
			noticeItemGR.addQuery('review_notice', tempNotice);
			noticeItemGR.query();
			while (noticeItemGR.next()) {
				noticeItemGR.review_notice = noticeGR.getUniqueValue();
				noticeItemGR.update();
			}
			// now you need to send out the notice, passing in the notice record for variables
			gs.eventQueue('x_11556_periodic_r.ReviewNotice', noticeGR, noticeGR.recipient, noticeGR.getUniqueValue());
			noticeCt++;
		}

		// finalize the execution
		var itemLabel = 'item';
		if (executionGR.total_items > 1) {
			itemLabel = 'items';
		}
		var noticeLabel = 'notice was';
		if (noticeCt > 1) {
			noticeLabel = 'notices were';
		}
		executionGR.total_notices = noticeCt;
		executionGR.run_end = new GlideDateTime();
		executionGR.state = 'Complete';
		executionGR.completion_code = 0;
		executionGR.description = noticeCt + ' ' + noticeLabel + ' generated and sent out covering a total of ' + executionGR.total_items + ' ' + itemLabel + '.';
		executionGR.update();

		// update the references to the sent email
		var sleeper = new global.Sleeper();
		sleeper.sleep(10000);
		noticeGR.initialize();
		noticeGR.addQuery('review_execution', executionGR.getUniqueValue());
		noticeGR.query();
		while (noticeGR.next()) {
			var watermark = noticeGR.getValue('email_watermark');
			if (watermark) {
				var watermarkGR = new GlideRecord('sys_watermark');
				if (watermarkGR.get('number', watermark.substring(4))) {
					noticeGR.setValue('email', watermarkGR.getValue('email'));
					noticeGR.update();
				}
			}
		}
	},

	calculateNextRunDate: function(configurationGR) {
        var runDate = new 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'
};

We still have to build the content for the notices, and a way for the notice recipients to communicate their responses back to the system so that the appropriate action can be taken, so let’s jump into all of that next time out.

Periodic Review, Part IX

Posted on August 11, 2023 | by snhackery

“Hide not your Talents, they for Use were made. What’s a Sun-Dial in the shade!”
— Benjamin Franklin

Last time, we wrapped up most of the work on the script that will handle the review process right up to the point where we need to send out the notice to the recipient. Today we will look at one way to send out an email notification and then build the notice that we will want to send out.

One of the easiest ways to trigger an outbound email is through the use of a System Event, not to be confused with an Event Management Event, which is an entirely different animal. And neither one of those is related in any way to a ServiceNow Event, but now we are really getting off track. To create a new Event, we will navigate to the Event Registry and then click on the New button.

Once we have created our new event, we can create an Email Notification and have the notification triggered by this event. To create our new Email Notification, we will navigate to All > System Notification > Email > Notifications and click on the New button. At this point, let’s not worry too much about the content of the message and let’s just do enough so that we can test things out and make sure that it all works. Once we establish that the email is actually sent out, we can go back in and create the message body that will work for our requirements.

Under the When to send tab, we select Event is fired from the Send when options and then we select our new event from the Event name options. Then on the Who will receive tab, we check the box labeled Event parm 1 contains recipient, which will allow us to send in the recipient as one of the event parameters.

In the What it will contain tab, we will just put the word Testing in the subject and body for now and then save the record so that we can run a test. Now we need to modify our Script Include to initiate the event, passing in the appropriate parameters, namely the notification record and the intended recipient. We will replace this line that we added for earlier testing:

gs.info('This is where we would send a notice to ' + noticeGR.getDisplayValue('recipient'));

… with this new code to add a new instance of the event to the queue:

// now you need to send out the notice, passing in the notice record for variables
gs.eventQueue('x_11556_periodic_r.ReviewNotice', noticeGR, noticeGR.recipient, noticeGR.getUniqueValue());
noticeCt++;

After we save that we can pop back over to Scripts – Background and see if all of this results in some email being sent out.

Well, that looks pretty good, but let’s take a look at the email logs and see if we actually sent out some notices.

OK, that works! Now that we know that our process will send out the notices to the designated recipients, the next thing that we will need to do is to come up with the content of the notice. That sounds like a good project for our next installment.

Periodic Review, Part VIII

Posted on August 2, 2023 | by snhackery

“Code is like humor. When you have to explain it, it’s bad.”
— Cory House

Last time, we built out the processExecution function in our utility Script Include, and now we need to continue on by building out the sendNotices function. Before we build it out completely, though, let’s just create a quick stub so that we can test out the work so far. This ought to do the trick.

sendNotices: function(configurationGR, executionGR, noticeGR, noticeItemGR, tempNotice) {
	gs.info('PeriodicReviewUtils.sendNotices: ' + configurationGR.getUniqueValue() + '; ' + executionGR.getUniqueValue() + '; ' + noticeGR.getUniqueValue() + '; ' + noticeItemGR.getUniqueValue() + '; ' + tempNotice);
}

Now we just need to make sure that we have the next run date set correctly on our lone example configuration and then we can navigate over to Scripts – Background and type in our earlier testing code.

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

And once again we can hit that Run script button and see what happens.

Well, that’s not good! It seems that there is some kind of problem with our calculateNextRunDate function. It’s always something! But then, that’s why I like to test things out as I go rather than waiting for the entire thing to be built to try things out. The good news is that it seems to have run all of the way through and made it all the way to the end, where it updates the run date, before it crashed and burned. Let me dig into that function and see what might be wrong.

(insert brief debugging pause)

OK, I found the issue. Apparently, it doesn’t like this line:

var runDate = new Date(configurationGR.getDisplayValue('next_scheduled_date'));

The next scheduled date on the configuration record is a GlideDate, and the display value is year, month, and then day. The Javascript Date constructor doesn’t like that, although I know that I have done that in the past without issue. Still, it doesn’t really matter because the value should be today’s date; otherwise, we wouldn’t be running right now. So we can simple change that to this:

var runDate = new Date();

That should still get us the date that we are looking for, so let’s hop back over to Scripts – Background and try this again.

That’s better. So far, so good. Now we need to actually build out that sendNotices function to group all of the notice item records by recipient, create a notice record for each recipient, and then send out the notices. We can develop an unduplicated list of recipients by using a GlideAggregate.

var noticeItemGA = new GlideAggregate('x_11556_periodic_r_review_notice_item');
noticeItemGA.addQuery('review_notice', tempNotice);
noticeItemGA.addAggregate('COUNT');
noticeItemGA.groupBy('recipient');
noticeItemGA.orderBy('recipient');
noticeItemGA.query();
while (noticeItemGA.next()) {
	var recipient = noticeItemGA.getValue('recipient');
	...
}

Once we have the recipient, we can create the notice record for that recipient.

noticeGR.initialize();
noticeGR.review_execution = executionGR.getUniqueValue();
noticeGR.recipient = recipient;
noticeGR.short_description = executionGR.getDisplayValue('run_date') + ' review notice for ' + configurationGR.getDisplayValue('short_description');
noticeGR.insert();

Now that a notice record for this recipient has been created, we need to go back and find all of the notice item records that are associated with this recipient and update the reference to the notice record from the temporary notice to the newly created notice for this recipient.

noticeItemGR.initialize();
noticeItemGR.addQuery('recipient', recipient);
noticeItemGR.addQuery('review_notice', tempNotice);
noticeItemGR.query();
while (noticeItemGR.next()) {
	noticeItemGR.review_notice = noticeGR.getUniqueValue();
	noticeItemGR.update();
}

At this point, we should be able to send out the notice, but as that is a rather complex process in and of itself, let’s just stub things out for now so that we can test out the process so far.

gs.info('This is where we would send a notice to ' + noticeGR.getDisplayValue('recipient'));

All together, our new sendNotices function now looks like this.

sendNotices: function(configurationGR, executionGR, noticeGR, noticeItemGR, tempNotice) {
	gs.info('PeriodicReviewUtils.sendNotices: ' + configurationGR.getUniqueValue() + '; ' + executionGR.getUniqueValue() + '; ' + noticeGR.getUniqueValue() + '; ' + noticeItemGR.getUniqueValue() + '; ' + tempNotice);
var noticeCt = 0;

	var noticeItemGA = new GlideAggregate('x_11556_periodic_r_review_notice_item');
	noticeItemGA.addQuery('review_notice', tempNotice);
	noticeItemGA.addAggregate('COUNT');
	noticeItemGA.groupBy('recipient');
	noticeItemGA.orderBy('recipient');
	noticeItemGA.query();
	while (noticeItemGA.next()) {
		var recipient = noticeItemGA.getValue('recipient');
		noticeGR.initialize();
		noticeGR.review_execution = executionGR.getUniqueValue();
		noticeGR.recipient = recipient;
		noticeGR.short_description = executionGR.getDisplayValue('run_date') + ' review notice for ' + configurationGR.getDisplayValue('short_description');
		noticeGR.insert();
		noticeItemGR.initialize();
		noticeItemGR.addQuery('recipient', recipient);
		noticeItemGR.addQuery('review_notice', tempNotice);
		noticeItemGR.query();
		while (noticeItemGR.next()) {
			noticeItemGR.review_notice = noticeGR.getUniqueValue();
			noticeItemGR.update();
		}
		gs.info('This is where we would send a notice to ' + noticeGR.getDisplayValue('recipient'));
	}
}

That should be enough to give things the old college try. Let’s pop back over to Scripts – Background and paste in our testing code and see what we get.

Nice! Obviously, a lot more testing is in order before we can say for sure that everything works for every scenario, but things are looking pretty good. Next time, let’s see if we can actually send out a notice.

Periodic Review, Part VII

Posted on July 26, 2023 | 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 July 21, 2023 | 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.

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.

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.

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 IV

Posted on June 2, 2023 | by snhackery

“I dream of men who take the next step instead of worrying about the next thousand steps.”
— Theodore Roosevelt

Last time, we did a little bit of work on the list and form for our new table, and now we need to try it out and make sure that everything works as intended. As we mentioned earlier, we will be using our recent Service Account Management demonstration app as the test case for this project, so let’s pull up the form and start entering some data.

*Selecting the Service Account table from the list*

The first thing that we will want to do is to select our Service Account table from the list of tables. Once that has been established, all of the table field drop-down choices will be fields from that table. Now we can select the columns to be used for each of the configuration values that are fields from the specified table.

*Selecting fields from the Service Account table*

Here, we run into a little bit of a problem. The fields we need for the Short Description, Description, and Recipient columns are all values from the base table. For the Escalation recipient column, however, what we would really like to specify there is the Manager of the Owner, which is a reference field on the sys_user table called manager. We would normally write that as a dot-walked field name using the value owner.manager. Since this is a drop-down, though, we don’t have that option. We’ll have to do a little research to see if there is a way around that limitation.

Those are the only required fields, so we can save the record at this point, just to establish our new configuration. Once we do that, though, we will want to bring the record back up again, as we need to test out the Test Filter button that we added to the form to make sure that it works. For now, let’s just use active=true as our test filter and see what happens.

Once we save the configuration record with the new filter, we can click on the Test Filter button as see where we land.

*Result from clicking on the Test Filter button*

Well, there is good news and bad news here. The good news is that we ended up on the Service Account table’s list page, which is where we wanted to be. The bad news is that there are no records being displayed. Fortunately, the reason for that is that I cleared out all of my sample test cases on the instance once that project was complete, so the problem is not because of any flaw in the UI Action. Still, if I want to use the Service Account Management app for testing, I am going to need to recreate some of those test cases before we get too far. Anyway, you can look at the filter display on the list and see that the filter that we entered on the configuration is accurately represented in the resulting list. So that looks good.

There are a lot more fields to enter, but some of them, such as the Resolution Script or the Resolution Subflow will have to wait until we actually build a Script Include or a Subflow. For now, I think we have done enough to check out all of the form customizations, so now it is time to get back to building tables. Next time, we will jump into creating the table that we will use to store the execution information each time the process runs. That one should be pretty straightforward, so maybe we may even get a chance to tackle another one after that.

Periodic Review

Posted on April 28, 2023 | by snhackery

“Ideas are of themselves extraordinarily valuable, but an idea is just an idea. Almost any one can think up an idea. The thing that counts is developing it into a practical product.”
— Henry Ford

When we wrapped up the Service Account Management project, we intentionally left out a critical part of the complete life-cycle of a service account, the periodic review of the account to ensure that the account was still needed. We did that intentionally because it was our opinion that it was best to leave that function to a generic third party product that could handle such a requirement for any number of use cases beyond just the management of service accounts. Virtually anything that is created, deployed, or installed for a temporary purpose should be reviewed on occasion to make sure that it is still needed, and if it is determined that it is no longer needed, some action should be take to revoke, deactivate, or uninstall the item for a number of reasons, including security and resource utilization. Regardless of the nature of the item, the process should basically be the same.

To have some generic product that would work for just about anything, there would have to be some kind of registration or set-up process to be used for each specific type of item that you wanted to review. And of course, there would have to be some meaningful name for these instances or use cases and they would need to be stored in some appropriately named table. For our purposes we could refer to these implementations of the product as Reviewed Artifacts, and we could create a table of that name that contained all of the information needed to run the review process for that particular implementation.

In practice, there would be some scheduled job that would run every day and refer to this table to see if there was any work to be done that day, and if there was, process each artifact’s workload in turn, sending out notices to the appropriate individuals informing them of the need to take some action to reaffirm the need for the items in question. Another table could keep track of these runs, and yet another could track the individual items associated with each run. Rather than send multiple notices to a single individual who might be responsible for more than one item, though, it would probably be better to consolidate all of the items for a specific individual onto a single notice, and so it might be better to have a table of notices sent out, and then a subordinate table of the items associated with that notice. In that case, the item table would point to the notice table, the notice table would point to the run table, and the run table would then point to the master configuration record for that particular reviewed artifact.

Upon receiving the notice of action required, you would want the recipient to then indicate whether or not each item on the notice was still required. For that, the notice could provide a link to a page that would display the list of items and provide a series of check boxes for various resolutions. To maximize flexibility, the possible resolutions could be customized for each reviewed artifact, and those options would be configured as part of the set-up for each new reviewed artifact and stored in yet another related table.

Once the recipient made their selections and submitted the response, the system could then update the item records within the system and also send the responses to some configured Script Include or Flow that would take the appropriate actions on the source records based on those responses.

To set all of this up for a new reviewed artifact, then, you would need to provide the source table containing the artifacts to be reviewed, the fields on the table that contain various bits of information such as the recipient of the notice and the description of the item, the frequency of the review, some artifact-specific verbiage for the notices, the options to be provided on the response entry page, and some artifact-specific process to handle the responses. Once we get into things, we may find that we will need other data points as well, but this should get us started.

It seems like a lot, but we will just take things on one piece at a time and see how it goes. Next time out we will get to work and create a Scoped Application and start throwing together some tables.

SNH Data Table Widgets on Share, Updated

Posted on April 5, 2023 | by snhackery

“Baby steps count, as long as you are going forward. You add them all up, and one day you look back and you’ll be surprised at where you might get to.”
— Chris Gardner

Recently, I came across some issues with the Content Selector Configuration Editor related to Scoped Applications, so I made a number of small corrections and put out a new Update Set for the editor. What I did not do, however, was to create a new Update Set for the SNH Data Table Widgets, which are also bundled with the very same editor. Yesterday, I finally got around to correcting that oversight, and now you should be able to find version 2.5 out on Share.

Version 2.5 is essentially the exact same bundle as the previous version (2.4.1), with the only change being the inclusion of the corrected configuration editor. Still, it does address the issues related to scoped configuration scripts, so it’s probably worth pulling down and installing it, just to avoid running into those annoying problems one day in the future. There are no new features or components in this new version, but it does now include the latest of everything, so this is the one that you will want.

Service Account Management, Part XX

Posted on April 3, 2023 | by snhackery

“Beginning in itself has no value; it is an end which makes beginning meaningful; we must end what we begun.”
— Amit Kalantri

Last time, we added the Requested Item table to our Service Account dashboard so that we could see the pending requests, but we left off with a field name error and the desire to add a few item variables to the table using some Scripted Value Columns. Today, we will fix up that little error, and add some columns to both tables, hopefully wrapping things up, at least for this version of the dashboard.

In our field list for the new table, we had included the field name opened, when in actuality, the correct field name for the opened date/time is opened_at. That’s an easy fix, and now our field list looks like this:

number,opened_at,request.requested_for,stage

While we are in the configuration updating field lists, let’s also add the new link to the original request to the field list for the Service Account table, which will now look like this:

number,type,user_id,owner,owning_group,original_request

Also, since that new column will be a link to the sc_req_item table, let’s map that table to the ticket page by adding a new entry to the reference map.

*Mapping the Requested Item table to the ticket page*

That should take care of the errors and oversights. Now let’s take a look at adding some item variables to the pending request view. We put some catalog item variables on an example table not too long ago, so let’s just follow that same approach and maybe steal a little code from that guy so that we don’t end up reinventing an existing wheel. Here is the script that we built for that exercise.

var ScriptedCatalogValueProvider = Class.create();
ScriptedCatalogValueProvider.prototype = {
    initialize: function() {
    },
 
    questionMap: {
        cpu: 'e46305fbc0a8010a01f7d51642fd6737',
        memory: 'e463064ac0a8010a01f7d516207cd5ab',
        drive: 'e4630669c0a8010a01f7d51690673603',
        os: 'e4630688c0a8010a01f7d516f68c1504'
    },
 
    getScriptedValue: function(item, config) {
        var response = '';
 
        var column = config.name;
        if (this.questionMap[column]) {
            response = this.getVariableValue(this.questionMap[column], item.sys_id);
        }
 
        return response;
    },
 
    getVariableValue: function(questionId, itemId) {
        var response = '';
 
        var mtomGR = new GlideRecord('sc_item_option_mtom');
        mtomGR.addQuery('request_item', itemId);
        mtomGR.addQuery('sc_item_option.item_option_new', questionId);
        mtomGR.query();
        if (mtomGR.next()) {
            var value = mtomGR.getDisplayValue('sc_item_option.value');
            if (value) {
                response = this.getDisplayValue(questionId, value);
            }
        }
 
        return response;
    },
 
    getDisplayValue: function(questionId, value) {
        var response = '';
 
        var choiceGR = new GlideRecord('question_choice');
        choiceGR.addQuery('question', questionId);
        choiceGR.addQuery('value', value);
        choiceGR.query();
        if (choiceGR.next()) {
            response = choiceGR.getDisplayValue('text');
        }
 
        return response;
    },
 
    type: 'ScriptedCatalogValueProvider'
};

We can make a copy of this script and call ours ServiceAccountDashboardValueProvider. Most of this appears to be salvageable, but we will want to build our own questionMap using the columns that we will want to use for our use case. To find the sys_ids for the variables that we will want to use, we can pull up the Catalog Item to get to the list of variables, and then pull up each variable and use the context menu to snag the sys_id for each one.

*Grabbing the sys_id for each desired catalog item variable*

Once we gather up all of the sys_ids, we will have a new map that looks like this:

questionMap: {
	account_id: '59fe77a4971311100362bfb6f053afcc',
	type: 'f98b24a4971711100362bfb6f053afa0',
	group: '3d4fbba4971311100362bfb6f053afe3'
},

That should be enough to make things work; however, in our case the types of variables involved will return the display value directly, so we do not need to go through that secondary process to look up the display value from the value. We can simply delete that unneeded function and return the value directly in this instance. That will make our new script look like this:

var ServiceAccountDashboardValueProvider = Class.create();
ServiceAccountDashboardValueProvider.prototype = {
	initialize: function() {
	},
 
	questionMap: {
		account_id: '59fe77a4971311100362bfb6f053afcc',
		type: 'f98b24a4971711100362bfb6f053afa0',
		group: '3d4fbba4971311100362bfb6f053afe3'
	},
 
	getScriptedValue: function(item, config) {
		var response = '';
 
		var column = config.name;
		if (this.questionMap[column]) {
			response = this.getVariableValue(this.questionMap[column], item.sys_id);
		}
 
		return response;
	},
 
	getVariableValue: function(questionId, itemId) {
		var response = '';
 
		var mtomGR = new GlideRecord('sc_item_option_mtom');
		mtomGR.addQuery('request_item', itemId);
		mtomGR.addQuery('sc_item_option.item_option_new', questionId);
		mtomGR.query();
		if (mtomGR.next()) {
			response = mtomGR.getDisplayValue('sc_item_option.value');
		}
 
		return response;
	},
  
	type: 'ServiceAccountDashboardValueProvider'
};

Now that we have a script to reference, we can go back into the Content Selector Configuration Editor and add our new columns.

*Defining our new Scripted Value Columns*

Once we have added our three new columns to the configuration, we can save the configuration script, which now looks like this:

var ServiceAccountDashboardConfig = Class.create();
ServiceAccountDashboardConfig.prototype = Object.extendsObject(global.ContentSelectorConfig, {
	initialize: function() {
	},

	perspective: [{
		name: 'requester',
		label: 'Requester',
		roles: ''
	},{
		name: 'fulfiller',
		label: 'Fulfiller',
		roles: 'itil'
	}],

	state: [{
		name: 'active',
		label: 'Active'
	},{
		name: 'retired',
		label: 'Retired'
	},{
		name: 'pending',
		label: 'Pending'
	}],

	table: {
		requester: [{
			name: 'x_660634_service_0_service_account',
			displayName: 'Service Account',
			active: {
				filter: 'active=true^ownerDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORowning_groupDYNAMICd6435e965f510100a9ad2572f2b47744',
				fields: 'number,type,user_id,owner,owning_group,original_request',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {
					sc_req_item: 'ticket',
					sys_user: 'user_profile'
				},
				actarray: []
			},
			retired: {
				filter: 'active=false^ownerDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORowning_groupDYNAMICd6435e965f510100a9ad2572f2b47744',
				fields: 'number,type,user_id,owner,owning_group,original_request',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {
					sc_req_item: 'ticket',
					sys_user: 'user_profile'
				},
				actarray: []
			},
			pending: {
				filter: 'number=0',
				fields: 'number',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {},
				actarray: []
			}
		},{
			name: 'sc_req_item',
			displayName: 'Requested Item',
			active: {
				filter: 'number=0',
				fields: 'number',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {},
				actarray: []
			},
			retired: {
				filter: 'number=0',
				fields: 'number',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {},
				actarray: []
			},
			pending: {
				filter: 'active=true^request.requested_forDYNAMIC90d1921e5f510100a9ad2572f2b477fe^cat_item=55cc38eb970711100362bfb6f053afa8',
				fields: 'number,opened_at,request.requested_for,stage',
				svcarray: [{
					name: 'account_id',
					label: 'ID',
					heading: '',
					script: 'x_660634_service_0.ServiceAccountDashboardValueProvider'
				},{
					name: 'type',
					label: 'Type',
					heading: '',
					script: 'x_660634_service_0.ServiceAccountDashboardValueProvider'
				},{
					name: 'group',
					label: 'Responsible Group',
					heading: '',
					script: 'x_660634_service_0.ServiceAccountDashboardValueProvider'
				}],
				aggarray: [],
				btnarray: [],
				refmap: {
					sys_user: 'user_profile'
				},
				actarray: []
			}
		}],
		fulfiller: [{
			name: 'x_660634_service_0_service_account',
			displayName: 'Service Account',
			active: {
				filter: 'active=true^type.fulfillment_groupDYNAMICd6435e965f510100a9ad2572f2b47744',
				fields: 'number,type,user_id,owner,owning_group',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {
					sys_user: 'user_profile'
				},
				actarray: []
			},
			retired: {
				filter: 'active=false^type.fulfillment_groupDYNAMICd6435e965f510100a9ad2572f2b47744',
				fields: 'number,type,user_id,owner,owning_group',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {
					sys_user: 'user_profile'
				},
				actarray: []
			},
			pending: {
				filter: 'number=0',
				fields: 'number',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {},
				actarray: []
			}
		},{
			name: 'sc_req_item',
			displayName: 'Requested Item',
			active: {
				filter: 'number=0',
				fields: 'number',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {},
				actarray: []
			},
			retired: {
				filter: 'number=0',
				fields: 'number',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {},
				actarray: []
			},
			pending: {
				filter: 'active=true^cat_item=55cc38eb970711100362bfb6f053afa8^assignment_groupDYNAMICd6435e965f510100a9ad2572f2b47744',
				fields: 'number',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {
					sys_user: 'user_profile'
				},
				actarray: []
			}
		}]
	},

	type: 'ServiceAccountDashboardConfig'
});

Now all we need to do is to pull up the dashboard under the new configuration and see how it all looks. First, let’s take a look at the new column that we added for the original request.

*Modified dashboard with new Original Request column*

There is only data there for the most recent test, but that’s just because that field did not exist on the table until recently. Now let’s click on the Pending state and see how our item variables came out.

*Pending requests with item variable columns*

Very nice! OK, I think that about does it for this version of the sample dashboard. There is still some work that we could do on the Fulfiller perspective, and it might be nice to add an Admin perspective that showed everything, but since this is just an example of what might be done, I will leave that as an exercise for those who might want to play around with things a bit. Next time, let’s take a look at what now have up to this point, and at what there might be left to do before we can wrap this one up and call it done.

Service Account Management, Part XIX

Posted on March 31, 2023 | by snhackery

“There are no big problems, there are just a lot of little problems.”
— Henry Ford

Last time, we wrapped up the initial table configuration for our Service Account dashboard and tested everything out to make sure that it all worked as intended. We also identified the fact that we need to add a second table to the configuration so that we can see the pending requests that have not yet created a record in the Service Account table.Before we do that, though, I decided that it would be useful to add a link to the original request on the Service Account record so that you could easily pull up the request from the account record.

*New Original Request field on the Service Account table*

To populate the field during the creation of the Service Account record, I pulled the Service Account Request Fulfillment flow up in the App Engine Studio and added an entry to drag in the data pill from the original request in the trigger.

*Populating the new Original Request field during record creation*

With that out of the way, we can return our attention to adding the new table to the dashboard configuration. To do that, we go back to the Content Selector Configuration Editor that we recently updated to correct a few issues related to Scoped Applications. Before we do that, though, let’s pull up the list of Requested Items and build ourselves a filter that we can use to show all of the open items for Service Accounts requested by the current operator.

*List filter for open Service Account items requested by the current operator*

We are looking for active items requesting the Service Account catalog item requested by the currently logged on user. The filter can be found in the URL under the sysparm_query parameter.

active%253Dtrue%255Ecat_item%253D55cc38eb970711100362bfb6f053afa8%255Erequest.requested_forDYNAMIC90d1921e5f510100a9ad2572f2b477fe

Of course, we have to do a few change alls to get rid of all of the double encoding present in this version, but once we do that we will have a workable filter for the pending state on our newly added table.

active=true^cat_item=55cc38eb970711100362bfb6f053afa8^request.requested_forDYNAMIC90d1921e5f510100a9ad2572f2b477fe

Now let’s jump into the editor and add our new table.

*Adding the sc_req_item table to the configuration*

For now, let’s assume that we don’t want anything to appear in the Active and Retired states, and we can use the same technique that we used on the original table when we didn’t want anything to appear for that table in the Pending state. We’ll set the field list to simply number, and set the filter to number=0.

*Setting the fields and filter for the Active state*

For the Pending state, we add a few more relevant fields and use the filter we snagged from the list URL earlier.

*Setting the fields and filter for the Pending state*

We can do a little more with this, but let’s save what we have for now and take it out for a spin, just to make sure that everything is still in working order. Saving the changes should take us to the generated script, which now looks like this.

var ServiceAccountDashboardConfig = Class.create();
ServiceAccountDashboardConfig.prototype = Object.extendsObject(global.ContentSelectorConfig, {
	initialize: function() {
	},

	perspective: [{
		name: 'requester',
		label: 'Requester',
		roles: ''
	},{
		name: 'fulfiller',
		label: 'Fulfiller',
		roles: 'itil'
	}],

	state: [{
		name: 'active',
		label: 'Active'
	},{
		name: 'retired',
		label: 'Retired'
	},{
		name: 'pending',
		label: 'Pending'
	}],

	table: {
		requester: [{
			name: 'x_660634_service_0_service_account',
			displayName: 'Service Account',
			active: {
				filter: 'active=true^ownerDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORowning_groupDYNAMICd6435e965f510100a9ad2572f2b47744',
				fields: 'number,type,user_id,owner,owning_group',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {
					sys_user: 'user_profile'
				},
				actarray: []
			},
			retired: {
				filter: 'active=false^ownerDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORowning_groupDYNAMICd6435e965f510100a9ad2572f2b47744',
				fields: 'number,type,user_id,owner,owning_group',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {
					sys_user: 'user_profile'
				},
				actarray: []
			},
			pending: {
				filter: 'number=0',
				fields: 'number',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {},
				actarray: []
			}
		},{
			name: 'sc_req_item',
			displayName: 'Requested Item',
			active: {
				filter: 'number=0',
				fields: 'number',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {},
				actarray: []
			},
			retired: {
				filter: 'number=0',
				fields: 'number',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {},
				actarray: []
			},
			pending: {
				filter: 'active=true^request.requested_forDYNAMIC90d1921e5f510100a9ad2572f2b477fe^cat_item=55cc38eb970711100362bfb6f053afa8',
				fields: 'number,opened,request.requested_for,stage',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {
					sys_user: 'user_profile'
				},
				actarray: []
			}
		}],
		fulfiller: [{
			name: 'x_660634_service_0_service_account',
			displayName: 'Service Account',
			active: {
				filter: 'active=true^type.fulfillment_groupDYNAMICd6435e965f510100a9ad2572f2b47744',
				fields: 'number,type,user_id,owner,owning_group',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {
					sys_user: 'user_profile'
				},
				actarray: []
			},
			retired: {
				filter: 'active=false^type.fulfillment_groupDYNAMICd6435e965f510100a9ad2572f2b47744',
				fields: 'number,type,user_id,owner,owning_group',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {
					sys_user: 'user_profile'
				},
				actarray: []
			},
			pending: {
				filter: 'number=0',
				fields: 'number',
				svcarray: [],
				aggarray: [],
				btnarray: [],
				refmap: {},
				actarray: []
			}
		}]
	},

	type: 'ServiceAccountDashboardConfig'
});

Now all we need to do is to pull up the dashboard with the modified configuration, click on the Pending state, and take a quick peek.

Well, that’s not too bad. Looks like we screwed up on the field name for the open date, but other than that, things look pretty good. I want to add a few more columns from the catalog item variables anyway, which we can do by configuring some Scripted Value Columns, so let’s fix our little error and deal with those new fields in our next installment.

Useful ServiceNow links:
Main web site:	https://servicenow.com
Developer site:	https://developer.servicenow.com
Annual Conference:	https://knowledge.servicenow.com