Tag: UI Action

Adding Detail to the Upgrade History Task

Posted on | by snhackery

“Ask not that the journey be easy; ask instead that it be worth it.”
— John F. Kennedy

With the addition of the Paris Upgrade Center, a new type of Task was introduced, the Upgrade History Task. Whenever you upgrade your instance, issues that require your attention will now produce an Upgrade History Task, which you can assign to the appropriate resources for resolution. This is a nice feature, and fairly well implemented, but the form layout is not quite organized in the way that I would like to see it, and there are some tidbits of data from various other, related tables that I would really like to see all in one place. Another thing that annoys me is that the Upgrade Details Related Link opens up a new window and breaks out of the iFrame, losing the header and sidebar menu. That page has a link back to the Upgrade History Task, and if you click back and forth a few times, suddenly you have all kinds of windows open, and none of them have navigation anymore. I don’t like that.

So, I thought about making a bunch of UI Formatters and rearranging the stock form layout to include all of the information that I like to see when I am working an upgrade issue, but the more upgrades that I work on, the less I like to tinker with stock components. Ultimately, I decided to just add a single UI Action that would pop up a modal dialog box that contained the information that I was looking for. Here are the things that I wanted to see:

  • A standard link to the Skip record that didn’t open up a new window,
  • The name of the affected table,
  • A link to the affected record,
  • When the affected record was last updated,
  • Who last updated the affected record,
  • Any indication that we have dealt with this issue before, and if so,
  • The details of what was done with this the last time that it came up in an upgrade.

None of that is present on the form right now, but I didn’t see any reason that we couldn’t pull it all together from the various sources, so I went to work. It seemed like there would be a bit of database querying to get all of this information, and I didn’t really want all of that on the page itself, so I started out by making myself a little Script Include to house all of major code. I called it UpgradeTaskUtils and created a single function called getAdditionalInfo to pull together all of the data.

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

    getAdditionalInfo: function(sysId) {
		var details = {};

		// all of the hard work goes here!

		return details;
	},

	type: 'UpgradeTaskUtils'
};

The first thing that we will need to do, of course, is to use the incoming sysId argument to go out and get the Upgrade History Record, and once we do that, we can then pull out the associated Skip record.

details.taskGR = new GlideRecord('upgrade_history_task');
if (details.taskGR.get(sysId)) {
	details.logGR = details.taskGR.upgrade_detail.getRefRecord();
	if (details.logGR && details.logGR.isValidRecord()) {
		// more code to follow ...
	}
}

Once we know that we have a valid Skip record, the next thing that we will want to do is go get the actual record that has the issue. That’s a little more complicated and uses a table called sys_metadata.

var metaGR = new GlideRecord('sys_metadata');
if (metaGR.get('sys_update_name', details.logGR.file_name.toString())) {
	details.recordGR = new GlideRecord(metaGR.sys_class_name);
	if (details.recordGR.get(metaGR.sys_id)) {
		details.lastRecordUpdate = details.recordGR.getDisplayValue('sys_updated_on');
		details.lastRecordUpdateBy = details.recordGR.getDisplayValue('sys_updated_by');
		// more code to follow ...
	}
}

Since the sys_updated_by fields is just a user_name string and not an actual reference to a User record, if we want to have the details on the User who last updated the record, we will need to go out and fetch that separately.

var userGR = new GlideRecord('sys_user');
if (userGR.get('user_name', details.lastRecordUpdateBy)) {
	details.userName = userGR.getDisplayValue('name');
	details.userSysId = userGR.getUniqueValue();
	details.userLink = '<a href="/sys_user.do?sys_id=' + details.userSysId + '">' + details.userName + '</a>';
}

That takes care of all of the interrelated records involved with this Task, but there is still more work to do if we want any historical data for this same artifact in previous upgrades. Basically, we want to find all of the records in the Upgrade History Task table that reference this same component, except for the one that we already have. We can just do a quick count to start off with, just to see if there is any point in looking any further.

details.previousIssues = 0;
details.prevIssueQuery = 'upgrade_detail.file_name=' + details.taskGR.upgrade_detail.file_name + '^number!=' + details.taskGR.number;
var taskGA = new GlideAggregate('upgrade_history_task');
taskGA.addAggregate('COUNT');
taskGA.addEncodedQuery(details.prevIssueQuery);
taskGA.query();
if (taskGA.next()) {
	details.previousIssues = taskGA.getAggregate('COUNT');
}

Now that we have a count of what’s out there, we can gather up all of the details if the count is greater than zero.

details.prevIssueLink = details.previousIssues;
if (details.previousIssues > 0) {
	details.prevIssueLink = '<a href="/upgrade_history_task_list.do?sysparm_query=' + details.prevIssueQuery + '">' + details.previousIssues + '</a>';
	var taskGR = new GlideRecord('upgrade_history_task');
	taskGR.addEncodedQuery(details.prevIssueQuery);
	taskGR.orderByDesc('sys_created_on');
	taskGR.query();
	if (taskGR.next()) {
		details.previousUpgrade = taskGR.getDisplayValue('upgrade_detail.upgrade_history.to_version');
		details.previousComments = taskGR.getDisplayValue('upgrade_detail.comments');
	}
}

That’s everything that I am looking for right at the moment. I may end up going back one day and tossing in a few more items, but for now, this should do the trick. All together, the new Script Include looks like this:

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

    getAdditionalInfo: function(sysId) {
		var details = {};

		details.taskGR = new GlideRecord('upgrade_history_task');
		if (details.taskGR.get(sysId)) {
			details.logGR = details.taskGR.upgrade_detail.getRefRecord();
			if (details.logGR && details.logGR.isValidRecord()) {
				var metaGR = new GlideRecord('sys_metadata');
				if (metaGR.get('sys_update_name', details.logGR.file_name.toString())) {
					details.recordGR = new GlideRecord(metaGR.sys_class_name);
					if (details.recordGR.get(metaGR.sys_id)) {
						details.lastRecordUpdate = details.recordGR.getDisplayValue('sys_updated_on');
						details.lastRecordUpdateBy = details.recordGR.getDisplayValue('sys_updated_by');
						var userGR = new GlideRecord('sys_user');
						if (userGR.get('user_name', details.lastRecordUpdateBy)) {
							details.userName = userGR.getDisplayValue('name');
							details.userSysId = userGR.getUniqueValue();
							details.userLink = '<a href="/sys_user.do?sys_id=' + details.userSysId + '">' + details.userName + '</a>';
						}
					}
				}
			}
			details.previousIssues = 0;
			details.prevIssueQuery = 'upgrade_detail.file_name=' + details.taskGR.upgrade_detail.file_name + '^number!=' + details.taskGR.number;
			var taskGA = new GlideAggregate('upgrade_history_task');
			taskGA.addAggregate('COUNT');
			taskGA.addEncodedQuery(details.prevIssueQuery);
			taskGA.query();
			if (taskGA.next()) {
				details.previousIssues = taskGA.getAggregate('COUNT');
			}
			details.prevIssueLink = details.previousIssues;
			if (details.previousIssues > 0) {
				details.prevIssueLink = '<a href="/upgrade_history_task_list.do?sysparm_query=' + details.prevIssueQuery + '">' + details.previousIssues + '</a>';
				var taskGR = new GlideRecord('upgrade_history_task');
				taskGR.addEncodedQuery(details.prevIssueQuery);
				taskGR.orderByDesc('sys_created_on');
				taskGR.query();
				if (taskGR.next()) {
					details.previousUpgrade = taskGR.getDisplayValue('upgrade_detail.upgrade_history.to_version');
					details.previousComments = taskGR.getDisplayValue('upgrade_detail.comments');
				}
			}
		}

		return details;
	},

	type: 'UpgradeTaskUtils'
};

Now we need put all of this data on page that we can use for our modal pop-up. I created a new UI Page called upgrade_history_task_info, and started it out by calling our new Script Include to obtain the data.

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
	<g:evaluate var="jvar_not_used">
var utu = new UpgradeTaskUtils();
var obj = utu.getAdditionalInfo(RP.getWindowProperties().get('task_sys_id'));
obj.lastRecordUpdate;		
	</g:evaluate>
	<div style="padding: 20px;">
		<!-- display grid goes here -->
	</div>
</j:jelly>

To format the data, I just used a basic HTML table, with one column for the labels and another for the data.

<table>
	<tr>
		<th style="padding: 5px;">Issue Details:</th>
		<td style="padding: 5px;"><a href="/${obj.logGR.getTableName()}.do?sys_id=${obj.logGR.getUniqueValue()}">${obj.logGR.getDisplayValue()}</a></td>
	</tr>
	<tr>
		<th style="padding: 5px;">Affected Table:</th>
		<td style="padding: 5px;">${obj.recordGR.getLabel()} (${obj.recordGR.getTableName()})</td>
	</tr>
	<tr>
		<th style="padding: 5px;">Affected Record:</th>
		<td style="padding: 5px;"><a href="/${obj.recordGR.getTableName()}.do?sys_id=${obj.recordGR.getUniqueValue()}">${(obj.recordGR.getDisplayValue() > ''?obj.recordGR.getDisplayValue():obj.recordGR.getUniqueValue())}</a></td>
	</tr>
	<tr>
		<th style="padding: 5px;">Record Last Updated:</th>
		<td style="padding: 5px;">${obj.recordGR.getDisplayValue('sys_updated_on')}</td>
	</tr>
	<tr>
		<th style="padding: 5px;">Record Last Updated By:</th>
		<td style="padding: 5px;"><g:no_escape>${(obj.userSysId > ''?obj.userLink:obj.recordGR.getDisplayValue('sys_updated_by'))}</g:no_escape></td>
	</tr>
	<tr>
		<th style="padding: 5px;">Previous Upgrade Issues:</th>
		<td style="padding: 5px;"><g:no_escape>${obj.prevIssueLink}</g:no_escape></td>
	</tr>
	<j:if test="${obj.previousIssues > 0}">
		<tr>
			<th style="padding: 5px;">Last Upgrade w/Issue:</th>
			<td style="padding: 5px;">${obj.previousUpgrade}</td>
		</tr>
		<tr>
			<th style="padding: 5px;">Last Upgrade Comments:</th>
			<td style="padding: 5px;">${obj.previousComments}</td>
		</tr>
	</j:if>
</table>

Now we have our data and we have it laid out nicely on a page, all that’s left to do is to pop it up on the screen. For that, we will need to build a UI Action. I called mine Additional Info, linked it to the Upgrade History Task table, and gave it the following onClick script:

function openAdditionalInfo() {
	var dialog = new GlideDialogWindow('upgrade_history_task_info');
	dialog.setSize(600, 600);
	dialog.setTitle('Additional Info');
	dialog.setPreference('task_sys_id', NOW.sysId);
	dialog.render();
}

That’s pretty much all there is to that. We still need to pull it up and click on it and see what happens, but assuming that all goes well, this exercise should have produced a nice little tool to make plodding through the skipped records in an upgrade just a little bit easier.

Additional Info modal pop-up on the Upgrade History Task form

Nice! Now all we need to do is gather up all of the parts and stuff them into an Update Set.

Fun with Webhooks, Improved

Posted on | by snhackery

“I think it’s very important to have a feedback loop, where you’re constantly thinking about what you’ve done and how you could be doing it better.”
— Elon Musk

So far, I have had relatively good luck playing around with my Simple Webhooks app, and have been able to post content to other systems such as Slack and MS Teams in addition to the test cases that I sent over to webhook.site. One thing that I did notice, though, was that my portal page for editing the details of a Webhook was missing a couple of items found on the corresponding form in the main UI. On the form for a Webhook in the main UI, I built a UI Action that you can use to send a test POST to your URL, and the form also includes a Delete button that you can use to get rid of your Webhook when you no longer need it or want it. The current version of the portal page has neither of those features, so I decided that it was time to add those in.

The first order of business, then, was to add the two buttons to the HTML, right after the existing Save button:

&nbsp;
<button ng-show="c.data.sysId" ng-click="testURL()" class="btn btn-default ng-binding ng-scope" role="button" title="Click here to send a test POST to this URL">Test URL</button>
&nbsp;
<button ng-show="c.data.sysId" ng-click="deleteThis()" class="btn btn-default ng-binding ng-scope" role="button" title="Click here to permanently delete this webhook">Delete</button>

I didn’t want them showing up on new records, since there is no point in deleting a record that you haven’t created yet, so I added an ng-show attribute based on the presence of an existing sys_id. Other than that, it’s just a basic copy and paste of the other button code with some minor modifications. Here’s how it looks rendered out:

New buttons added to the form HTML

The new buttons reference new client-side functions, so next we will need to add those to the existing client-side script. Here are the two that I came up with:

$scope.testURL = function() {
	spModal.confirm('Send a test POST to this URL?').then(function(confirmed) {
		if (confirmed) {
			c.data.action = 'test';
			c.server.update();
		}
	});
};

$scope.deleteThis = function() {
	if (c.data.sysId) {
		spModal.confirm('Permanetly delete this Webhook?<br/>(This cannot be undone.)').then(function(confirmed) {
			if (confirmed) {
				c.data.action = 'delete';
				c.server.update().then(function(response) {
					goBack();
				});
			}
		});
	} else {
		goBack();
	}
};

I ended up putting a Confirm pop-up on both of them, even though technically the URL test is not destructive. I just thought that it might be nice to confirm that you really want send something over to another system before you actually did it. I also added the c.data.action variable so that once we were over on the server side, that code would know what to do. In our previous version, the only call to the server side was that Save button, so there was no question what needed to be done. But now that we have multiple possible actions, everyone — including Save — will need to register their intentions by setting this variable to some known value (save, test, or delete) before invoking c.server.update(). All of the actual work to perform the save, test, and delete actions is done on the server side, so let’s pop over there next.

To begin, I pulled out all of the existing Save logic and put it into a function of its own. Then I added the following conditional step, assuming that I would have similar functions for the other two actions:

	if (input) {
		if (input.action == 'save') {
			save(input);
		} if (input.action == 'test') {
			test(input);
		} if (input.action == 'delete') {
			deleteThis(input);
		}
	} else {
		. . .

The Delete function turned out to be pretty basic stuff:

function deleteThis(input) {
	whrGR.get(input.sysId);
	whrGR.deleteRecord();
	gs.addInfoMessage('Your Webhook data has been deleted.');
}

Most of the code for the Test URL button I just stole from the existing UI Action built for the same purpose. Much of that is buried in the Script Include anyway, so that turned out to be fairly simple as well:

function test(input) {
	whrGR.get(input.sysId);
	var wru = new WebhookRegistryUtils();
	var result = wru.testURL(whrGR);
	if (result.status == '200') {
		gs.addInfoMessage('URL "' + input.url + '" was tested successfully.');
	} else {
		gs.addErrorMessage('URL test failed: ' + JSON.stringify(result, null, '<br/> '));
	}
}

That’s about all there was to that. Technically, you cannot really call this an enhancement since it is functionality that should have been in there from the start. Let’s just call it a much needed improvement. Here’s the new Update Set.

Fun with Webhooks, Part X

Posted on | by snhackery

“Control is for beginners.”
— Ane Størmer

I’ve been playing around with our little Incident Webhook subsystem to make sure that everything works, and to make sure that I had finally developed all of the pieces that I had intended to build. For the most part, I’m quite happy with what we have put together during this exercise, but like most end users who finally get their hands on something that they have ordered, now that I have a working model in my hands and have tried to use if for various things, I can envision a number of different enhancements that would make things even better. Still, what we have is pretty nice all on its own, although I did break down and make just a few minor adjustments.

One thing that I had thought about doing, but didn’t, was to skip the confirmation pop-up on the custom Webhook Registry page’s Cancel button when no changes had been made to the form. Going through that a few times was enough to motivate me to put that in there, and I like this version much better. While I was in there, I also built a goBack() function to house the code for returning to the previous page, and then called that function wherever it was appropriate. This didn’t really save that much in the way of code, since the current goBack() logic is only one line itself, but it consolidates the logic in a single place if I ever want to wire in support for something like my Dynamic Breadcrumbs. The entire client side code for the Webhook Registry widget now looks like this:

function WebhookRegistry($scope, $location, spModal) {
	var c = this;

	$scope.cancel = function() {
		if ($scope.form1.$dirty) {
			spModal.confirm('Abandond your changes and return to your Webhooks?').then(function(confirmed) {
				if (confirmed) {
					goBack();
				}
			});
		} else {
			goBack();
		}
	};

	$scope.save = function() {
		if ($scope.form1.$valid) {
			c.server.update().then(function(response) {
				goBack();
			});
		} else {
			$scope.form1.$setSubmitted(true);
		}
	};

	function goBack() {
		$location.search('id=my_webhooks');
	}
}

One other thing that I noticed when attempting to integrate with various other targets is that many sites are looking for a property named text as opposed to message. I ended up renaming my message field to text to be more compatible with this convention, but it would really be nice to be able to pick and chose what properties you would like to have in your payload, as well as being able to specify what you wanted them to be named. That’s on my wish list for a future version for sure.

Something that I meant to include in this version, but forgot to do, was to emulate the Test URL UI Action on the Webhook Registry widget so that Service Portal users could have that same capability on that portal page. That was definitely on my plan to include, but I just spaced it out when I was putting that all together. I definitely want to be sure to include that at some point in the near future. I would do it now, but I already built the Update Set and I’m just too lazy to go back and fix it now.

One other thing that is on my wish list for some future version is the ability to set this up for more than just the Incident table. I thought about just switching over to the Task table, which includes Incident as well as quite a few other things derived from Task, but the base Task table does not include the Incident’s Caller or the Request’s Requested for, so there would have to be some special considerations included to cover that. The Task table has Opened by, but that’s not really the same thing when you are dealing with folks calling in and dealing with an Agent entering their information. I thought about adding some additional complexity to cover that, but in the end I just put all of that on my One Day … list and left well enough alone.

Based on what I first set out to do, I think it all came out OK, though. Yes, there are quite a few more things that we could add to make it applicable to a broader domain, and there are a number of things that we could do to make it more flexible, user-friendly, and user-customizable, but it’s a decent start. Certainly good enough to warrant the release of an initial version, which you can download here. Since this is a scoped app, I did not bundle any of the dependencies in the Update Set, so if you want to try this out in your own instance as is, you will need to also grab the latest version of SNH Form Fields and SNH ServiceNow Events, which you can find here. All in all, I am happy with the way that it came out, but I am also looking forward to making it even better one day, after I have spent some time attempting to use it as it is today.

Update: There is a better (improved) version here.

Fun with Webhooks, Part III

Posted on | by snhackery

“All things are difficult before they are easy.”
— Thomas Fuller

Now that we have built the Webhook Registry table and configured the associated form layout, the last thing we need to do before we move on is to set up the Test URL UI Action and the underlying code to make it work. Clicking on the Test URL button should pass the current registry record to a function in a new Script Include that will pull the URL out of the registry, POST an example JSON object to the URL, and verify the response. Since we have to reference the Script Include in the UI Action, let’s work on that first.

To create a new Script Include, navigate to System Definition -> Script Includes and then click on the New button. Since this will be a general purpose Script Include for our application, I named it WebhookRegistryUtils.

New WebhookRegistryUtils Script Include

To test a URL, we will want to add a new function to our new Script Include that accepts a Webhook Registry GlideRecord as an argument and returns an Object that contains the results of the test POST. Let’s call this function testURL().

testURL: function(whrGR) {
	var result = {};

	var jsonObj = {message: 'This is a test posting.'};
	var request = new sn_ws.RESTMessageV2();
	request.setEndpoint(whrGR.getValue('url'));
	request.setHttpMethod('POST');
	request.setRequestHeader('Content-Type', 'application/json');
	request.setRequestHeader('Accept', 'application/json');
	request.setRequestBody(JSON.stringify(jsonObj, null, 4));
	var response = request.execute();
	result.status = response.getStatusCode();
	result.body = new global.JSON().decode(response.getBody());
	result.error = response.haveError();
	result.errorCode = response.getErrorCode();
	result.errorMessage = response.getErrorMessage();

	return result;
},

This code is fairly self-explanatory. The jsonObj is our test payload and the request is an empty sn_ws.RESTMessageV2 that we populate with the URL found in the registry record and the hard-coded Method of POST. The response is the result of executing the request, and then we pull all of the relevant information out of the response and use it to populate the result object, which we then return to the caller. We may end up reworking some of this when we start adding additional functionality, but this should be enough to test a URL with no authentication.

At this point, we could test out the code by writing a quick little Fix Script to grab a registry record and pass it to this function, but it might be just as easy to ahead and just make the UI Action and test it out on the form. To create the UI Action, pull up the Webhook Registry form, select Configure -> UI Actions from the hamburger menu, and then click on the New button. I named mine Test URL, checked the Form button checkbox, and put the following script in the Condition:

current.url > ''

That condition keeps the Test URL button from appearing on the form when there is no URL to test.

Test URL UI Action

For the Script, I just call the testURL function in our new Script Include and then notify the user of the results. There is not much to the code, but here it is:

var wru = new WebhookRegistryUtils();
var result = wru.testURL(current);
action.setRedirectURL(current);
if (result.status == '200') {
	gs.addInfoMessage('URL "' + current.url + '" was tested successfully.');
} else {
	gs.addErrorMessage('URL test failed: ' + JSON.stringify(result, null, '<br/> '));
}

There is undoubtedly a much nicer way to format that failure message, but this will get the job done for now. We can always clean that up later. Right now, though, let’s push the button and see what happens! On second thought, though, we can’t really do that until we insert a record into the registry. let’s take care of that now.

New Webhook Registry entry

Once all of the parts are in place, this entry should POST a payload to the specified URL every time there is a change to an Incident assigned to the owner. For the URL, I went out to check out Webhook.site, and was assigned that end point URL, so I threw that in there. That should be a valid webhook URL, so our first test should be successful. Once we prove that, we can mangle up the URL and hit it again to see what a failure looks like. But first things first — let’s see if we can make it work with a real URL.

Successful URL test

So far, so good! Real quick, before we try out the bad one, let’s pop over to Webhook.site and see if there is any evidence that we ran this test. Oh, look — there is!

Test POST results found on Webhook.site

That’s actually a pretty cool little tool. That’s going to be quite handy for this particular adventure. I like it. Now, let’s try out one that we know will fail.

Unsuccessful URL test

Well, that seems to work as well. So now we have our first table and related form, the beginnings of our Script Include and a handy UI Action. From here, we can work on the My Webhooks Portal Widget, or we can start building out the process that sends out the POSTs to the qualifying destinations. That’s nothing that we need to decide today, though; we can figure that all out next time.

Fun with Webhooks, Part II

Posted on | by snhackery

“Well done is better than well said.”
— Benjamin Franklin

Now that we have the idea fairly well formulated, it’s time to get to work. The first thing that we need to do is create our Scoped Application, which you can do by navigating to My Company Applications and then clicking on the Create new button in the upper right-hand corner. I still use the classic UI rather than the Studio, so you may approach this task a little differently, but the end result should be the same.

New Simple Webhook Application

At this point, I have just created the most basic of empty shells. There are no modules or tables or roles or any other artifacts … it’s basically just the bare application record itself. Creating the app does create a scope, and also puts you into that scope, so as long as you don’t change that, everything that you do from this point on will be built in that scope. The first thing that we will want to build is the database table for our registry, which we can do by navigating to System Definition -> Tables and clicking on the New button.

New Webhook Registry table

Once you give it a Label it will generate the appropriate name, and the next thing that you are going to want to do is to uncheck the Create module option, as we only want the table at this point and we don’t need all of those other artifacts generated. We want to give our registrations an ID using the platform’s built-in tools, so we will want to create a field labeled Number of type String and put the following in the Default value:

javascript:getNextObjNumberPadded();

We have a number of other columns to define, but let’s save this for now and go set up our auto-numbering before we forget. To do that, navigate to System Definition -> Number Maintenance and click on the New button. Select our new table from the list and then let’s set the Prefix to WHR for Webhook Registry,

Setting up auto-numbering for our new Webhook Registry table

With that out of the way, we can go back to our table and add the rest of the fields. Here are the ones that I added to get things started:

  • Active – True/False, with default of True
  • Table – Table Name, with default of incident
  • Owner – Reference to the sys_user table
  • Type – String, with four initial choices (Single Item, Caller / Requester, Assignment Group, and Assignee)
  • URL – URL
  • Authentication – String, with two initial choices (None and Basic)
  • User Name – String
  • Password – Password
  • Document ID – Document ID
  • Person – Reference to the sys_user table
  • Group – Reference to the sys_user_group table

We may add a few more later on as we add new features, but this will get us by for now. Event though my intent is to focus solely on the Incident table for now, I went ahead and added the Table column and just defaulted it to Incident. That was mainly so that I could use as the Dependent field on the Document ID field. If you have never worked with Document ID fields before, you can just think of them as a special form of Reference field where you don’t have to specify the table that is being referenced. Instead, you point to another field on the record that contains the name of the table. This way, one of your records can reference one table and another record in that same table can reference a different table, all based on the table specified in the Dependent field. To set up the Dependent field, use the Dependent Field tab on the Dictionary Entry for the Document ID field.

Document ID Dependent field specification

After entering all of the fields, I used the Show form link at the bottom of the page to bring up the form for the new table. Using the Configure -> Form Layout option, I rearranged the fields on the screen to my liking, and then removed the Table field completely, as that will default to Incident for now and we don’t want anyone changing it to anything else at this point.

After getting everything laid out just right, the next thing that I did was to add a few UI Policies to control certain fields based on the value in other fields. For example, I hide the User Name and Password fields unless you set the Authentication to Basic. Similarly, the presence of the Document ID, Group, and Person fields are all dependent on the value of the Type field. Basically, this just hides fields that are not needed and reveals them when they are.

The other things that I wanted to do on this form was to give the user the ability to test their URL. That seemed like a good use for a new UI Action, but rather than putting all of the code for that process in the Action itself, I decided to start a Script Include to house these types of utilitarian functions. Putting all of that together seems like a good exercise for our next installment in this series, so this looks like a good stopping point for now.

Scoped Application Properties, Part IV

Posted on | by snhackery

“Any intelligent fool can make things bigger and more complex… It takes a touch of genius – and a lot of courage to move in the opposite direction.”
— Albert Einstein

In my haste to wrap things up last week, I neglected to actually create an application-specific System Property and then verify that the convenience link and menu item that we created actually worked as intended. I also neglected to post the Update Set containing all of the artifacts related to this adventure, so I have decided that a fourth installment in this series is warranted. To begin, let’s return to where we last off, which was to push the new Setup Properties button to produce all of the application components necessary to manage properties specific to the application. We can scroll down to the bottom of the page now and see the results:

Sample Application page after clicking on the Setup Properties button

The first thing that you will notice is that the Setup Properties button is now gone. Having completed its reason for living, it can now go away permanently, never to be seen or heard from again until we create our next scoped application. Our other UI Action, the link to the System Properties maintenance page, is not visible either, though. That’s just because we have yet to create our first System Property for this application. Let’s fix that by clicking on that New button under the System Properties tab. And just to show off our Reference type System Properties hack, let’s go ahead and make it a Reference property.

Creating a new application-specific property

To create the new property, we give it a Name and a Description, select reference as the Type and then select the sys_user_group table as the Table. Once we have completed the form, we can now click on the Submit button, which will return us to the original Sample App definition page where we can see the effect of adding the first property to the application

Related Links with at least one System Property defined

Now our new UI Action appears, as there is at least one System Property to manage. Clicking on the link will take us to the page where all of the properties for this application can be valued.

Application Property maintenance page

Of course, we also put this same link on the left-hand side bar menu, so we can type Sample App in the navigation filter to bring that one up to verify as well.

Using the left-hand navigation to open up the Application Properties page

That pretty much verifies that all of the parts and pieces are actually doing their respective jobs. Now all that is left is to leave you with that Update Set.

Scoped Application Properties, Part III

Posted on | by snhackery

“You may delay, but time will not, and lost time is never found again.”
— Benjamin Franklin

I got a little sidetracked last time and never got around to building out the code that we need to automate the set-up of application-level properties, so let’s get right to it. Let’s see, at the push of our new button, we wanted to create a Category, a Role, and a Menu Item. We don’t want to create them if they were already created, though, so first we should check and see if they exist, them create them if they don’t. Everything will be named in accordance with the underlying application, so let’s start out by gathering up all of that application-specific data right at the top:

var gr = new GlideRecord('sys_app');
gr.get('name', appName);
var scope = gr.sys_id;
var prefix = gr.scope;
var menu =  appsgrcope.menu;
gs.addInfoMessage('Scope: ' + scope + '; Prefix: ' + prefix + '; Menu: ' + menu);

The scope, prefix, and menu values obtained in this section will be used in setting up the Category, the Role, and the Module (menu). First let’s do the Category:

var category = new GlideRecord('sys_properties_category');
category.addQuery('name', appName);
category.query();
if (category.next()) {
	gs.info('Category ' + appName + ' already exists.');
} else {
	gs.info('Creating category ' + appName);
	category.initialize();
	category.application = scope;
	category.name = appName;
	category.title = "System Properties for Application " + appName;
	category.insert();
}

There’s not much mystery here: we look for a Category on the sys_properties_category table, and if we don’t find it, then we create. We can take the same approach for the Role:

var role = new GlideRecord('sys_user_role');
role.addQuery('name', prefix + '.admin');
role.query();
if (role.next()) {
	gs.info('Admin role already exists: ' + role.name);
} else {
	gs.info('Creating Admin role ' + prefix + '.admin');
	role.initialize();
	role.sys_scope = scope;
	role.name = prefix + '.admin';
	role.suffix = 'admin';
	role.description = appName + ' Administrators';
	role.insert();
}

Last, but not least is the sidebar menu item. Here we have to check to see if the application has its own menu section, put the item there if it does, and put it with the other System Properties if it doesn’t:

var module = new GlideRecord('sys_app_module');
module.active = true;
module.link_type = "DIRECT";
module.query = "/system_properties_ui.do?sysparm_title=" + encodeURIComponent(appName + " Properties") +"&sysparm_category=" + encodeURIComponent(appName);
module.order = 999;
module.roles = role.name;
if (menu.nil()) {
	gs.info("Adding to System Properties Menu ... no orginal menu");
	module.application = 'd546447bc0a8016900046469895b557a';
	module.sys_name = appName + " Properties";
	module.title = appName + " Properties";
} else {
	gs.info("Adding to Module Menu ... has an established menu");
	module.application = menu;
	module.sys_name = "Application Properties";
	module.title = "Application Properties";
}
module.insert();

That’s pretty much it. We can wrap all of that up into a function in a global Script Include, and then update the UI Action to call that function when the button is clicked. Here is the entire script of the new Script Include:

var AppPropertiesUtils = Class.create();
AppPropertiesUtils.prototype = Object.extendsObject(AbstractAjaxProcessor, {
	
	setUpApplicationProperties: function() {
		var appName = current.getDisplayValue('name');
		gs.info("Setting up system properties for the following application: " + appName );
		
		var gr = new GlideRecord('sys_app');
		gr.get('name', appName);
		var scope = gr.sys_id;
		var prefix = gr.scope;
		var menu =  gr.menu;
		gs.info('Scope: ' + scope + '; Prefix: ' + prefix + '; Menu: ' + menu);
		
		var category = new GlideRecord('sys_properties_category');
		category.addQuery('name', appName);
		category.query();
		if (category.next()) {
			gs.info('Category ' + appName + ' already exists.');
		} else {
			gs.info('Creating category ' + appName);
			category.initialize();
			category.application = scope;
			category.name = appName;
			category.title = "System Properties for Application " + appName;
			category.insert();
			
			var role = new GlideRecord('sys_user_role');
			role.addQuery('name', prefix + '.admin');
			role.query();
			if (role.next()) {
				gs.info('Admin role already exists: ' + role.name);
			} else {
				gs.info('Creating Admin role ' + prefix + '.admin');
				role.initialize();
				role.sys_scope = scope;
				role.name = prefix + '.admin';
				role.suffix = 'admin';
				role.description = appName + ' Administrators';
				role.insert();
			}

			var module = new GlideRecord('sys_app_module');
			module.active = true;
			module.link_type = "DIRECT";
			module.query = "/system_properties_ui.do?sysparm_title=" + encodeURIComponent(appName + " Properties") +"&sysparm_category=" + encodeURIComponent(appName);
			module.order = 999;
			module.roles = role.name;
			
			if (menu.nil()) {
				gs.info("Adding to System Properties Menu ... no orginal menu");
				module.application = 'd546447bc0a8016900046469895b557a';
				module.sys_name = appName + " Properties";
				module.title = appName + " Properties";
			} else {
				gs.info("Adding to Module Menu ... has an established menu");
				module.application = menu;
				module.sys_name = "Application Properties";
				module.title = "Application Properties";
			}
			module.insert();
			
			gs.addInfoMessage("System properties for this application have now been successfully initialized. Use the System Properties Tab at the bottom of the page to add, change, and delete System Properties for this application.");
		}
	},

	type: 'AppPropertiesUtils'
});

… and here is the code that we will add to our UI Action to call the script and then refresh the page:

new AppPropertiesUtils().setUpApplicationProperties();
action.setRedirectURL(current);

Now that we have all of the pieces in place, the only thing left to do is to give it all a try and see if everything works out as we intended. The first thing to do is to pull up an app and push the new button. For testing purposes, I created a useless sample app, just to see if we can’t give this thing a go and see what happens.

Before pushing the button

To test things out, all we have to do is pull up the application and click on the Setup Properties button and then see what happens:

After pushing the button

As you can see from the image above, not only did the message come out indicating that the set-up work has been completed, but the Setup Properties button itself is now no longer on the page, as its work has been done and there is no longer any need for the UI Action. All I need to do now it wrap all of these parts and pieces into an Update Set and post it out here one day for those of you that might want to take a closer look …

Scoped Application Properties, Part II

Posted on | by snhackery

“Any daily work task that takes 5 minutes will cost over 20 hours a year, or over half of a work week. Even if it takes 20 hours to automate that daily 5 minute task, the automation will break even in a year.”
— Breaking into Information Security: Crafting a Custom Career Path to Get the Job You Really Want by Josh More, Anthony J Stieber, & Chris Liu

So far, we have created the UI Action to produce our Setup Properties button, configured the conditions under which the button would appear, and built the Business Rule to ensure that all properties created for an application are placed in a Systems Properties Category of the same name. Before we jump into the business of building out all of the things that we want to automate through the push of that button, there are just a couple of more little odds and ends that we will want to take care of first. One thing that will be helpful in maintaining System Properties for the application will be to have the properties listed out as Related Lists. That’s accomplished fairly easily by selecting Configure -> Related Lists from the hamburger menu on the main Application configuration page:

Configuring Related Lists on the main Application page

This will bring up the Available and Selected Related Lists for an Application, and you just need to find the System Property->Application entry in the Available bucket, highlight it, and then use the right arrow button to push it over into the Selected bucket. Oh, and don’t forget to click on that Save button once you’re done.

Activating the System Properties Related List

One other little thing that would be handy would be a link to the property admin page somewhere on the main Application configuration page. Even though our setup automation will be creating a link to that page somewhere on the main navigation menu, it would still be handy to have a link to that same page right here where we are configuring the app. The format of the URL for that link in both instances is the address of the page, plus a couple of parameters, one for the page title and the other for the name of the Category:

/system_properties_ui.do?sysparm_title=<appName> Properties&sysparm_category=<appName>

To build a link to that page on the main Application configuration page can be easily accomplished with another UI Action similar to the first UI Action that we built for our button, but this time we will select the Form link checkbox instead of the Form button checkbox. The code itself is just constructing the URL and then navigating to that location:

function openPage() {
	var appname = document.getElementById('sys_app.name').value;
	var url = '/system_properties_ui.do?sysparm_title=' + encodeURIComponent(appname) + '%20Properties&sysparm_category=' + encodeURIComponent(appname);
	window.location.href = url;
}

There may be more elegant ways to do that, but this works, so that’s good enough for now. This link should show after the Application Properties have been configured, so the display rules are basically opposite of those for our button: the button will show until you use it and the link will only show after you use the button. We can pretty much steal the condition from other UI Action, then, and just flip the last portion that checks for the presence of the Category:

gs.getCurrentApplicationId() == current.sys_id && (new GlideRecord('sys_properties_category').get('name',current.name)) && (new GlideRecord('sys_properties_category_m2m').get('category.name',current.name))

Those of you who are paying close attention will notice that we also add yet one more check to our condition, and that was just to make sure that there was at least one System Property defined for the app. There is no point in bringing up the property value maintenance page if there aren’t any properties. Assuming that you have set up application properties and you have defined at least one property, you should see the new link appear down at the bottom of the main Application configuration page:

New link to the property value maintenance page

That should take care of all of those other little odds and ends … now all that is left is to build out the code that will handle all of those initial setup tasks. Last time, I said that we would take care of that here, but as it turns out, that that was a lie. We’ll have to take care of that next time.

Scoped Application Properties

Posted on | by snhackery

“I have been impressed with the urgency of doing. Knowing is not enough; we must apply. Being willing is not enough; we must do.”
— Leonardo da Vinci

Many times when I build a scoped application in ServiceNow I will end up creating one or more System Properties that are specific to the application. To allow application administrators to maintain the values for those properties, I will usually create a menu item for that purpose, either under the application’s menu section if there is one, or under the the general Systems Properties menu item if there is not. To set all of that up requires a certain amount of work, and being a person who likes to avoid work whenever possible, I decided that it might be worth looking into possible ways to automate all of that work so that I could accomplish all of those tasks with minimal effort. It’s not that I’m afraid of work — I can lay right down next to a huge pile of work and sleep like a babe — it’s just that if there is a way to automate something to save myself some of that time and effort, I’m all in.

So what exactly is it that I seem to keep doing over and over again whenever I start out on a new app? I guess the main thing is the menu option that brings up the System Properties related to the application. If the application happens to have a menu section of its own, then I usually add this down at the bottom of that section and label it Application Properties. For applications that do not have a menu section of their own, I will use the name of the application (plus the word Properties) for the label, and stick it in the existing System Properties section of the menu. Regardless of where the menu item lives, it will point to the stock ServiceNow System Properties value entry page, which is not driven by Scope, but by Category. In order to bring up just the properties for this particular application, I also need to create a Category, usually using the name of the application as the category name, and then put all of application’s properties in that Category. Of course, the easiest way to put all of an application’s properties into a specific category is to create a Business Rule that does just that whenever a new property is created. Now, you don’t want just anyone to be tinkering with these important properties, so yet another thing that I always end up having to create is a Role that can be used to secure access to the properties.

None of these things are complicated, but it all takes time, and the time adds up. If you could do it all with just the push of a button, that would be pretty sweet. So what would we need to do that? Well, right off the bat, I guess the first thing that you would need would be that button. Let’s create a UI Action tied to the application form. It doesn’t have to do anything at this point; we just want to get the button out there to start things off.

UI Action to Set Up Application Property Support

Open up the New UI Action form by clicking on the New button at the top of the UI Action list, and enter Setup Properties in both the Name and Action Name fields. Check the Form button checkbox and the Active checkbox and that should be enough to save the record and create the UI Action. Pull up any Scoped Application at this point, and you should see the new button at the top of the screen.

Setup Properties button on Custom Application page

You really only want to see that button on apps that haven’t already been set up for application-level properties. Once you push that button and everything gets set up, the button should go away and never be seen or heard from again. Pushing the button will initiate a number of different things, so there will be a number of potential things that you could check to see if it’s work has already been accomplished. One of the easiest is probably the presence of a System Properties Category with the same name as the application. To configure the UI Action to only appear if such a category does not yet exist, you can add this line to the Condition property of the UI Action:

gs.getCurrentApplicationId() == current.sys_id && !(new GlideRecord('sys_properties_category').get('name',current.name))

The above line also ensures that you are in the right scope to be editing the app, as you wouldn’t want anyone setting everything up under the wrong scope. So now we have the button and we have it set up so that it only shows up when it is needed. We also have to figure what, exactly, is going to happen when we click on the button, but that’s a little more complicated, so let’s just set that aside for now. A considerably easier task would be to create that Business Rule that makes sure that all System Properties created for this app get placed into the Category of the same name. That one is pretty straightforward.

(function executeRule(current, previous) {
	var sc = new GlideRecord('sys_scope');
	sc.get(current.sys_scope);  
	if (sc.name != "Global" && sc.name != "global") {
		var category = new GlideRecord('sys_properties_category');
		if (category.get('name', sc.name)) {
			var prop = new GlideRecord('sys_properties');
			prop.addQuery('sys_scope', current.sys_scope);
			prop.orderBy('suffix');
			prop.query();
			while (prop.next()) {
			    var propertyCategory = new GlideRecord('sys_properties_category_m2m');	
				propertyCategory.addQuery('property', prop.sys_id);
				propertyCategory.addQuery('category', category.sys_id);
				propertyCategory.query();
				if (!propertyCategory.next()) {
					propertyCategory.initialize();
					propertyCategory.category = category.sys_id;
					propertyCategory.application = current.sys_scope;
					propertyCategory.order = 100;
					propertyCategory.property = prop.sys_id;
					propertyCategory.insert();	
				}
			}
		}
	}
})(current, previous);

This is a fairly simple script that implements fairly simple rules: get the scope of the current property, make sure it isn’t global, see if there is a Category on file with the same name as the Scope, and if so, put the current property into that category. That’s it. Once this is active, whenever you create a non-global System Property, it will automatically be assigned to the Category created for that application.

This is probably a good place to stop for now. Next time out, we will get into the details of just what happens when you push that new button we created.

Fun with JDBC Data Sources

Posted on | by snhackery

“It is common sense to take a method and try it. If it fails, admit it frankly and try another. But above all, try something.”
— Franklin D. Roosevelt

ServiceNow JDBC type data sources are used primarily for importing data from external databases, usually in bulk via periodically scheduled imports. This is a useful feature that allows you to run standard SQL statements either directly from the ServiceNow cloud, or locally on a specified MID server. Unfortunately, the SQL has to be static, and I have yet to find a way to pass dynamic query parameters to the SQL statement so that I can use a JDBC data source for running parameterized queries.

For a standard bulk import, there is really no need to pass query parameters to the SQL statement, but if you want to fetch a specific record, or obtain a list of transactions related to a specific account, it would be nice to be able run a prepared statement to which you could pass one or more parameters. There may actually be a way to set that up, but since I really don’t know what I am doing, I have no knowledge of anything remotely like that. Still, that’s pretty much what I want to be able to do.

The basic use case that I have in mind is a simple UI Action that brings up a modal dialog box containing data from a foreign database table. Let’s say that I have a JDBC connection to a license database on my internal corporate intranet, and the purpose of the button is to check the current status of the license of the person displayed on the screen. I have the person’s license number as a property of the current record, and I want to pass it to a process that will use that number as a key to read the license record from the JDBC data source. If I could pass that number in as a parameter of the SQL, that would be perfect, but since I don’t see any way to do that, we’ll have to resort to a little ServiceNow hackery …

Like most components of ServiceNow, a JDBC type data source is just a record in a table in the system database. In this case, the relevant table is sys_data_source, and the SQL statement can be found in the property sql_statement. Now, I would not recommend this approach for any function that is used with any regularity or by a large, active audience, but because this particular function is a seldomly used, low volume activity, we should be able to get away with it. The little bit of hackery will be to rewrite the SQL statement every time that we use it, injecting a variable value into the stock SQL, saving the data source record, and then launching the job using the updated SQL. The first thing that we will have to do is to create the JDBC data source and configure it with its own SQL statement by setting the Query property to Specific SQL:

JDBC Data Source with Specific SQL

This is the SQL that we will want to modify to insert our dynamic key value in place of the hard-coded key value that is currently in the data source definition. Essentially, we will want to keep everything up to and including that first single quote, and then we can add to that our dynamic value and then a closing single quote to finish things off. We can do that in Javascript with something like this:

sql = sql.substring(0, sql.indexOf("'") + 1) + keyValue + "'";

Of course, before we do that, we have to read the data source definition record in from the database, then replace the SQL statement value, and then update the record in the database, but that’s all simple GlideRecord manipulation that is the foundation of ServiceNow development. But before we dig into all of that, let’s step back just a little bit and talk about what we need to have in place to make all of this work.

To fetch data from a database on your own local system, you will need more than just a JDBC type data source. You will also need a destination Table, an Import Table, a Transform Map, and a Scheduled Import job that you can launch to make the magic happen. ServiceNow tables and transform maps are pretty vanilla items that you can learn about from a variety of sources, so we won’t spend too much time on that here. You will need to create the table, though, before you set up the scheduled import, as you will need to reference that table (as well as the data source) when you set up the scheduled import job. Oh, and let’s not get too overly attached to that word “scheduled” … we really don’t want this thing running on a schedule; we want it running on demand. To create a scheduled import, you do have to define a schedule, but once a year seems like a good enough choice for a schedule if you are going to have to pick one to declare.

For our purpose, though, we will be launching our scheduled import from a script. Once we import the selected record, we don’t really need to import it again, so our script will start out looking in the table first. If we don’t find a record, or if we do find a record and it is out of date, then we launch the job to go get it from the local source database. As this is an asynchronous process, we might want to monitor the job to see when it has been completed, but for our purposes, the presence of the new record in the database table will tell us the same thing, so we can just launch the job and forget it, and then keep looking in the table for the data that we are waiting for. The entire sequence of events would go something like this:

var sleepMs = 1000;
var maxAttempts = 30;
var attempts = 0;
var updateRequested = false;

function getLicense() {
	var gr = new GlideRecord('imported_license_info');
	gr.addQuery('id', data.license.number);
	gr.query();
	if (gr.next()) {
		data.license.status = gr.getDisplayValue('status');
		data.license.expiration_date = gr.getDisplayValue('expiration_date');
	} else {
		if (!updateRequested) {
			var ds = new GlideRecord('sys_data_source');
			ds.get('name', data.config.dataSource);
			var sql = ds.sql_statement;
			ds.sql_statement = sql.substring(0, sql.indexOf("'") + 1) + data.license.number + "'";
			ds.update();
			var job = new GlideRecord('scheduled_import_set');
			job.get('name', data.config.jobName);
			gs.executeNow(job);
			updateRequested = true;
		}
		if (attempts++ < maxAttempts) {
			gs.sleep(sleepMs);
			getLicense();
		}
	}
}

In this example, we first look to the ServiceNow database table for our info, and if we don’t find it, then we check to see if we have launched the import job or not. If we haven’t, then we grab the data source, update the SQL with the key of the record that we want to read, and then launch the job. Then we wait for a bit and check again. The next time through the process, if we still do not have data in the table, we see that we have already launched the job, so now all we have to do is wait for a bit and check again. Rinse. Repeat. Eventually, we will either have the data that we requested or we will run out of attempts and return empty-handed. Since it is possible that something might go wrong with the import, the setting a maximum number of attempts keeps this from just looping through this process forever.

The danger in this approach, of course, is that more that one person will want to run this process at the same time, and one user will rewrite the SQL statement before the other user’s job runs, and then the first user’s SQL will never get a chance to run. That’s a real possibility and one that should not be discounted, but in this particular instance, it was just for an occasionally used convenience feature. The remote possibility of that happening in this case was not significant enough to warrant coding a defensive mitigating strategy.

Later on, I did run into a situation where that would have been a real problem, but that’s a discussion for another time …