Month: February 2019

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 …

Testing ServiceNow Event Utilities

Posted on | by snhackery

“Testing leads to failure, and failure leads to understanding.”
— Burt Rutan

Now that we have put together a basic ServiceNow Event utility and added a few enhancements, it’s time to try it out and see what happens. There are actually two reasons that we would want to do this: 1) to verify that the code performs as intended, and 2) to see what happens to these reported Events once they are generated. We will want to test both the server side process and the client side process, so we will want a simple tool that will allow us to invoke both. One way to do that would be with a basic UI Page that contains a few input fields for Event data and a couple of buttons, one to report the Event via the server side function and another to report the Event using the client side function.

For the sake of simplicity, let’s just collect the description value from the user input and hard code all of the rest of the values. We could provide more options for input fields, but we’re just testing here, so this will be good enough to prove that everything works. We can always add more later. But for now, maybe just something like this:

Simple Event utility tester

The first thing that we will need is some HTML to lay out the page:

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
<script src="client_event_util.jsdbx"></script>
<div>
 <g:ui_form>
  <h4>Event Tester</h4>
  <label for="description">Enter some text for the Event details:</label>
  <textarea id="description" name="description" class="form-control"></textarea>
  <div style="text-align: center; padding: 10px;">
    <input class="btn" name="submit" type="submit" value="Client Side Test" onclick="clientSideTest();"/>
	 
    <input class="btn" name="submit" type="submit" value="Server Side Test"/>
  </div>
 </g:ui_form>
</div>
</j:jelly>

There’s really nothing too special here; just a single textarea and a couple of submit buttons, one for the client side and one for the server side. On the client side button we add an onclick attribute so that we can run the client side script. On the server side button, we just let the form submit to the server, and then run the server side script when we get to the other side. The client side script is similarly very simple stuff:

function clientSideTest() {
	ClientEventUtil.logEvent('event_tester', 'None', 'Client Event Test', 3, document.getElementById('description').value);
	alert('Event generated via Client Side function');
}

… as is the server side script:

if (submit == "Server Side Test") {
	new ServerEventUtil().logEvent('event_tester', 'None', 'Server Event Test', 3, description);
	gs.addInfoMessage('Event generated via Server Side function');
}

Now all we have to do is hit that Try It button on the UI page, enter some description text, and then click one of the submit buttons to see what happens. On the client side:

Client side Event test

… and on the server side:

Server side Event test

Now that we have generated the Events, we can verify that they were created by going into the Event Management section of the menu and selecting the All Events option. By inspecting the individual Events, you can also see that each Event triggered an Alert, and by setting up Alert Management Rules, these Alerts could drive subsequent actions such as creating an Incident or initiating some automated recovery activity. But now we are getting into the whole Event Management subsystem, which is way outside of the scope of this discussion. My only intent here was to demonstrate that your ServiceNow components can easily leverage the Event Management infrastructure built into the ServiceNow platform, and in fact, do it quite easily once you created a few simple utility modules to handle all of the heavy lifting. Hopefully, that objective has been achieved.

Just in case anyone might be interested in playing around with this code, I bundled the two scripts and the test page together into an Update Set.

Enhanced Event Management for ServiceNow

Posted on | by snhackery

“Great things are done by a series of small things brought together.”
— Vincent Van Gogh

The one property of a ServiceNow Event that we virtually skipped over last time was the additional_info property. This is pretty much a catch-all for any other thing that you might want to record along with the Event itself. The additional_info property is stored in the database as a JSON-formatted string, which you can instantiate in use and then access like any other Javascript object. By leveraging the additional_info property, we can inject standard elements into the Event so that the reporting module does not have to include the code to provide that information. One such bit of info could be details on the currently logged on user. Another might be a Stack Trace containing the details of how we arrived at the point of an Event occurring.

The one thing that we would not want to do, however, would be to overlay any information that the reporting entity has provided, so it will be important to first check for the presence of any data in the additional_info object before we set any values of our own. The first thing that we would have to do would be to check to see if an additional_info value was even provided, and that it was an object to which we could add additional values. Here is one way to approach such a check:

if (additional_info) {
	if (typeof additional_info != 'object') {
		additional_info = {info: additional_info};
	}
} else {
	additional_info = {};
}

This ensures that we have an object, and that we have preserved whatever non-object (string, boolean, number,etc.) values that may have been provided instead of an object. Once we know we have an object to work with, then we can check the object for other properties, and if not already provided, provide a standard value. For example, here is how we could potentially include the various details on the user:

if (!additional_info.user) {
	additional_info.user = {};
	additional_info.user.sys_id = gs.getUserID();
	additional_info.user.id = gs.getUserName();
	additional_info.user.name = gs.getUserDisplayName();
}

Injecting a Stack Trace could be handled in a similar fashion:

if (!additional_info.stackTrace) {
	additional_info.stackTrace = this.getStackTrace();
}

Of course, a server side Stack Trace is of little value if your issue is a client side Event, so you would probably want to snag a client side Stack Trace while you were on the client side, before you sent everything over to the server side to be reported. We can steal some of the code from our server side counterpart to enhance the client side function and turn it into something like this:

logEvent: function(source, resource, metric_name, severity, description, additional_info) {
	if (additional_info) {
		if (typeof additional_info != 'object') {
			additional_info = {info: additional_info};
		}
	} else {
		additional_info = {};
	}
	if (!additional_info.stackTrace) {
		additional_info.stackTrace = this.getStackTrace();
	}
	var ga = new GlideAjax('ServerEventUtil');
	ga.addParam('sysparm_name', 'logClientEvent');
	ga.addParam('sysparm_source', source);
	ga.addParam('sysparm_resource', resource);
	ga.addParam('sysparm_metric_name', metric_name);
	ga.addParam('sysparm_severity', severity);
	ga.addParam('sysparm_description', description);
	ga.addParam('sysparm_additional_info', JSON.stringify(additional_info));
	ga.getXML();
}

By creating a common Event reporting utility function and leveraging the additional_info property for specific selected values, virtually all of the Events reported by ServiceNow components can share a common set of properties. This creates opportunities for common Event processing scripts and generic reporting possibilities that would not exist if everyone were simply following their own unique approach to reporting Events. And once you establish an organizational standard for common values stored in the additional_info property, adding additional items of interest at a future point in time is simply a matter of updating the common routine that everyone calls to report Events.

We still need to put together that testing page that we talked about last time out, but at this point, I think that will have to be a project for another day …

Update: There is an even better version here.

Event Management for ServiceNow

Posted on | by snhackery

“If you add a little to a little, and then do it again, soon that little shall be much.”
— Hesiod

The Event Management service built into ServiceNow is primarily designed for collecting and processing events that occur outside of ServiceNow. However, there is no reason that you cannot leverage that very same capability to handle events that occur in your own ServiceNow applications and customizations. To do that easily and consistently, it’s helpful to bundle up all of the code to make that happen into a function that can be called from a variety of potential users. A server-side Script Include can handle that quite nicely:

var EventUtil = Class.create();
EventUtil.prototype = {
	initialize: function() {
	},
	logEvent: function(source, resource, metric_name, severity, description) {
		var event = new GlideRecord('em_event');
		event.initialize();
		event.source = source;
		event.event_class = gs.getProperty('instance_name');
		event.resource = resource;
		event.node = 'ServiceNow';
		event.metric_name = metric_name;
		event.type = 'ServiceNow';
		event.severity = severity;
		event.description = description;
		event.insert();
	},
	type: 'EventUtil'
};

There are a number of properties associated with Events in ServiceNow. Here is the brief explanation of each as explained in the ServiceNow Event Management documentation:

VariableDescription
SourceThe name of the event source type. For example, SCOM or SolarWinds.
Source Instance (event_class)Specific instance of the source. For example, SCOM 2012 on 10.20.30.40
nodeThe node field should contain an identifier for the Host (Server/Switch/Router/etc.) that the event was triggered for. The value of the node field can be one of the following identifiers of the Host:
  • Name
  • FQDN
  • IP
  • Mac Address
If it exists in the CMDB, this value is also used to bind the event to the corresponding ServiceNow CI.
resourceIf the event refers to a device, such as, Disk, CPU, or Network Adapter, or to an application or service running on a Host, the name of the device or application must be populated in this field. For example, Disk C:\ or Nic 001 or Trade web application.
metric_nameUsed Memory or Total CPU utilization.
typeThe type of event. This type might be similar to the metric_name field, but is used for general grouping of event types.
message_keyThis value is used for de-duplication of events. For example, there might be two events for the same CI, where one event has CPU of 50% and the next event has CPU of 99%. Where both events must be mapped to the same ServiceNow alert, they should have the same message key. The field can be left empty, in which case the field value defaults to source+node+type+resource+metric_name. The message_key should be populated only when there is a better identifier than the default.
severitySeverity of the event. ServiceNow values for severity range from 1 – Critical to 5 – Info, with the severity of 0 – Clear. Original severity values should be sent as part of the additional information.
additional_infoThis field is in JSON key/value format, and is meant to contain any information that might be of use to the user. It does not map to a pre-defined ServiceNow event field. Examples include IDs of objects in the event source, event priority (if it is not the same as severity), assignment group information, and so on. Values in the Additional information field of an Event that are not in JSON key/value format are normalized to JSON format when the event is processed.
time_of_eventTime when the event occurred on the event origin. The format is: yyyy-MM-dd HH:mm:ss GMT
resolution_stateOptional – To indicate that an event has been resolved or no longer occurring, some event monitors use ‘clear’ severity, while other event monitors use a ‘close’ value for severity. This field is used for those monitors proffering the latter. Valid values are New and Closing.

Generating an Event in ServiceNow is simply writing a record to the em_event table. To reduce the amount of info that needs to be passed to the utility, our example function assumes a standard value for a number of properties of the Event, such as the event_class, node, and type, and leaves out completely those things that will receive a default value from the system such as message_key, time_of_event, and resolution_state. For our purpose, which is a means to generate internal Events within ServiceNow, we can accept all of those values as standard defaults. The rest will need to be passed in from the process reporting the Event.

For the source value, I like to use the name of the object (Widget, UI Script, Script Include, etc.) reporting the Event. For the resource value, I like to use something that describes the data involved, such as the Incident number or User ID. The source is the tool, and the resource is the specific data that is being processed by that tool. The other three data points that we pass are metric_name, severity, and description, all of which further classify and describe the event.

The example above takes care of the server side, but what about the client side? To support client-side event reporting, we can add an Ajax version of the function to our server-side Script Include, and then create a client-side UI Script that will make the Ajax call. The modified Script Include looks like this:

var ServerEventUtil = Class.create();
ServerEventUtil.prototype = Object.extendsObject(AbstractAjaxProcessor, {

	logClientEvent: function() {
		this.logEvent(
			this.getParameter('sysparm_source'),
			this.getParameter('sysparm_resource'),
			this.getParameter('sysparm_metric_name'),
			this.getParameter('sysparm_severity'),
			this.getParameter('sysparm_description'));
	},

	logEvent: function(source, resource, metric_name, severity, description) {
		var event = new GlideRecord('em_event');
		event.initialize();
		event.source = source;
		event.event_class = gs.getProperty('instance_name');
		event.resource = resource;
		event.node = 'ServiceNow';
		event.metric_name = metric_name;
		event.type = 'ServiceNow';
		event.severity = severity;
		event.description = description;
		event.insert();
	},

	type: 'ServerEventUtil'
});

To access this code from the client side of things, a new UI Script will do the trick:

var ClientEventUtil = {

	logEvent: function(source, resource, metric_name, severity, description, additional_info) {
		var ga = new GlideAjax('ServerEventUtil');
		ga.addParam('sysparm_name', 'logClientEvent');
		ga.addParam('sysparm_source', source);
		ga.addParam('sysparm_resource', resource);
		ga.addParam('sysparm_metric_name', metric_name);
		ga.addParam('sysparm_severity', severity);
		ga.addParam('sysparm_description', description);
		ga.getXML();
	},

	type: 'ClientEventUtil'
};

Now that we have created our utility functions to do all of the heavy lifting, reporting an Event is a simple matter of calling the logEvent function from the appropriate module. On the server side, that would something like this:

var seu = new ServerEventUtil();
seu.logEvent(this.type, gs.getUserID(), 'Unauthorized Access Attemtp', 3, 'User ' + gs.getUserName() + ' attempted to access ' + functionName + ' without the required role.');

On the client side, where we don’t have to instantiate a new object, the code is event simpler:

ClientEventUtil.logEvent('some_page.do', NOW.user.userID, 'Unauthorized Access Attemtp', 3, 'User ' + NOW.user.name + ' attempted to access ' + functionName + ' without the required role.');

To test all of this out, we should be able to build a simple UI Page with a couple of test buttons on it (one for the server-side test and one for the client-side test). This will allow us to both test the utility modules and also see what happens to the Events once they get generated. That sounds like a good project for next time out.