ServiceNow: SOAP Journal

Integration using Web Services

Monthly Archives: March 2014

Getting Distinct Records


Every once in a while, I run into a situation where I need a list of distinct values from a table. The latest situation I ran into for this, I needed to get a distinct list of every user that had an open Project Task.

The easiest way to do this of course is to go to a list view, and sort by the field you care about. For example:

Project Tasks by User Grouping Project Tasks by Assigned To


For my purposes though, I wanted to send out scripted email notifications to everyone that had an open task. As a result, I needed this information available to me through scripting, which caused me to look for a different solution.

The answer came in the form of “GlideAggregate”. This is a Service-Now custom JavaScript object that functions very similar to the popular “GlideRecord”, with a few key differences.

GlideAggregate is built to…

View original post 159 more words

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", _
    Set oWSRequestBody = oWSRequestDoc.createElement("soap:Body")
    Set oWSRequestOperation = oWSRequestDoc.createElement("tns:execute")
    oWSRequestOperation.setAttribute "xmlns:tns", _
      "" & 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 "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", _
      Post = True
      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)
  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")
  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.