ServiceNow: SOAP Journal

Integration using Web Services

SNDML 3 – ServiceNow Data Mart Loader

Introducing SNDML version 3

I am pleased to announce SNDML3, a command line utility to load SQL databases from ServiceNow. SNDML3 is a good place to start if you are considering a data warehouse for your ServiceNow data, or if you want to quickly download some data for analysis. SNDML3 is written in Java and is completely open source. It will run on Linux, Unix, Windows and Mac OS. The initial release of SNDML3 supports MySQL, PostgreSQL, Oracle and Microsoft SQL Server. SNDML3 is hosted on GitHub. You can download the files here.

Although much of the SNDML 2.6 code has been recycled, SNDML3 is essentially a rewrite. The SOAP Direct Web Services API has been replaced with the REST Table API for improved performance. The “script file” syntax used for SNDML2 has been replaced with a simpler and more powerful YAML configuration. The new code uses Apache HttpClient for ServiceNow communications, JDBC for database communications, and Log4j2 for logging.

SNDML3 includes many new features, some of which I have highlighted here.

Quick Load

The quick load feature allows you load a single table without creating a YAML file. Simply specify the connection profile and the table name on the command line using the -t option as in this example.

java -jar sndml-3.0.1-mysql.jar -p mydev.profile -t cmdb_ci_service

This feature is intended for testing connectivity or loading small tables. It does permit specification of additional options available via YAML.

Partition Load

The partition load feature is designed for backloading of large task based tables or other transactional tables in your ServiceNow instance. This feature logically partitions the source table based on sys_created_on and loads the target table in reverse chronological order starting with the most recent partition. For example, to load all incident records by month, use the following.

tables:
- {source: incident, partition: month}

The load can be split up by quarter, month or week. For a table with a high volume of inserts, you can even partition by day or hour. Use the following to load all records from sys_email which were created on March 16.

tables:
- {source: sys_email, created: [2018-03-16, 2018-03-17], partition: hour}

Incremental Load

A job to load an extremely large table could take many hours to complete. Once the load finishes, you will likely want to keep the target table up to date. We can rely on the sys_updated_on field in ServiceNow to find any records which have been inserted or updated since our last load.

SNDML3 uses a metrics file to keep track of timestamps. The metrics file is simply a text file (actually a Java properties file) which is written out at the completion of an SNDML3 execution. We could modify the above load of the incident table as follows to create the file incident.metrics when the load completes.

metrics: incident.metrics
tables:
- {source: incident, partition: month}

The following script performs an incremental load. It will update or insert to the SQL target table any incident records updated or inserted in the source since the last load.

metrics: incident.metrics
tables:
- {source: incident, since: last}

When this load completes it will overwrite the file incident.metrics with fresh metrics. We could schedule this last job to run repeatedly (perhaps via cron), and with each execution it will update or insert records since the prior run.

Prune

The above process will handle updates and inserts, but what about deletes? For this we use action: prune. The prune action scans sys_audit_delete for any records which have been recently deleted. We can modify the above script as follows to handle inserts, updates and deletes.

metrics: incident.metrics
tables:
- {name: incident_update, source: incident, target: incident,
action: update, since: last}
- {name: incident_prune,  source: incident, target: incident,
action: prune,  since: last}

Sync

Another available action is sync. This action begins by extracting all values of sys_updated_on from the source (ServiceNow) table and comparing them with sys_updated_on in the target (SQL) table. Based on the compare it determines which rows in the target table need to be inserted, updated or deleted. Sync is not available with the since directive; but it can be used with partition and with a created date range. This script will sync all incident records created in the last 60 days.

tables:
- {source: incident, action: sync, partition: week, created: -60d}

Conclusion

The SNDML3 code is new, so please test it out in your non-production ServiceNow instance. Post a comment regarding your experience to this blog, or report any issues via GitHub.

Advertisements

Zeep for SOAP Web Services from Python

Introducing Zeep

“Zeep” is Dutch for “soap”. Zeep is a fast and modern SOAP Web Services client for Python. Zeep fully supports Python 3.x (unlike SOAPpy, which appears to have fallen out of support).

Zeep uses the Requests module for its underlying transport. When you create a Client object using Zeep, it inspects the WSDL, and dynamically generates code to access the underlying service. When accessing ServiceNow SOAP Web Services from Python, we can simplify our code by creating a small class named ServiceNow to hold connection information. This class contains a method named client which returns a Zeep Client.

import requests
import zeep

class ServiceNow:

    def __init__(self, instance, username, password):
        self.instance = instance
        self.session = requests.Session()
        self.session.auth = requests.auth.HTTPBasicAuth(username, password)
        self.transport = zeep.transports.Transport(session=self.session)

    def client(self, tablename):
        wsdl_url = 'https://%s.service-now.com/%s.do?WSDL' % (
            self.instance, tablename)
        return zeep.CachingClient(wsdl_url, transport=self.transport)

The function zeep.CachingClient causes Zeep to cache the WSDL locally for 60 minutes using Sqlite. If you don’t want to cache the WSDL, replace this function with zeep.Client.

Loading Data

We can now use this class to construct ServiceNow SOAP clients. A separate client object is required for each ServiceNow table. The following example inserts a record into the incident table. To test this example in a personal dev instance, first create a user named soap.test and grant the user soap and itil roles.

sn = ServiceNow('dev00000', 'soap.test', 'password')
incident = sn.client('incident')
response = incident.service.insert(
    short_description = 'Lorem ipsum dolor sit amet',
    caller_id = 'Fred Luddy',
    urgency = '1'
)
print('inserted number=%s sys_id=%s' % (response['number'], response['sys_id']))

If the keyword arguments are unmanageable, create a dictionary and use Python’s double-star operator to unpack the argument list.

rec = {
    'short_description': 'Lorem ipsum dolor sit amet',
    'caller_id': 'Fred Luddy',
    'urgency': '1'
}
response = incident.service.insert(**rec)

Note that caller_id is a reference field. The ServiceNow API allows you to specify either a sys_id or a display value when updating reference fields.

If you have the Insert Multiple Web Service plugin installed, you can insert multiple rows from a Python client by first constructing a list of dicts. This is particularly useful if you are using a Web Service Import Set. The following snippet inserts two incident records.

recs = [
    {   'short_description': 'Consectetur adipiscing elit',
        'caller_id': 'Don Goodliffe' },
    {   'short_description': 'Curabitur sit amet tincidunt',
        'caller_id': 'Bow Ruggeri' },
]
response = incident.service.insertMultiple(recs)

Reading Data

The ServiceNow best practice recommendation for retrieving a large number of records with SOAP is to use the getKeys and getRecords methods to retrieve the data in chunks. Here is a Zeep illustration of using that technique to read the entire incident table, 200 rows at a time.

incident = sn.client('incident')
# get a list of all the keys
response = incident.service.getKeys()
# convert the comma separated list to a Python list
if int(response['count']) == 0:
    keys = []
else:
    keys = response['sys_id'][0].split(',')
print('reading %d rows' % len(keys))
pagesize = 200
start = 0
while start < len(keys):
    # grab a slice of keys and construct an encoded query
    chunk = keys[start: start + pagesize]
    start += pagesize
    query = 'sys_idIN' + ','.join(chunk)
    rows = incident.service.getRecords(__encoded_query=query)
    for row in rows:
        print('%s %s' % (row['number'], row['short_description']))

The WSDL for the getKeys method states that the occurrence of the sys_id element in the response is maxOccurs="unbounded" minOccurs="0", i.e. an array. However, the actual response (as documented in the wiki) is not an array, but a single sys_id element containing a comma delimited list of sys_ids, or an empty sys_id element if there are no qualifying keys.  If there are zero keys, Zeep uses the WSDL to incorrectly construct a list with a single None element. The above code compensates for the WSDL inconsistency by checking the count of sys_id‘s before splitting the keys.

Scripted Web Services

The Zeep Python client also works well for custom scripted SOAP Web Services that have defined input and output parameters. When defining input and output parameters, ensure that each parameter has a unique order number. If the parameter order is ambiguous, Zeep will be unable to parse the response XML.

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.