ServiceNow: SOAP Journal

Integration using Web Services

Category Archives: SQL

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.

- {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.

- {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
- {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
- {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.


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
- {name: incident_update, source: incident, target: incident,
action: update, since: last}
- {name: incident_prune,  source: incident, target: incident,
action: prune,  since: last}


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.

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


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.


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 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.layout.ConversionPattern=%d{HH:mm:ss} [%t] %p %c %m%n


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

For general information on Log4J configuration files refer to

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.

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 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.


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.