Problem creating SOAP integration to Planview

Matt Small1 · ‎09-03-2015

I have been tasked with trying to set up a web service integration between ServiceNow and Planview and running into a hurtle and looking for some assistance or ideas. I am still green on web services but logically what I need to do seems simple. What is available on the Planview side is a bit fuzzy but i am trying anyway. I know I am going to have to do a 2 step process where I request to get a token an and then use that token to query (starting simple for now) a table in Planview and get some data back. I know eventually this will have to be a scripted function as I have seen in some other posts on REST but i am just trying to get past the basics first. Here is what I have so far -

Note: I can do this process using SOAPUI and manually manipulating the header so I know can technically connect but trying to get that into ServiceNow is where i am stuck.

Get Token

1. Connect to the https://yourdomain.pvcloud/testing/services/LoginService.svc

2. call the login function and pass datasource, userid, password.

3. The LoginResponse is a token key- S-vxEmB29KOj7iUpOSRTJdx7pmI8w......

Query -

1. Connect to the https://yourdomain.pvcloud/testing/services/QueryService.svc

2. call the Read function and define some query. (no issue here)

Here is where I am having trouble. In SOAPUI I can add a header parameter of Cookie and Value of LoginCert=myreturntokenvalue from above.

Here and example of what the Raw SOAPUI looks like -

POST https://yourdomain/testing/services/QueryService.svc HTTP/1.1

Accept-Encoding: gzip,deflate

Content-Type: text/xml;charset=UTF-8

SOAPAction: "http://schemas.planview.com/PlanviewEnterprise/Services/QueryService/2012/12/IQueryService/Read"

Cookie: LoginCert=SjsKZEVVgceSUZ9iGwuXqcJAf7L........

Content-Length: 863

Host: Yourdomain

Connection: Keep-Alive

User-Agent: Apache-HttpClient/4.1.1 (java 1.5)

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://schemas.planview.com/PlanviewEnterprise/Services/QueryService/2012/12">

<soapenv:Header/>

<soapenv:Body>

<ns:Read>

<ns:sqlQuery>select

pe.planning_code,

pe.ppl_code,

(select description from ip.structure where structure_code = pe.ppl_code) Project_name,

pe.short_name work_id,

(select description from ip.structure where structure_code =pe.code20) Project_status,

(select top 1 (select full_name from ip.ip_user where user_name=wf.user_name)

from ip.WF_TEAM wf where workflow_id = (select workflow_id from ip.WORKFLOW where planning_code = pe.ppl_code) and wf.lifecycle_role_code= '249') PM

from ip.planning_entity pe

where

pe.ppl_code= pe.planning_code</ns:sqlQuery>

</ns:Read>

</soapenv:Body>

</soapenv:Envelope>

That 'Cookie' parameter is getting passed in the the HTTP header (I believe). It is not something that is set in the XML soap Header. My challenge is I don't know how to set these Cookie value in the HTTP header. Would like to know if other have done this type of thing before and how did they solve.

Matt Small1 · ‎09-17-2015

So I was able to get this working with a little trial and error. I create 2 soap messages to account for the LoginService and to the iQueryService in planview. Then is was a matter of scripting so I could get of token value, parse it and then script to query. Here is the scripted code (sorry I don't know how to insert it in those pretty JS editor sceens like in other posts).

//Perform "Open Session" Web Service call with proper credentials to get the token

var s = new SOAPMessage('Planview - Login', 'Login');

s.setParameter('userName','youruser');

s.setParameter('password', 'yourpassword');

s.setParameter('datasource', 'your_ds');

var response = s.post();

var xmldoc = new XMLDocument(response);

var LoginResponse = xmldoc.getNodeText("//LoginResult")

var token = 'LoginCert='+LoginResponse;

//Now that you have token pass in the HTTP header to do your query

//modified from here http://wiki.servicenow.com/index.php?title=Scripting_Outbound_SOAP#Sending_a_Direct_SOAP_Message

var requestBody;

var responseBody;

var status;

var sm;

try{

sm = new sn_ws.SOAPMessageV2("Planview - iquery", "Read");

sm.setRequestHeader('Cookie', token) //token from first WS call

sm.setStringParameter('sqlQuery', "your SQL query here);

sm.setHttpTimeout(10000) //In Milli seconds. Wait at most 10 seconds for response from http request.

response = sm.execute();//Might throw exception if http connection timed out or some issue with sending request itself because of encryption/decryption of password and stuff

responseBody = response.haveError() ? response.getErrorMessage() : response.getBody();

status = response.getStatusCode();

} catch(ex) {

responseBody = ex.getMessage();

status = '500';

} finally {

requestBody = sm ? sm.getRequestBody():null;

}

var xmldoc2 = new XMLDocument(responseBody);

gs.print("Request Body: " + requestBody);

gs.print("Response: " + xmldoc2);

gs.print("HTTP Status: " + status);

I should be able to parse through the XML response and use however i need but I was able to get over this hurdle.

View solution in original post

whitneyjiang · ‎10-20-2017

I have a question: for my case, we have to bypass a proxy server to reach to the target Planview site, how can I setup proxy server in ServiceNow?

From wiki site, we need to setup 4 sys properties for proxy server, but I don't see how these properties can connect to that particular soap message. We have a lot of soap messages, does that mean all outbound soap messages will have to bypass the same proxy server?

We are currently in Geneva version.

Appreciated your help.

thanks,

whitney

Phonsie Hevey1 · ‎08-18-2020

Thanks for the details here on the integration.

With regards to parsing the data coming back another way of doing this server side is using gs.getXMLText(string, XPATH expression)

var responseBody = response.getBody();
var status = response.getStatusCode();
var status_from_xml = gs.getXMLText(responseBody, "//status");

I think that's easier if you just need a few elements from the XML.

You can search for "Parse XML payloads" in the docs for more information.