ServiceNow: SOAP Journal

Integration using Web Services

Category Archives: ServiceNow

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.

Scripted Web Service from VB

Recently I posted a Visual Basic Class for use in calling Direct Web Services from VB Script. Here is a similar class for use in a calling Scripted Web Services.

To use the class

  1. Create a new ServiceNowScriptedWS object
  2. Call SetName to initialize the object, specifying the name of the Web Service.
  3. Call SetValue for each of the input parameters.
  4. Call Post
  5. Call GetValue for each of the output parameters.

Here is the code for the class. It references the same three global variables as the earlier code: gServiceNowURL, gServiceNowUser and gServiceNowPass.

Class ServiceNowScriptedWS
 
  Dim sName, sEndpointURL, sResponsePath
  Dim oWSRequest, oWSRequestDoc, oWSResponseDoc
  Dim oWSRequestEnvelope, oWSRequestBody, oWSRequestOperation
 
  Public Sub SetName(name)
    ' This function must be called BEFORE Post to initialize the class
    sName = name
    sEndpointURL = gServiceNowURL & sName & ".do?SOAP"
    sResponsePath = "/soap:Envelope/soap:Body/executeResponse/"
    Set oWSRequest = CreateObject("MSXML2.XMLHTTP")
    Set oWSRequestDoc = CreateObject("MSXML2.DOMDocument")
    Set oWSRequestEnvelope = oWSRequestDoc.createElement("soap:Envelope")
    oWSRequestEnvelope.setAttribute "xmlns:soap", _
      "http://schemas.xmlsoap.org/soap/envelope/"
    Set oWSRequestBody = oWSRequestDoc.createElement("soap:Body")
    Set oWSRequestOperation = oWSRequestDoc.createElement("tns:execute")
    oWSRequestOperation.setAttribute "xmlns:tns", _
      "http://www.service-now.com/" & sName
    oWSRequestDoc.appendChild oWSRequestEnvelope
    oWSRequestEnvelope.appendChild oWSRequestBody
    oWSRequestBody.appendChild oWSRequestOperation
  End Sub
   
  Public Function Post
    ' This function does the actual Web Services call
    ' It returns True if the call is successful and False if there is an error
    oWSRequest.open "POST", sEndpointURL, False, gServiceNowUser, gServiceNowPass
    oWSRequest.setRequestHeader "Content-Type", "text/xml"
    oWSRequest.send oWSRequestDoc.xml  
    If oWSRequest.status = 200 Then   
      Set oWSResponseDoc = CreateObject("MSXML2.DOMDocument")
      oWSResponseDoc.loadXML oWSRequest.responseText
      oWSResponseDoc.setProperty "SelectionLanguage", "XPath"
      oWSResponseDoc.setProperty "SelectionNamespaces", _
        "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"
      Post = True
    Else
      Set oWSResponseDoc = Nothing
      Post = False
    End if
  End Function
   
  Public Function Status
    ' If Post returns False then call this function to obtain the HTTP status code
    Status = oWSRequest.status
  End Function
   
  Public Function StatusText
    ' If Post returns False then call this function for the error text
    StatusText = oWSRequest.statusText
  End Function
   
  Public Sub SetValue(fieldname, fieldvalue)
    ' This function must be called BEFORE Post
    Dim oChild
    Set oChild = oWSRequestDoc.createElement(fieldname)
    oChild.appendChild(oWSRequestDoc.createTextNode(fieldvalue))
    oWSRequestOperation.appendChild(oChild)
  End Sub
   
  Public Function GetValue(fieldname)
    ' This function must be called AFTER Post
    GetValue = oWSResponseDoc.selectSingleNode(sResponsePath & fieldname).text
  End Function
      
End Class

SOAP Retrieval Limit

Someone help me out here. Did I miss something or am I just stupid?

On the wiki page Retrieving A Large Number Of Records From ServiceNow it says

ServiceNow has imposed a hard-limit of 250 records that can be retrieved at any time in a single query.

The wiki page was created in July 2009.  The “Winter 2009 Stable 2” Release Notes mention the following new “feature”

  • Limit SOAP to 250 records

ServiceNow seemed to be pretty serious about the 250 record limitation. However, in September 2012 a new property — glide.remote_glide_record.max_count — appeared on the Available System Properties wiki page with notes about controlling the “records returned when using a Perl API gliderecord query.” On the Direct Web Services wiki page there is an example of Perl code using windowing, with the following comment:

Using a windowed query mechanisms overcomes the default limitation of only getting a maximum of 250 records per query.

“Default” implies that the limitation can be overridden. I assumed that the purpose of the system property was to override the default.

This is, in fact, the behavior or the property. It raises the “hard-limit”. If you set the property, it will increase the number of records returned by the getRecords SOAP method. It is not Perl specific. It works for any language. I assumed that setting the property was the only way to get more than 250 record in a single SOAP call. There is a __limit extended query parameter documented; but I assumed that this parameter was for retrieving fewer records, and that it did not allow you to bypass the “hard-limit”.

Not so!

The “hard-limit” is not a hard-limit at all! It is a soft-limit. It turns out that there is an easy way to get more than 250 records, and it does not require changing a system property. All you need to do is ask! By simply passing in the __limit parameter, you can override the “hard-limit”.

To test this theory, I modified the Visual Basic example from my previous post. This code now returns a list of all the users in single Web Services call.

Dim objGetUsers : Set objGetUsers = New ServiceNowDirectWS
objGetUsers.SetMethod "sys_user", "getRecords"
objGetUsers.SetValue "__limit", "2000"
If objGetUsers.Post Then
  Dim nRows : nRows = objGetUsers.GetRowCount
  Dim i : For i = 1 To nRows
    WScript.Echo i & ": " & objGetUsers.GetRowValue(i, "user_name") & _
      ": " & objGetUsers.GetRowValue(i, "name")
  Next
Else
  WScript.Echo "Error: " & objGetUsers.Status
  WScript.Echo objGetUsers.StatusText
End If

Obviously we do not want to get carried away. It is fine to bump up the limit if you are trying to retrieve a list of your 1500 users. To retrieve your list of 150,000 servers, you are still strongly advised to use windowing.

The thing I am still trying to figure out is: Has it always worked this way? Was the hard-limit really a hard-limit in the beginning, and then it was reduced to a soft-limit? Or was it always a soft-limit, and the wiki page simply written to discourage developers from bypassing the limit. Please leave a comment if you know the answer.