Re: [rt-users] Need help getting REST API calls to work using WinHttpRequest from VBA
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
Re: [rt-users] Need help getting REST API calls to work using WinHttpRequest from VBA
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 Cris On 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 #)/showhttps://(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
[rt-users] 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