ServiceNow: SOAP Journal

Integration using Web Services

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);
        }	
    }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: