ServiceNow: SOAP Journal

Integration using Web Services

Category Archives: Visual Basic

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.

VB Script

Several weeks ago I had to write some Visual Basic code to access ServiceNow. I created a simple Class to do most of the dirty work. You may find it useful if you have a similar requirement to access ServiceNow from VBScript. Here is the Class.

Option Explicit

Const gServiceNowUser = "admin"
Const gServiceNowPass = "admin"
Const gServiceNowURL = "https://demo000.service-now.com/"

Class ServiceNowDirectWS
  ' Use this class to call ServiceNow Direct Web Services functions
  ' For documentation on the Direct WS API see:
  ' http://wiki.servicenow.com/index.php?title=Direct_Web_Service_API_Functions

  Dim sEndpointURL, sTableName, sMethod, sResponsePath
  Dim oWSRequest, oWSRequestDoc, oWSResponseDoc
  Dim oWSRequestEnvelope, oWSRequestBody, oWSRequestOperation

  Public Sub SetMethod (tableName, method)
    ' This function must be called BEFORE Post to initialize the class
    ' method must be "insert", "update", "getKeys", "get" or "getRecords"
    sTableName = tableName
    sMethod = method
    sResponsePath = "/soap:Envelope/soap:Body/" & sMethod & "Response/"
    sEndpointURL = gServiceNowURL & sTableName & ".do?SOAP"
    If (sMethod = "get" Or sMethod = "getRecords") Then
      sEndpointURL = sEndpointURL & "&displayvalue=all"
    End If
    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:" & sMethod)
    oWSRequestOperation.setAttribute "xmlns:tns", _
      "http://www.service-now.com/" & sTableName
    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
    ' If method is "insert" then it can be used to obtain the sys_id of the inserted record
    ' If method is "get" then it can be used to obtain any field from the record
    GetValue = oWSResponseDoc.selectSingleNode(sResponsePath & fieldname).text
  End Function
  
  Public Function GetRowCount
    ' This function may be called after Post if the method is "getRecords"
    ' It returns the number of records in the result set
    Dim sResultsPath, oNodeset    
    sResultsPath = sResponsePath & "getRecordsResult"
    Set oNodeSet = oWSResponseDoc.selectNodes(sResultsPath)
    getRowCount = oNodeSet.length   
  End Function
  
  Public Function GetRowValue(rownum, fieldname)
    ' This function may be called after Post if the method is "getRecords"
    ' It returns a single field from a single record
    Dim sRowPath, sFieldPath
    sRowPath = sResponsePath & "getRecordsResult[" & rownum & "]/"
    sFieldPath = sRowPath & fieldname
    GetRowValue = oWSResponseDoc.selectSingleNode(sFieldPath).text  
  End Function
  
End Class

Note that this code uses three global variables: gServiceNowUser, gServiceNowPass and gServiceNowURL. Obviously, you will need to change these values.

Now that we have the class, we can use it to do some work. Here is a simple example that creates an incident ticket.

' Specify the ticket values
Dim wsInsertIncident : Set wsInsertIncident = New ServiceNowDirectWS
wsInsertIncident.SetMethod "incident", "insert"
wsInsertIncident.SetValue "short_description", "Demo WS Incident"
wsInsertIncident.SetValue "description", "Demo WS Incident"
wsInsertIncident.SetValue "caller_id", "Abel Tuter"
wsInsertIncident.SetValue "category", "hardware"
wsInsertIncident.SetValue "subcategory", "mouse"

' Perform the insert and check the status
If Not wsInsertIncident.Post Then
  WScript.Echo "Error=" & wsInsertIncident.Status 
  WScript.Echo wsInsertIncident.StatusText
  WScript.Quit
End If

Dim strIncidentSysId, strIncidentNumber
strIncidentSysId = wsInsertIncident.GetValue("sys_id")
strIncidentNumber = wsInsertIncident.GetValue("number")
WScript.Echo "Inserted: " & strIncidentNumber

Note that these examples are using WScript.Echo. This code is executed using Microsoft CScript.exe.

Here is a example that prints a list of the users in the sys_user table.

Dim objGetUsers : Set objGetUsers = New ServiceNowDirectWS
objGetUsers.SetMethod "sys_user", "getRecords"
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

The above example highlights the 250 row limitation of direct web services. Although there are more users, the script only returns the first 250. The following is a more complex example that fixes the problem. This code fetches the records in chunks of 100 until there are no more chunks available.

Dim objGetUsers : Set objGetUsers = New ServiceNowDirectWS
objGetUsers.SetMethod "sys_user", "getRecords"
objGetUsers.SetValue "__order_by", "user_name"
Dim done, firstRow : done = False : firstRow = 0
Do While Not done
  objGetUsers.SetValue "__first_row", firstRow
  objGetUsers.SetValue "__last_row", firstRow + 100
  If objGetUsers.Post Then
    Dim nRows : nRows = objGetUsers.GetRowCount
    If nRows > 0 Then
      Dim i : For i = 1 To nRows
        WScript.Echo (firstRow + i) & ": " & objGetUsers.GetRowValue(i, "user_name") & _
          ": " & objGetUsers.GetRowValue(i, "name")
      Next
      firstRow = firstRow + 100
    Else
      done = True
    End If
  Else
    WScript.Echo "Error: " & objGetUsers.Status
    WScript.Echo objGetUsers.StatusText
    done = True
  End If
Loop