ServiceNow: SOAP Journal

Integration using Web Services

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
Advertisements

6 responses to “VB Script

  1. JamesN903 2013/10/31 at 6:12 pm

    Reblogged this on 14 Emerton @EcoStratus Technologies and commented:
    Simply Awesome! Thank you!

  2. Craig Talbert 2014/01/16 at 4:30 pm

    Thanks! This is really helpful code, we’re currently doing something similar with VB but I like this approach more.

  3. Pingback: Scripted Web Service from VB | ServiceNow: SOAP Journal

  4. Adam 2014/07/23 at 4:27 pm

    Hi this seems really great. One question though…I just got an error executing it ‘VBscript runtime error: Object Required: ‘oWSResponseDoc.selectSingleNode(…)’
    Have you seen this?

  5. Rafique Mohammed 2016/09/11 at 4:07 am

    Does this work with ServiceNow Eureka release??
    It is not returning ‘GetRecords’ results for me.
    whereas SOAPUI works fine.

    • gflewis 2016/10/01 at 6:11 pm

      Hi Rafique. I do not have a Eureka release handy to test with, but it should work fine. Double check that your credentials are correct.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: