ServiceNow: SOAP Journal

Integration using Web Services


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";
        else {
            print "insert $tablename $sys_id\n";

Perl behind a proxy server

You may have trouble using the SOAP API if you are running Perl behind a proxy server.  The easiest solution is to configure the proxy with environment variables as in this example.

use ServiceNow::SOAP;
$ENV{HTTP_PROXY} = "http://my.proxy.server";
my $sn = ServiceNow($instance, $username, $password);

This works because ServiceNow::SOAP is built on top of SOAP::Lite which is built on top of LWP. For more information refer to:

Using map for efficient Perl queries

Suppose we have a range of dates, and we want to write a Perl script that finds all incident tickets within that range, and prints the incident number, the name of the assignment group and the assignment group manager. To write a ServiceNow report that generates the data as a CSV file is extremely simple. Grabbing the data efficiently using the Direct Web Services API is a bit trickier.

To translate the Assignment Group sys_id into a name, we can take advantage of the display value feature. However, the same technique does not work for the Manager name because it is a double hop from the incident record. We could create a ServiceNow view to pull the necessary columns; but let’s assume we want to do it only with Web Services.

The following example shows how we can use Perl’s map function to efficiently retrieve the necessary sys_user_group data into a Perl hash for quick lookups. First, on line 13, we use the ServiceNow::SOAP query and fetchAll methods to retrieve the pertinent incident records.  Then, on line 14, we use map to extract a list of group sys_ids. We will only pull from ServiceNow those groups that are relevant to our query. We use the List::MoreUtils uniq function to discard any duplicates. On the next line we use asQuery and fetchAll to retrieve all the related sys_user_group records in a singe Web Services call.  Then, on line 16, we use map again: this time to convert the list of sys_user_group records in a hash that is keyed by the group sys_id.  As we loop through our incident records, we can look up any group in our hash (line 23) to pull the associated details.

use strict;
use List::MoreUtils qw(uniq);
use ServiceNow::SOAP;
my $instance = "myinstance";
my $user = "soap.perl";
my $pass = "password";
my $sn = ServiceNow($instance, $user, $pass, dv=>'all', trace=>1);

my $start_date = "2015-01-01";
my $end_date = "2015-04-30";
my $filter = "opened_at>=$start_date^opened_at<$end_date";

my @inc_recs = $sn->table("incident")->query($filter)->fetchAll();
my @grp_keys = uniq map { $_->{assignment_group} } @inc_recs;
my @grp_recs = $sn->table("sys_user_group")->asQuery(@grp_keys)->fetchAll();
my %grp_hash = map { $_->{sys_id} => $_ } @grp_recs;

foreach my $inc_rec (@inc_recs) {
    my $number = $inc_rec->{number};
    my $opened = $inc_rec->{opened_at};
    my $grp_id = $inc_rec->{assignment_group};
    my $grp_name = $inc_rec->{dv_assignment_group};
    my $grp_rec = $grp_hash{$grp_id};
    my $mgr_name = $grp_rec ? $grp_rec->{dv_manager} : "";
    print "$number | $opened | $grp_name | $mgr_name\n";