Re: Read SAS Viya REST API logs

devrant · Posted 05-16-2023 10:17 AM

Hi!

I'd like to know, if there is any way to access the part of a http request passed on to the said API where one can check was actually passed on (e.g. url, header(s), body).

Is there any way to do that?

Thanks in advance.

BR

Devrant

joeFurbee · Posted 05-16-2023 10:34 AM

Hi @devrant ,

Are you looking for a log on the SAS side or info from the client side?

From the SAS side, you can create a log file as explained in the code in this API use case on GitHub. The log will show what's going on in SAS, but I don't think it will contain the API call.

From the client side, you can use various tools to see the calls that are made. In Postman, you can enable the Postman Console and see the entire API transaction. You can also create an API call and then click on the </> icon on the right side and it will display the code as it will be sent (and you have the choice of languages to display). If you're using Python or another language, there are commands you can run to display the call as it will be sent.

Hope this helps. If this isn't what you needed, please provide more details.

Join us for SAS Community Trivia
SAS Bowl XXXVIII, SAS Programming: Getting Started
Wednesday, February 14, 2024, at 10 a.m. ET | #SASBowl

devrant · Posted 05-16-2023 11:14 AM

Hi @joeFurbee!

Thank you for your prompt response. Let me describe my use case in a couple of points.

Goal: I want to call a specific SAS job over the REST API from PL/SQL.

I've successfuly done so with postman and with curl, so I assume that I have the request in the form I need it.

Now I want to achieve the same thing with the same request, but using PL/SQL which apparenly does not seem to work so far. In case of other APIs my PL/SQL proc functions properly.

(I get:

"<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html><head>
<title>400 Bad Request</title>
</head><body>
<h1>Bad Request</h1>
<p>Your browser sent a request that this server could not understand.<br />
</p>
</body></html>
")

-----

I've also installed Mockoon to locally test what is being passed on from PL/SQL to a mock API.
(There my request looks as follows:

GENERAL:

Request URL: /content/xyz

Method: POST

Caught by route: /content/:param1

HEADERS:

Accept: application/vnd.sas.job.execution.job+json

Authorization : Bearer: ey...

Connection: close

Content-length: 166

Content-type: application/vnd.sas.job.execution.job.request+json

Host: xxxxx

User-agent: Mozilla/4.0

PARAMS:

Route params (1)

param1: xyz

Query params (none)

BODY:

{ "jobDefinitionUri": "/jobDefinitions/definitions/xxx", "arguments":{ "_contextName": "SAS Job Execution compute context"} })
and I get a response HTTP status 200.
 
---

Therefore I'm looking for a way to see what actually "arrives" to SAS, in other words, what is actually being called from the API side.
 
BR
Devrant

joeFurbee · Posted 05-18-2023 08:06 AM

Thanks for the additional info @devrant. I don't know of any log that will capture the incoming API call. I've posted an internal inquiry to see if someone has an idea on how to do this.

In the meantime, the error seems to indicate there's a syntax or path error with the request. We can try and dissect your call to see if we can discover the issue. I've never programmed in PL/SQL. A quick Google search of an example REST call and merging with what you sent, this is the call I came up with:

create or replace
procedure job_exec()
  is 
  url := 'http://server.sas.com';
  content := { "jobDefinitionUri": "/jobDefinitions/definitions/xxx", "arguments":{ "_contextName": "SAS Job Execution compute context"}};
  
begin
  request := utl_http.begin_request(url, 'POST','HTTP/1.1');
  utl_http.set_header(req, 'content-type', 'application/vnd.sas.job.execution.job.request+json');
  utl_http.set_header(req, 'accept', 'application/vnd.sas.job.execution.job+json'); 
  utl_http.set_header(req, 'user-agent', 'mozilla/4.0');
  utl_http.set_header(req, 'authorization ', 'Bearer ABCD_1234*xyz.987');

utl_http.write_text(request,content);
response := util_http.get_response(request);

That may not be completely correct, but we can at least compare it to your code.

Join us for SAS Community Trivia
SAS Bowl XXXVIII, SAS Programming: Getting Started
Wednesday, February 14, 2024, at 10 a.m. ET | #SASBowl

AlanC · Posted 05-19-2023 12:34 AM

Normally, when I am having to decipher REST calls, I use Fiddler or Wireshark. Fiddler is my go-to and looking at the raw call. Not sure if that helps but see what is going across the wire and then do a diff with Postman or curl. I would assume that SAS handles the call correctly so something is amiss in the header (most likely).

https://github.com/savian-net

joeFurbee · Posted 05-23-2023 10:16 AM

I agree with @AlanC that Wireshark may be a good way to capture the call that's hitting SAS.

I also received the following code from a colleague. I have not yet attempted it, but wanted to pass it along.

The first step is to create a table:

CREATE TABLE WWW_DATA (num NUMBER, dat CLOB)

Then run the code below:

===========================================================

create or replace NONEDITIONABLE PROCEDURE SAS is

req utl_http.req;

response utl_http.resp;

url varchar2(4000) := 'http://base.ingress-nginx.mm4c2-m1.modelmanager.sashq-d.open...';

content varchar2(4000) := '{"jobDefinition": {"name": "Model Manager Job", "type": "Compute", "code": "%put _all_;", "properties": []}, "arguments":{ "_contextName": "SAS Job Execution compute context"}}';

x_clob CLOB;

l_buffer VARCHAR2(32767);

n NUMBER;

BEGIN

dbms_output.put_line('hello');

req := utl_http.begin_request(url, 'POST');

UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');

utl_http.set_header(req, 'Authorization', 'Bearer eyJhbGciOiJSUzIGk8k54QTOsXsj4BKzooqds6E..........qIz9qNWhy4xZ05zCE14Q');

utl_http.set_header(req, 'Accept', '*/*');

utl_http.set_header(req, 'Accept-Encoding', 'gzip, deflate, br');

utl_http.set_header(req, 'Connection', 'keep-alive');

utl_http.write_text(req,content);

response := utl_http.get_response(req);

dbms_output.put_line('Response Status: '

||response.status_code

||' ' || response.reason_phrase);

dbms_lob.createtemporary(x_clob, FALSE );

BEGIN

x_clob := EMPTY_CLOB;

LOOP

UTL_HTTP.READ_TEXT(response, l_buffer, LENGTH(l_buffer));

x_clob := x_clob || l_buffer;

END LOOP;

UTL_HTTP.END_RESPONSE(response);

dbms_output.put_line('x_clob opened');

EXCEPTION

WHEN UTL_HTTP.END_OF_BODY THEN

UTL_HTTP.END_RESPONSE(response);

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

UTL_HTTP.END_RESPONSE(response);

END;

SELECT COUNT(*) + 1 INTO n FROM WWW_DATA;

INSERT INTO WWW_DATA VALUES (n, x_clob);

COMMIT;

END SAS;

========================================================

The highlighted part needs to be replaced, of course.

Then you will see the data in db table like [in the image below]:

The key is to simulate browser headers, as code in bold above.

Join us for SAS Community Trivia
SAS Bowl XXXVIII, SAS Programming: Getting Started
Wednesday, February 14, 2024, at 10 a.m. ET | #SASBowl

AllanBowe · Posted 05-23-2023 11:19 AM

Joe - that's fascinating, thanks for sharing!

I also wanted to share our go-to tool for HTTP request sniffing (which also works for https out-of-the-box for certain tools): https://httptoolkit.com/

Much easier to use than WireShark and can often be deployed without admin rights.

(Edited to remove PG inaccuracy)

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)
Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs

devrant · Posted 05-24-2023 08:23 AM

Hello Joe, hello Allan, hello Alan!

First, I wanted to thank you all, for sharing you expretise.

I haven't had the time yet to thoroughly test your proposals, but I can confirm that I now recieve HTTP status codes 200 and 201. I'll need to slightly adjust my query to be able to call a job execution and I would also need to implement some wrapper functions for automated token renewal.

For now I've tried to sniff my requests with Fiddler(+tcpdump) and Wireshark but to no avail.

I shall provide some updates as I've figured the reason.

BR

Devrant

AlanC · Posted 05-24-2023 08:39 AM

Well, congrats. That is a start.

Remember on https traffic, you need to have any of the tools act as a 'man in the middle' tool. See Decrypt HTTPS traffic - Fiddler Classic (telerik.com). I would use Fiddler Classic vs Fiddler Everywhere but that is just an old habit.

The tool that Allan referenced looked interesting as well. I haven't played with it but probably worth checking out.

https://github.com/savian-net

devrant · Posted 06-13-2023 10:05 AM

Hello!

A short feedback from my side.

The request gets constructed like this:

req := utl_http.begin_request(url, 'POST');               -- works with the following too: utl_http.begin_request(url, 'POST', utl_http.http_version_1_1);
utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');    -- works without User-Agent header too

The following 3 rows seem to have made the difference:

utl_http.set_header(req, 'Accept', '*/*');
utl_http.set_header(req, 'Accept-Encoding', 'gzip, deflate, br');
utl_http.set_header(req, 'Connection', 'keep-alive');

BR

Devrant

Classroom Training Available!