ServiceNow: SOAP Journal

Integration using Web Services

SNDML Debugging

This post contains instructions for obtaining runtime diagnostics from SNDML 2.6.5.

Create a Log4J configuration file

SNDML 2.6.5 is built using Log4J 1.2. Create a file named log4j_debug.properties similar to the following:

# This file contains the Log4J configuration for the ServiceNow Datamart Loader
#
# The names of loggers in the "soap" package will be prefixed with "ServiceNow".
# The names of loggers in the "datamart" package will be prefixed with "Datamart".
#
# Most loggers for the "soap" package are NOT based on the class name.
# Instead, there are two loggers for each table, named as follows:
#   ServiceNow.Request.<tablename>
#   ServiceNow.Response.<tablename>
# Diagnostic information prior to the SOAP call will be written to the Request logger.
# Diagnostic information after the SOAP call will be written to the Response logger.
# 
# Logger levels are as follows:
# ERROR - Serious error that will result in termination of the program
# WARN  - Truncation or recoverable data conversion errors
# INFO  - One line per SOAP or Database call
# DEBUG - Display XML sent and received
# TRACE - Field level diagnostics (extremely verbose)

log4j.rootLogger=INFO, Console

log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d{HH:mm:ss} [%t] %p %c %m%n

log4j.logger.Datamart=DEBUG
log4j.logger.ServiceNow=DEBUG
log4j.logger.ServiceNow.Request=DEBUG
log4j.logger.ServiceNow.Response=INFO
log4j.logger.ServiceNow.Request.sys_db_object=WARN
log4j.logger.ServiceNow.Response.sys_db_object=WARN
log4j.logger.ServiceNow.Request.sys_dictionary=WARN
log4j.logger.ServiceNow.Response.sys_dictionary=WARN

Note that you can set the logging at different levels for different tables.

For general information on Log4J configuration files refer to http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/PropertyConfigurator.html.

Make the Log4J config file available in the class path

The Log4J configuration needs to be in the class path so that Log4J can load it as a resource. One way to do this is to place the file in the current directory and prepend the current directory (“.”) to the class path. You can specify the class path on the Java command line using the “-cp” option.

For Unix the use a colon for the path separator:

-cp .:sndml-2.6.5-mysql.jar

For Windows use a semicolon for the path separator:

-cp .;sndml-2.6.5-mysql.jar

Set log4j.configuration to the name of the config file

Use the “-D” option to set the system property log4j.configuration to the the name of the file.

-Dlog4j.configuration=log4j_debug.properties

Put it all together

You cannot use the “-jar” option with “-cp”. Instead you must specify the main class on the command line. The main class is servicenow.common.datamart.Loader.

The full Java command looks like this:

java -cp .:sndml-2.6.5-mysql.jar -Dlog4j.configuration=log4j_debug.properties servicenow.common.datamart.Loader -p <propfile> -f <scriptfile>

Where <propfile> is the SNDML properties file and <scriptfile> is the SNDML script file.

SNDML version 2.6.5

I have dusted off and released on GitHub version 2.6.5 of the ServiceNow Datamart Loader.  It works with Geneva and Helsinki. Let me know if you use it. Let me know if you find any issues.  It’s free.

SnowMirror

Yesterday we completed the upgrade of our ServiceNow Data Mart to SnowMirror version 3.0.3.  This is a great product and we have received excellent technical support from Pavel Müller.  We had been running version 2.7.13 for about a month and the upgrade to 3.0.3 was simple and painless.  We are currently replicating 80+ tables, the largest of which contain several million rows.  We run on a small AWS EC2 Linux server and the database is RDS MySQL.  If you are looking replicate ServiceNow data into a relational database, you should consider this product.

Using SOAP to copy data between instances

There are lots of ways to move data between two ServiceNow instances. This blog post examines using an external script (written in Perl) and the SOAP API.

There are several reasons why you might want to consider this approach.

  • It is simple. Especially if you are already using Perl.
  • It is repeatable and completely scriptable (unlike an import map which requires manual control).
  • sys_ids are automatically preserved.  This is important, as it makes it easy to maintain relationships in the target system.

There are also a couple of things to consider which may or may not influence your decision.

  • Inserts and updates will be restricted by any access controls and affected by any business rules in the target system.
  • Creator/updater information is not preserved. Records in the target system will be created/updated by the Web Service account, not the original user.
  • Inserts are updates are logged in sys_audit (unless logging has been disabled for the specified target table).

There is one big reason why you might want to NOT use this approach. Performance. SOAP Web Services have never had a reputation for blazing speed. On the other hand, you may not care very much about speed.  Perhaps your Perl script runs at night while you are asleep.  Perhaps you only care about records inserted in the last 24 hours.  If you only need inserts, you could speed this code up considerably by using Insert Multiple.  However, as there is no “Update Multiple” Direct Web Services function, it is difficult to speed up update processing except by creating a Web Service Transform Map.

Inserts and updates performed via Direct Web Services are constrained by Access Controls and subject to Business Rules.  Consider, for example, what happens when you copy an Incident ticket from System A to System B.  What happens to the Number field?  Is it preserved or is changed?  If the account which is used for the inserts does not have write permissions for incident.number, then then a new, unique number will be assigned.  On the other hand, if the script is running as admin, or some other user with write permissions on the field, then the logic is controlled by the Perl script.  The script might use the same value as in the source system (which could potentially cause collisions), or assign a different unique value, perhaps by changing only by prefix portion of the number.

The following sample code begins by creating a hash of all records in the target table, so it can easily determine if a particular record should be inserted or updated. This approach may not be practical if the target table is extremely large. The code attempts to insert (or update) all fields from the source to the target. The code could be easily modified to perform manipulations, such as using a different value for the “number” field.

use ServiceNow::SOAP;
...

my $tablename = "whatever_you_want";
my $filter = "sys_updated_on>=2015-10-01";  # for example

my $sn_source = ServiceNow($source_instance, $source_username, $source_password);
my $sn_target = ServiceNow($target_instance, $target_username, $target_password);

# get a list of all keys in the target table
my @target_keys = $sn_target->table($tablename)->getKeys();
# convert to a hash for quick lookup
my %target_lookup = map { $_ => $_ } @target_keys;

my $source_query = $sn_source->table($tablename)->query($filter);

# read records in chunks of 250
while (my @source_recs = $source_query->fetch()) {
    # update or insert records one at a time
    for my $rec (@source_recs) {
        my $sys_id = $rec->{sys_id};
        if ($target_lookup{$sys_id}) {
            print "update $tablename $sys_id\n";
            $sn_target->table($tablename)->update(%$rec);	            
       }
        else {
            print "insert $tablename $sys_id\n";
            $sn_target->table($tablename)->insert(%$rec);
        }	
    }
}