Need help getting REST API calls to work using WinHttpRequest from VBA

Our goal is to create a Ticket in RT using VBA using the WinHttpRequest
object. We understand that there are 2 login pages involved with RT.

So, first we log into the first RT login page using using a “POST” request
and passing the username and password using the SetCredentials function of
the WinHttpRequest object. This request appears to return a valid session
cookie which we parse to use in our second WinHttpRequest request.

In making our second request we use a “GET” request setting the 2nd RT
login username and password using the SetCredentials function and in
addition we now are passing the session cookie (that is we send a portion
of the session cookie returned from the previous request) info using the
SetRequestHeader (as in SetRequestHeader “Cookie”, mvarSessionCookie ). In
this second request we are asking for an RT Ticket to be returned using
"https://(our url)/REST/1.0/ticket/(a ticket #)/show". However, we receive
the 401 Authorization error instead.

Some sample code would be great.

*Below is the code we are using presently to verify that the session cookie
we receive from the 1st RT login page is valid by *

using a second request to return an existing RT ticket (which is hard
coded for testing purposes):

Private Const cnstBase_URL As String = “https://[our RT url]”

With WinHttpReq
    'either of the following lines seems to work and return a cookie
    .Open "POST", cnstBase_URL

    .SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1;

WOW64; rv:31.0) Gecko/20100101 Firefox/31.0"
.SetRequestHeader “Connection”, “keep-alive”

    'user name and password for 1st RT login page/url
.SetCredentials cnstRequestor, cnstPWD,

HTTPREQUEST_SETCREDENTIALS_FOR_SERVER

    On Error Resume Next
    .Send

    If Err.Number = 0 Then
        If .Status = "200" Then
            On Error Resume Next
            output_Cookie = .getResponseHeader("Set-Cookie")
            On Error GoTo 0

            myCookie = Split(output_Cookie, ";")
            If UBound(myCookie) > 0 Then
                'implicit conversion to string
                mvarSessionCookie = myCookie(0)
            End If
        Else
            Debug.Print "HTTP " & .Status & " " & .StatusText
        End If
    Else
        Debug.Print "Error " & Err.Number & " " & Err.Source & " " &

Err.Description
End If
On Error GoTo 0

End With

Set WinHttpReq = Nothing

If Trim(mvarSessionCookie) = "" Then Exit Function

'perform second request
Set WinHttpReq = New WinHttp.WinHttpRequest
With WinHttpReq
    'get ticket data
    Dim TargetURL As String

    'to test cookie, display a ticket
    'hard coded for testing as this works from the Browser which I

thought would be a good test
’to see if the Cookie variable works
TargetURL = “https://[our RT url]/REST/1.0/ticket/96494/show”

    .Open "GET", TargetURL, False

    .SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1;

WOW64; rv:31.0) Gecko/20100101 Firefox/31.0"
.SetRequestHeader “Connection”, “keep-alive”

    .SetRequestHeader "Cookie", mvarSessionCookie

    'user name and password for 2nd RT login page/url
.SetCredentials cnstBasic_Auth_User, cnstBasic_Auth_PWD,

HTTPREQUEST_SETCREDENTIALS_FOR_SERVER

    On Error Resume Next
    .Send

    If Err.Number = 0 Then
        If .Status = "200" Then
            Debug.Print
            Debug.Print .ResponseText
            Debug.Print .GetAllResponseHeaders
        Else
            Debug.Print "HTTP " & .Status & " " & .StatusText
        End If
    Else
        Debug.Print "Error " & Err.Number & " " & Err.Source & " " &

Err.Description
End If
On Error GoTo 0

    Debug.Print .GetAllResponseHeaders

End With

Set WinHttpReq = Nothing

Thanks, in advance,
Tim

Hi,
you will probably find what you need by looking at the source code of my RTChecker project.
Look here: https://www.assembla.com/spaces/rtcheckerv2

Hope this helps
CrisOn 05/01/2015 04:39 PM, Tim Elkin wrote:
Our goal is to create a Ticket in RT using VBA using the WinHttpRequest object. We understand that there are 2 login pages involved with RT.

So, first we log into the first RT login page using using a “POST” request and passing the username and password using the SetCredentials function of the WinHttpRequest object. This request appears to return a valid session cookie which we parse to use in our second WinHttpRequest request.

In making our second request we use a “GET” request setting the 2nd RT login username and password using the SetCredentials function and in addition we now are passing the session cookie (that is we send a portion of the session cookie returned from the previous request) info using the SetRequestHeader (as in SetRequestHeader “Cookie”, mvarSessionCookie ). In this second request we are asking for an RT Ticket to be returned using "https://(our url)/REST/1.0/ticket/(a ticket #)/show"https://(oururl)/REST/1.0/ticket/(aticket#)/show. However, we receive the 401 Authorization error instead.

Some sample code would be great.

Below is the code we are using presently to verify that the session cookie we receive from the 1st RT login page is valid by
using a second request to return an existing RT ticket (which is hard coded for testing purposes):

Private Const cnstBase_URL As String = "https://[our RT url]"

With WinHttpReq
    'either of the following lines seems to work and return a cookie
    .Open "POST", cnstBase_URL

    .SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:31.0) Gecko/20100101 Firefox/31.0"
    .SetRequestHeader "Connection", "keep-alive"

    'user name and password for 1st RT login page/url
.SetCredentials cnstRequestor, cnstPWD, HTTPREQUEST_SETCREDENTIALS_FOR_SERVER

    On Error Resume Next
    .Send

    If Err.Number = 0 Then
        If .Status = "200" Then
            On Error Resume Next
            output_Cookie = .getResponseHeader("Set-Cookie")
            On Error GoTo 0

            myCookie = Split(output_Cookie, ";")
            If UBound(myCookie) > 0 Then
                'implicit conversion to string
                mvarSessionCookie = myCookie(0)
            End If
        Else
            Debug.Print "HTTP " & .Status & " " & .StatusText
        End If
    Else
        Debug.Print "Error " & Err.Number & " " & Err.Source & " " & Err.Description
    End If
    On Error GoTo 0

End With

Set WinHttpReq = Nothing

If Trim(mvarSessionCookie) = "" Then Exit Function

'perform second request
Set WinHttpReq = New WinHttp.WinHttpRequest
With WinHttpReq
    'get ticket data
    Dim TargetURL As String

    'to test cookie, display a ticket
    'hard coded for testing as this works from the Browser which I thought would be a good test
    'to see if the Cookie variable works
    TargetURL = "https://[our RT url]/REST/1.0/ticket/96494/show"

    .Open "GET", TargetURL, False

    .SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:31.0) Gecko/20100101 Firefox/31.0"
    .SetRequestHeader "Connection", "keep-alive"

    .SetRequestHeader "Cookie", mvarSessionCookie

    'user name and password for 2nd RT login page/url
.SetCredentials cnstBasic_Auth_User, cnstBasic_Auth_PWD, HTTPREQUEST_SETCREDENTIALS_FOR_SERVER

    On Error Resume Next
    .Send

    If Err.Number = 0 Then
        If .Status = "200" Then
            Debug.Print
            Debug.Print .ResponseText
            Debug.Print .GetAllResponseHeaders
        Else
            Debug.Print "HTTP " & .Status & " " & .StatusText
        End If
    Else
        Debug.Print "Error " & Err.Number & " " & Err.Source & " " & Err.Description
    End If
    On Error GoTo 0

    Debug.Print .GetAllResponseHeaders

End With

Set WinHttpReq = Nothing

Thanks, in advance,
Tim

By using the following code placed into a class called clsRT_Ticket I was
able to create RT Tickets within VBA successfully. Hope this helps others
that have run into this issue with VBA.

'Must Include Microsoft WinHTTP Services, version 5 (winhttp.dll) in your
reference

‘NOTE: to call CreateTicket function from code
’Set RT_Ticket = New clsRT_Ticket
’With RT_Ticket
’ .ProcessBar = Me.StatusBar
’ .DisplayTicketInfoUponCreation = True
’ .Body = sbody
’ .PIDs = cPIDs
’ .BankID_Old = sOrgReturnedCheck_BankID
’ .BankID_New = ReturnedCheck.BankId
’ .CreateTicket
’End With
’Set RT_Ticket = Nothing

Private WinHttpReq As WinHttpRequest

Private Const multiPartBoundary As String = “–xYzZY”

Private mvarRT_URL As String
Private mvarRT_CreateTICKET As String
Private mvarRT_ShowTICKET As String
Private mvarRT_Auth_User As String
Private mvarRT_Auth_PWD As String

Private mvarSessionCookie As String
Private mvarCookieHolder As Variant

Private mvarBody As String
Private mvarRequestor As String
Private mvarSubject As String
Private mvarTicketNumber As String

Private Sub Class_Initialize()
'create the WinHttpRequest object
Set WinHttpReq = New WinHttpRequest

mvarRT_URL = "https://(RT server):#####"  'where ##### points to a valid

port if needed
mvarRT_CreateTICKET = mvarRT_URL & "/REST/1.0/ticket/new"
mvarRT_ShowTICKET = mvarRT_URL & "/REST/1.0/ticket/%ticket%/SHOW"
mvarRT_Auth_User = "(valid RT user name)"
mvarRT_Auth_PWD = "(valid RT user password)"
mvarRequestor = “(valid RT requestor name)”

mvarBody = ""
mvarSubject = ""
mvarTicketNumber = ""

End Sub

Private Sub Class_Terminate()
Set WinHttpReq = Nothing

End Sub

Public Function CreateTicket() As Boolean
Dim sMultiPartData As String
Dim sCustomFieldValue As String 'value to be placed into custom
field if used

mvarBody = "text to be displayed in body of "
mvarSubject = ""

'setup multipart/form-data to be passed to RT request
sMultiPartData = ""
sMultiPartData = multiPartBoundary & vbCrLf
sMultiPartData = sMultiPartData + "Content-Disposition: form-data;

name="“content”"" + vbCrLf + vbCrLf
sMultiPartData = sMultiPartData + “id: ticket/new” + vbCrLf
sMultiPartData = sMultiPartData + "Subject: " + mvarSubject + vbCrLf
sMultiPartData = sMultiPartData + "Text: " + mvarBody + vbCrLf
sMultiPartData = sMultiPartData + "Requestor: " & mvarRequestor & vbCrLf

'had to include next statement twice to get custom field values to be

accepted in RT
sMultiPartData = sMultiPartData + "CF-(name of custom field): " +
sCustomFieldValue + vbCrLf
sMultiPartData = sMultiPartData + "CF-(name of custom field): " +
sCustomFieldValue + vbCrLf

sMultiPartData = sMultiPartData + "Queue: General" + vbCrLf
sMultiPartData = sMultiPartData + "--xYzZY--"

With WinHttpReq
    If getValidSessionCookie Then   'get cookie from first call to RT
        'Debug.Print .ResponseText
        
        '2) create New RT ticket
        .Open "POST", mvarRT_CreateTICKET
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1;

WOW64; rv:38.0) Gecko/20100101 Firefox/38.0"
.setRequestHeader “Accept”,
“text/html,application/xhtml+xml,application/xml;q=0.9,/;q=0.8”
.setRequestHeader “Accept-Language”, “en-US,en;q=0.5”
.setRequestHeader “DNT”, “1”
.setRequestHeader “Referer”, mvarRT_CreateTICKET
.setRequestHeader “Cookie”, mvarSessionCookie
.setRequestHeader “Connection”, “keep-alive”
.setRequestHeader “Content-Type”, “multipart/form-data;
boundary=xYzZY”
.setRequestHeader “Content-Length”, CStr(Len(sMultiPartData))
.send (sMultiPartData)

        If .Status = 200 Then
            mvarTicketNumber = Trim(.responseText)
            If InStr(mvarTicketNumber, "401 Credentials required") < 1

Then
’get RT ticket number
mvarTicketNumber = Replace(mvarTicketNumber, Chr(10),
"")
If mvarTicketNumber <> “” Then
’return just the RT ticket number
mvarTicketNumber = Mid(mvarTicketNumber,
InStr(mvarTicketNumber, "# Ticket “) + Len(”# Ticket "))
mvarTicketNumber = Replace(mvarTicketNumber, “
created.”, “”)
End If

                Debug.Print "RT Ticket #" & CStr(mvarTicketNumber) & "

created."
Else
Debug.Print .responseText
Debug.Print .getAllResponseHeaders
End If
Else
Debug.Print .responseText
Debug.Print .getAllResponseHeaders
End If
Else
’cannot establish a connection to RT server, so try again up to
3 attempts only
Debug.Print .responseText
Debu8g.Print .getAllResponseHeaders
End If

End With

End Function

Public Function ShowTicket(ByVal sTicketNumber As String) As Boolean

If getValidSessionCookie Then   'get initial request and cookie to pass

to RT request
’3) show RT ticket information returned
With WinHttpReq
.Open “GET”, Replace(mvarRT_ShowTICKET, “%ticket%”,
sTicketNumber)
.setRequestHeader “User-Agent”, “Mozilla/5.0 (Windows NT 6.1;
WOW64; rv:38.0) Gecko/20100101 Firefox/38.0”
.setRequestHeader “Accept”,
“text/html,application/xhtml+xml,application/xml;q=0.9,/;q=0.8”
.setRequestHeader “Accept-Language”, “en-US,en;q=0.5”
.setRequestHeader “DNT”, “1”
.setRequestHeader “Cookie”, mvarSessionCookie
.setRequestHeader “Connection”, “keep-alive”
.send

        If .Status = 200 Then
            Debug.Print .responseText
        End If
    End With
End If

End Function

Private Function getValidSessionCookie() As Boolean
Dim sMsg As String

With WinHttpReq
        
    .Option(4) = 13056  'WinHttpRequestOption_SslErrorIgnoreFlags 13056:

ignore all err,

    '1) get cookie to reuse (DO NOT CHANGE!!!)
    .Open "POST", mvarRT_URL
    .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; WOW64;

rv:38.0) Gecko/20100101 Firefox/38.0"
.setRequestHeader “Accept”,
“text/html,application/xhtml+xml,application/xml;q=0.9,/;q=0.8”
.setRequestHeader “Accept-Language”, “en-US,en;q=0.5”
.setRequestHeader “DNT”, “1”
.setRequestHeader “Connection”, “keep-alive”
.setRequestHeader “Content-Type”,
“application/x-www-form-urlencoded”
.setRequestHeader “Content-Length”, Len(“user=” & mvarRT_Auth_User &
"&pass=" & mvarRT_Auth_PWD)
.send (“user=” & mvarRT_Auth_User & “&pass=” & mvarRT_Auth_PWD)

    If .Status = 200 Then
        mvarSessionCookie = .getResponseHeader("Set-Cookie")

        mvarCookieHolder = Split(mvarSessionCookie, ";")
        If UBound(mvarCookieHolder) > 0 Then
            'implicit conversion to string
            mvarSessionCookie = mvarCookieHolder(0)
        End If
    End If
    
End With

End Function

View this message in context: http://requesttracker.8502.n7.nabble.com/Need-help-getting-REST-API-calls-to-work-using-WinHttpRequest-from-VBA-tp59981p61745.html