question Problem creating SOAP integration to Planview in Developer forum

Problem creating SOAP integration to Planview

Matt Small1 — Fri, 04 Sep 2015 06:09:27 GMT

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.

Re: Problem creating SOAP integration to Planview

coryseering — Fri, 04 Sep 2015 17:36:31 GMT

Hi Matt,

I assume you'll be using the SOAPMessageAPIV2 for this. The API allows you to set arbitrary HTTP headers:

https://wiki.servicenow.com/index.php?title=SOAPMessageV2_API#setRequestHeader.28String_headerName.2C_String_headerValue.29

developer API - SOAPMessageV2 - setRequestHeader

Have you tried scripting this at all yet? It's hard to troubleshoot problems or explain how to do it when there isn't anything to troubleshoot or tweak yet. I suggest setting up a simple message in ServiceNow and just trying to get the token back from Planview. Once you do that, you can take that token and try your next message programmatically. Post the script you use here, as you run into problems, and the community can help you get over the hurdles as they come up.

Right now your biggest hurdle is that you haven't started doing it in ServiceNow yet.

Re: Problem creating SOAP integration to Planview

Matt Small1 — Fri, 04 Sep 2015 17:49:03 GMT

I have set up the Soap message to get the token and am successful at that i just was trying to see how to modify the HTTP header in the outbound SOAP configuration on the queryservice. Sorry i didn't make that clear earlier. I will work on scripting Step 2 after getting the token and see how far I get. Thanks.

Re: Problem creating SOAP integration to Planview

Matt Small1 — Thu, 17 Sep 2015 23:43:21 GMT

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.

Re: Problem creating SOAP integration to Planview

coryseering — Wed, 07 Oct 2015 16:14:01 GMT

Hi Matt,

Awesome! If you issue more queries after the first, be sure to store and re-use the cookie so you don't need to authenticate with every transaction- just once at the start.

Re: Problem creating SOAP integration to Planview

whitneyjiang — Fri, 20 Oct 2017 19:44:09 GMT

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

Re: Problem creating SOAP integration to Planview

Phonsie Hevey1 — Tue, 18 Aug 2020 11:26:55 GMT

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.