Month: March 2019

Service Portal Widget Help

Posted on | by snhackery

“Some people think design means how it looks. But of course, if you dig deeper, it’s really how it works.”
— Steve Jobs

The other day I was trying to remember how to do something on the Service Portal, and it occurred to me that it would be nice to have a little pop-up help screen on all of my various custom widgets so that I could put a little help text behind each one in a consistent manner. There is already an Angular Provider called snPanel that provides a set of stock wrappings for a widget, so it seemed like it wouldn’t take too much to add a little something to that guy to put some kind of Help icon in a consistent place on the title bar, say a question mark in the upper right-hand corner:

Possible User Help Icon

Of course, placing the icon on the screen is just the start; setting it up to do something in a way that was useful and easy to maintain would be the bulk of the work. A modal dialogue seemed to be the best vehicle to deliver the content, but the content would need to come from somewhere, and be somehow linked to the widget in a way that did not require extra code or special logic. After considering a number of various alternatives, I decided to create a new Knowledge Base called User Help for the specific purpose of housing the content for my new help infrastructure. I didn’t want to have to do anything special to a widget in order for the help to be available, so my plan was to use the Title property of the Knowledge Article to store the name or ID of the widget, linking the article directly to the widget without having to store any kind of help ID on the widget itself. This would allow me to create help text for any widget by simply putting some kind of link to the existing widget in the title of the article, which would not actually appear on the screen in my proposed help text modal pop-up window.

So for now, let’s just assume that we can modify the snPanel to include a link to a modal pop-up and focus on the widget that we will use to display the help content. To make ongoing maintenance easier, in addition to the help text itself, it would also be quite handy to have a link to the editor on the screen for those with the power to edit the help text. The link would be slightly different depending on whether or not there was already some help text in existence for this widget, but that’s easy enough to handle with a little bit of standard widget HTML:

<div ng-bind-html="c.data.html"></div>
<div ng-show="c.data.contentEditor && c.data.sysId" style="padding: 20px; text-align: center;">
  <a class="btn btn-primary" href="/nav_to.do?uri=%2Fkb_knowledge.do%3Fsys_id%3D{{c.data.sysId}}" target="_blank">Edit this Help content</a>
</div>
<div ng-show="c.data.contentEditor && !c.data.sysId" style="padding: 20px; text-align: center;">
  <a class="btn btn-primary" href="/nav_to.do?uri=%2Fkb_knowledge.do%3Fsys_id%3D-1%26sysparm_query%3Dkb_knowledge_base%3D{{c.data.defaultKnowledgeBase}}%5Ekb_category%3D{{c.data.defaultCategory}}%5Eshort_description%3D{{c.data.id}}" target="_blank">Create Help content for this widget</a>
</div>

The first DIV is for the content and the next two are mutually exclusive, if they even show up at all. If you are not a content editor, you won’t see either one, but if you are, you will see the Edit this Help content link if help text exists for this widget and the Create Help content for this widget link if it does not.

We shouldn’t need any client side code at all for this simple widget, and the server side should be fairly simple as well: go fetch the help content and figure out if the current user can edit content or not. For our little example, let’s just limit editing to users with the admin role, and assume that the default knowledge base and default category are both called User Help.

(function() {
	if (!data.defaultKnowledgeBase) {
		fetchDefaultKnowledgeBase();
	}
	if (!data.defaultCategory) {
		fetchDefaultCategory();
	}
	data.contentEditor = false;
	if (gs.hasRole('admin')) {
		data.contentEditor = true;
	}
	if (input && input.id) {
		data.id = input.id;
		if (!data.html) {
			data.html = fetchHelpText();
		}
	}

	function fetchHelpText() {
		data.sysId = false;
		var html = '<p>There is no Help available for this function.</p>';
		var help = new GlideRecord('kb_knowledge');
		help.addQuery('kb_knowledge_base', data.defaultKnowledgeBase);
		help.addQuery('kb_category', data.defaultCategory);
		help.addQuery('short_description', data.id);
		help.addQuery('workflow_state', 'published');
		help.query();
		if (help.next()) {
			data.sysId = help.getValue('sys_id');
			html = help.getDisplayValue('text');
		} 
		return html;
	}

	function fetchDefaultKnowledgeBase() {
		var gr = new GlideRecord('kb_knowledge_base');
		gr.addQuery('title', 'User Help');
		gr.query();
		if (gr.next()) {
			data.defaultKnowledgeBase = gr.getValue('sys_id');
		} 
	}

	function fetchDefaultCategory() {
		var gr = new GlideRecord('kb_category');
		gr.addQuery('kb_knowledge_base', data.defaultKnowledgeBase);
		gr.addQuery('label', 'User Help');
		gr.query();
		if (gr.next()) {
			data.defaultCategory = gr.getValue('sys_id');
		} 
	}
})();

The script includes three independent functions, one to fetch the sys_id of the default knowledge base, one to fetch the sys_id of the default category, and one to use those two bits of data, plus the widget‘s ID, to fetch the help content. There are three hard-coded values in this example that would be excellent candidates for System Properties: the Knowledge Base, the Knowledge Category, and Role or Roles used to identify content editors. For now, though, I just plugged in specific values to simplify the example. That’s pretty much it for the pop-up modal dialogue’s widget. Now we just need to hack up the snPanel code to wedge in our link. We should be able to do that by inserting another bit of HTML inside of the h2 heading tag:

<div class="pull-right">
  <a href class="h4" title="Click here for Help" ng-click="widgetHelp()">
    <span class="m-r-sm fa ng-scope fa-question"></span>
  </a>
</div>

The ng-click value in the anchor tag references a scoped function, so we’ll need to add a controller to the provider so that we can insert the code for that function. This is the code that will run whenever someone clicks on our new fa-question icon.

controller: function($scope, spModal) {
	$scope.widgetHelp = function() {
		spModal.open({
			title: 'User Help',
			widget: 'snh-help',
			widgetInput: {id: $scope.widget.id},
			buttons: [
				{label: 'Close', primary: true}
			],
			size: 'lg'
		});
	};
},

The function basically contains a single line of code, which is your standard spModal open command using all of the usual parameters, plus passing in the widget‘s ID as widgetInput, which will be used as the key to retrieve the associated help text from the default Knowledge Base. This actually turned out to be a little more of a modification than it would care to make to a standard ServiceNow component such as snPanel, so I ended up creating a copy of my own and producing the snhPanel, which can now be used anywhere that snPanel can be used. All told, we created one widget and one Angular Provider to make all of this work, and then configured a Knowledge Base and Knowledge Category to house the help text content created through this new user help infrastructure. There are only a couple of parts to this one, but if anyone is interested, here is an Update Set that contains both of them.

Yet Even More Fun with JDBC Data Sources

Posted on | by snhackery

“We have a strategic plan; it’s called doing things.”
— Herb Kelleher

Recently I had the need to write to an external database on demand in much the same way as I had been reading from an external database on demand earlier. I was able to hack the Scheduled Import process to fetch a specific record, so my thought was that there must be some kind of equivalent Scheduled Export process that I might be able to bend to my will to satisfy my current requirement. Unfortunately, the Scheduled Export process is based on dropping a file somewhere, not on writing to an external database. You have a lot of options as to what type of file it is, but it is still just a file, and I wanted to write to a database table.

So, then I looked into lighting up the JDBCProbe, which I did without too much difficulty, but after digging into all of its various capabilities, I realized that it really didn’t have that much more to offer than the techniques that I had already been using. You still have to create a Data Source, you still have to configure the job to run, you still have to launch the job, and you still have to monitor the job for completion and successful execution. Since I had already pretty much worked all of that out with the Scheduled Import process, decided that I would just stick with that.

The difference, of course, was that this time I wanted to write, not read. Fortunately, that problem can be solved with SQL, and nothing else about my original hack really needed to change. Scheduled Imports expect the end result of the SQL execution to be a Result Set, which is the outcome of an SQL query. However, a query is not the only way to produce a Result Set. SQL Insert and Update statements can also produce a Result Set when you use the Output clause. The Output clause returns specified values such as a generated Identity key back in the form of a Result Set. By leveraging the Output clause, you can build an SQL statement that will both update the external database and return a Result Set all in one operation.

Consider the following example SQL:

INSERT
   EMPLOYEE,
   INCIDENT_ID,
   DATE,
   HOURS_WORKED
OUTPUT
   INSERTED.ID,
   INSERTED.EMPLOYEE,
   INSERTED.INCIDENT_ID,
   INSERTED.DATE,
   INSERTED.HOURS_WORKED
INTO
   TIME_CARD
VALUES(
   'snhackery',
   'INC0000456',
   getdate(),
   6.5)

This SQL statement will insert the specified values into the external database table, and then return the inserted values, along with the newly generated ID on the external table, back to ServiceNow in the form of a Result Set. To the Scheduled Import job, this response is indistinguishable from the response received from a standard SELECT statement. Now, you still have to have a somewhat useless table on the ServiceNow end of things to receive the returned data, even though all you really wanted to do was to write to a table in a remote database, but that table can serve as a log of all records written out to the external destination.

It is possible that we could have accomplished this without the need for the local ServiceNow table by utilizing the JDBCProbe, but this happens to work, so that’s good enough to satisfy Rule #1.

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.

Even More Fun with JDBC Data Sources

Posted on | by snhackery

“When in doubt, use brute force.”
— Butler Lampson, Hints for Computer System Design

After hacking up a JDBC type Data Source to pass in a dynamic SQL parameter and then modifying that hack to provide simultaneous update protection, we still have a couple of unresolved issues related to this approach. For one, since we are watching the table and not the scheduled import job, a Record Not Found result and a Something Bad Happened to the Job and it Never Ran result end up looking exactly the same (no new record ever shows up in the table). It would be nice to be able to distinguish one from the other, particularly if you wanted to log an Event in the case of the latter (but not the former!). Another somewhat related issue involves the age of the imported data. Once you retrieve a record from an outside database, there needs to be some criteria for how long you hang on to it and continue to use it before you go back to the outside database and fetch a fresh copy. There are a number of ways to deal with that, which we can review, but first let’s take a look at the Not Found issue.

We already implemented a post-import script in our last modification, and that’s a great place to insert some code to verify that the job ran. After all, the entire purpose of the script is to run when the job is over, so that’s the perfect spot to check to see if you found a record or not. But if we did not find a record, what do we do about it? One approach would be to add a column to the table called Record Found with a type of True/False and a default value of True. Then, in your post-import script, you can grab the key value from the SQL (before you reset it for others to use!), and use it to read the table to see if you found a record. If you didn’t find a record, the you can then create a record using the key value and setting the Record Found column to False. This way, your process that is spinning endlessly waiting for a record to appear will see the record and get out of the waiting loop, and you will just have to check the Record Found column to see if a record was returned or not. Converting that narrative to code might produce something like this:

var ds = import_set.data_source.getRefRecord();
var sql = ds.getValue('sql_statement');
var parts = sql.split("'");
var keyValue = parts[1];
sql = sql.substring(0, sql.indexOf("'")) + "'AVAILABLE'";
ds.setValue('sql_statement', sql);
ds.update();
var gr new GlideRecord('your_table_name');
gr.addQuery('id', keyValue);
gr.query();
if (!gr.next()) {
    gr.initialize();
    gr.setValue('id', keyValue);
    gr.setValue('record_found', false);
    gr.insert();
}

This code actually combines the simultaneous update protection code from our last outing with the new code needed to handle the Record Not Found condition. To complete the solution, of course, the process that reads in this record from the database table will need to check the Record Found column to ensure that there was an actual record returned, and if not, take the appropriate action.

As to the question of how long an externally fetched record is valid before reaching back out to the external database and fetching a fresh one, that really depends on the specific use case. If your specific implementation requires up-to-the-minute information, then the simplest solution is to just delete the imported record as soon as you grab the data. That will ensure that the initial query will always come up empty, and you will always launch a new Scheduled Import job to get a fresh copy. If, on the other hand, the data is not that volatile, and will last throughout the day, you could always set up a nightly process to clean out the table overnight. Under this strategy, the first person interested in a specific record would pull it from the external database, and everyone else who comes along afterwards would just use the imported copy and not have to pay the penalty of the wait time for the completion of the Scheduled Import.

Yet another approach might be to timestamp all of the records at the time of import, and then compare the timestamp value at read to a specified duration from the current date/time. If the record is too old, then trigger a fetch from the external database. Under this scenario, you wouldn’t necessarily have to ever delete the data in the ServiceNow table, as things would be driven by the timestamp, not the presence of a record. You may still want to schedule some kind of periodic record deletion anyway, though, just to keep from hanging on to records that you may never look at again once they are expired.

Up to this point, we have been talking about leveraging the Scheduled Import capability of ServiceNow to fetch single records, or limited sets of records, on demand based on a single key. The data always flows in one direction: from your internal database into ServiceNow. But what if you have a need to write to an internal database on demand from ServiceNow? Well, that’s an interesting question, but one we will have to address at a later date when we have a little more time …

More Fun with JDBC Data Sources

Posted on | by snhackery

“Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.”
— John F. Woods

Rewriting the SQL of a JDBC type Data Source was a neat trick for a specific, limited use case, but to use that tactic on a more traditional requirement would require that we fill in a few of the holes left unattended by that simplistic example. How we deal with the local copy of the data is one major consideration. How we deal with multiple, concurrent users is another one. The latter concern is probably the easiest to address, so let’s take that one on first.

The simplest way to support active multiple users is to treat the Data Source like an airplane restroom and put up some kind of Occupied sign that limits access to only one user at a time. In this case, it would actually be more like an Unoccupied sign that signals that the Data Source is available for use. Using our previous example’s SQL, it might look something like this:

SELECT
   STATUS,
   EXPIRATION_DATE
FROM
   LICENSE_MASTER
WHERE
   ID='AVAILABLE'

The presence of the key word AVAILABLE is our cue that the Data Source is open for business. If that word is not present, then we wait our turn. In practice, that might look something like this in Javascript:

function fetchData() {
    var ds = new GlideRecord('sys_data_source');
    ds.get('name', data.config.dataSource);
    var sql = ds.sql_statement;
    if (sql.indexOf('AVAILABLE') != -1) {
        ds.sql_statement = sql.replace('AVAILABLE', data.license.number);
        ds.update();
        var job = new GlideRecord('scheduled_import_set');
        job.get('name', data.config.jobName);
        gs.executeNow(job);
    } else {
        if (attempts++ < maxAttempts) {
            gs.sleep(sleepMs);
            fetchData();
        }
    }
}

Once you have replaced the key word AVAILABLE with your own key, everyone else who comes along will no longer find the welcome sign out, and will have to wait their turn to utilize the service. The one thing that you will have to do, however, is to reset the SQL back to its open-for-business state once your import job has completed. That can be handled in the specifications for the import job itself. Near the bottom of the Scheduled Import form, there is a check-box labeled Execute post-import script. Checking this box opens up a Script input field where you can enter a script that will run once the Scheduled Import job has completed:

Post-import Script option

To reset the SQL in the Data Source to make it available for the next potential user waiting in line, you can do something like this:

var ds = import_set.data_source.getRefRecord();
var sql = ds.getValue('sql_statement');
sql = sql.substring(0, sql.indexOf("'")) + "'AVAILABLE'";
ds.setValue('sql_statement', sql);
ds.update();

With the post-import script in place, your key value will be safe through the execution of the import job, but then as soon as it completes, will once again be replaced with the key word AVAILABLE to signal to to the next person in line that it is their turn in the barrel. Things may take a tad bit longer under this strategy, but at least users will not be stepping on each other’s requests when they attempt to use features implementing this concept. And of course, the most important thing is that it satisfies Rule #1.