PeopleSoft Reporting Web Services

While browsing through Peoplebooks for API syntax I ran into this topic related to Reporting web services and I was pleasantly surprised that this feature is available out of the box but I don't think is used frequently. Looks like it has been around since 8.56.x  or maybe even before. So PeopleSoft provides SOAP as well as REST based web services to expose PeopleSoft data based on PS Query or Connected query to external systems. I think this is pretty cool. So as long as the data set can be constructed in PS Query or Connected Query it can be exposed out to a 3rd party service using out of the box infrastructure. This greatly simplifies elaborate design and development time of application engine programs which are generally used for building interfaces.

PeopleSoft provides QAS_QRY_SERVICE which is the service for Query Access Manager.

and PROCESSREQUEST which is the service for Process Scheduler. Via these services once can create and get query and process request type items. I tried out the query service to execute a query via Postman and it works like a charm.

There is lot of good info in PeopleBooks so I won't repeat all of that here. So I currently have two RSS feeds based on PS Query which I use for monitoring the status of process monitor processes and jobs and another one which monitors the asynchronous  services. I used these as the test cases to fetch data using the web services instead of RSS. Also setup a user which has access to the service operation and to the queries and can only run the queries. 

As I have the queries already built I tested out the REST service operation QAS_EXECUTEQRY_REST_GET to execute the queries. 

Example of the URI Template:

{OwnerType}/{QueryName}/{OutResultType}/{OutResultFormat}?isconnectedquery=
{isConnectedQuery}&maxrows={MaxRow}&prompt_psqueryname={Prompt_PSQueryName*}
&prompt_uniquepromptname={Prompt_UniquePromptName*}&prompt_fieldvalue=
{Prompt_FieldValue*}&filterfields={FilterFields*}&json_resp={json_response}

Provided QueryName, OwnerName is PUBLIC as the query I am using is public query, OutResultType is JSON as I want the results back in JSON format, OutResultFormat is NONFILE as I want the response in message object, isconnectedquery = N, maxrows set it to 100 ( I would never get so many rows back), json_resp set it to true, did not set anything for rest of the URI parameters. 

HTTP GET https://myserver/PSIGW/RESTListeningConnector/PSFT_HR/ExecuteQuery.v1/PUBLIC/N_MY_QUERY/JSON/NONFILE?isconnectedquery=N&maxrows=100&prompt_psqueryname=&prompt_uniquepromptname=&prompt_fieldvalue=&filterfields=&json_resp=true 

The response is received as follows:

{

   "status":"success",

   "data":{

      "query":{

         "numrows":3,

         "queryname=":"N_MY_QUERY",

         "rows":[

            {

               "attr:rownumber":1,

               "PRCSINSTANCE":558373,

               "MAINJOBINSTANCE":558373,

               "PRCSJOBNAME":"MYJOB",

               "PRCSTYPE":"PSJob",

               "PRCSNAME":"MYPRCS",

               "OPRID":"PS",

               "RUNCNTLID":"MYRUN",

               "RECURNAME":"Daily",

               "RUNDTTM":"2020-07-04T12:15:00-0400",

               "RUNSTATUS":"Processing",

               "DISTSTATUS":"Scheduled"

            },

            {

               "attr:rownumber":2,

               "PRCSINSTANCE":558374,

               "MAINJOBINSTANCE":558373,

               "PRCSJOBNAME":"MYJOB",

               "PRCSTYPE":"SQR Process",

               "PRCSNAME":"MYSQRPRCS",

               "OPRID":"PS",

               "RUNCNTLID":"MYRUN",

               "RECURNAME":"Daily",

               "RUNDTTM":"2020-07-04T12:15:00-0400",

               "RUNSTATUS":"Processing",

               "DISTSTATUS":"Scheduled"

            },

            {

               "attr:rownumber":3,

               "PRCSINSTANCE":558375,

               "MAINJOBINSTANCE":558373,

               "PRCSJOBNAME":"MYJOB",

               "PRCSTYPE":"SQR Report",

               "PRCSNAME":"MYPROCESS",

               "OPRID":"PS",

               "RUNCNTLID":"MYRUN",

               "RECURNAME":"Daily",

               "RUNDTTM":"2020-07-04T12:15:00-0400",

               "RUNSTATUS":"Pending",

               "DISTSTATUS":"Scheduled"

            }

         ]

      }

   }

}

Following is an example when query does not return any results.

{

   "status":"success",

   "data":{

      "query":{

         "numrows":0,

         "queryname=":"N_MY_QUERY",

         "rows":[

         ]

      }

   }

}

Native sFTP transmission in PeopleSoft using RSA Keys

I have been using PeopleSoft's URL definition to setup sftp based transmission based on password authentication successfully. Recently was challenged to use sFTP but with RSA keys. Seemed simple at first but then as I dug deeper found out that there are a lot of nuances that one needs to be aware off. So here is what I learnt so far. I am using PT 8.53.26 for this exercise.

At a high level the steps are
1. Create a RSA public/private key pair. I am using PuTTY generator but there are a lot of other tools out there which do the same.
2. Create a URL definition in PeopleSoft and associate the RSA keys with the same.

So lets look at creating the RSA key pair first.

Launch the PuTTY Key Generator and select SSH-2 RSA (default) and then click on the Generate button. Once complete the public key, key fingerprint and key comment values are displayed. Key comment can be updated to some other value or can be left as-is. Put in a key passphrase.
Save the keys by clicking on the "save public key" and "save private key" button respectively.
Use .pub extension for the  public key and .ppk for the private key. Filename can be anything for your choice. So for this discussion we will call this putty-gen.pub and putty-gen.ppk.

The highlighted text in blue in the above picture is the public key in OpenSSH format so copy that and then paste it to a text file. We will name this file sftpuser. No extension just sftpuser. I will explain shortly the reason behind this naming convention.

Provide putty-gen.pub or sftpuser file to the vendor so that they can add it to their sftp server.
Once vendor sets an account for you they will provide the sftp address, port is generally 22 and an userid. This userid should match the filename, so we will consider that the userid created by the external vendor is called sftpuser. Hence the naming convention. When you send the public key to the vendor you wouldn't know the userid so at that point the file can be called anything, the filename is more critical to the PeopleSoft setup than the vendor's setup.

Via the PuTTY gen tool navigate to Conversions > export OpenSSH Key and export the private key to a text file. Name this file sftpuser.ppk

You can always go back in time load the privatekey "putty-gen.ppk" file in PuTTY Generator and update the private key password.

While the vendor is doing their setup and configuration we have following two options in PeopleSoft.
The first one and the preferred one is to import the keys in the Digital Certificates component as this options stores them in the database. The second option is to store the keys on the file server. We will discuss both the options here.

Digital Certificate Option (preferred option)

Navigate to PeopleTools > Security > Security Objects > Digital Certificates and add a new row. Select Type as SSH, provide some value for Alias and then a "Copy", hyperlink should show up. Click on that link and then paste the contents of sftpuser.ppk and sftpuser in the appropriate boxes and click on Save (which is to the bottom-right of the page).

Next navigate to PeopleTools > Utilities > Administration > URLs and create a new URL.

Add in the sftp URL provided by the vendor. It could be something like

sftp://vendorname.com

or  sftp://vendorname.com/~/drop-directory

or sftp://vendorname.com/drop-directory

The ~ indicates that a successful logon takes you to the home directory and from there you can navigate to lets say a sub-directory called as "drop-directory" where you have to drop the files.

Then click on the URL Properties link

Provide the following properties

AUTHTYPE = 1 - PUBLICKEY 

SSHKEYALIAS = alias set on the digital certificates component ( you can do a lookup here and pick the correct value)

USER = sftpuser (userid provided by the vendor)

PASSWORDKEY = private key passphrase (in plain text not encrypted) 

Option 2 – Keys stored on file system

We have to create some directories under the PS_SERVDIR location. So if the call to transmit the files is going to be made from the application server then in case of de-coupled PS_HOME it would be PS_CFG_HOME\appserv\{Domain} and process scheduler it would be PS_CFG_HOME\appserv\prcs\{Domain} and in case of a non de-coupled PS_HOME like the older way (traditional) it would be PS_HOME\appserv\{Domain} and PS_HOME\prcs\appserv\{Domain}

If you do not want to create sub-directories under here then there is some more information in Peoplebooks to create a new environment variable to point to a new location. 

So under PS_SERVDIR create a directory called sshkeys and 2 sub-directories under that called as private and public. The names of these 3 directories cannot change. Copy the sftpuser.ppk file to the private directory and sftpuser file to public directory. The filename naming convention is primarily enforced for this option. 

Now back to within PIA create a new URL definition. The URL properties is what is slightly different from the previous option. 

Provide the following properties

AUTHTYPE = 1 - PUBLICKEY 

PRIVATEKEY = sftpuser.ppk 

PUBLICKEY = sftpuser

USER = sftpuser (userid provided by the vendor)

PASSWORDKEY = private key passphrase (in plain text not encrypted) 

So that's essentially it. 

Following is the basic peoplecode call to transmit the file. 

&returncode = PutAttachment(URL.N_VENDOR, &filename, &fullpathtofile);

where N_VENDOR is the URL definition created above
&filename is the filename as it test.txt

and &fullpathtofile is the complete path as in C:\temp\test.txt