topic Re: PROC HTTP - ServiceNow URL help needed in SAS Procedures

PROC HTTP - ServiceNow URL help needed

Bruno7 — Mon, 09 May 2022 21:53:00 GMT

I want to be able to
1) read ServiceNow for all unassigned sc tasks under my group
2) read ServiceNow again to obtain the corresponding RITM numbers for each task (we need bits of information from the sc task as well as from the RITM in order to process the ServiceNow request)
3) Update the sc task to show work is in progress and that it has been assigned to the automated process

I know this can be done, but am having trouble finding the correct URL syntax that will do items 2 and 3. Here is the syntax I found that does accomplish item #1 and brings back every unassigned sc task out there. Now I just need to match each task with its RITM, and update the task to show it is work in progress and assigned:

libname mylib "/mydir/myfolder/";

%macro get_unassigned_task();
	%LET unpw =xxxxxxxxxxx
		DATA _NULL_;
	call symput('encode',put("&unpw",$base64X76.));
	RUN;

	filename response TEMP;
	filename headers TEMP;
	%let url_query=%nrstr(https://mysite.service-now.com/api/now/table/task?sysparm_query=name%3D);
	%let url_query_cont=%nrstr(&assignment_group=MYASSIGNMENTGROUP);
	%put &url_query.&url_query_cont;
	options set= SSLCALISTLOC="/sas/sas94m5install/SASSecurityCertificateFramework/1.1/cacerts/cacerts.pem";

	PROC HTTP

		url="&url_query.&url_query_cont."	

		out=response
		headerout=headers;
		headers
			"Accept"="application/json"
			"Content-Type"="application/json"
			"Authorization"=" Basic &encode";
	RUN;

	LIBNAME RESPONSE JSON fileref=response;

	PROC SQL NOPRINT;
		SELECT count(*) into :total_obs FROM RESPONSE.ALLDATA;
	QUIT;

	%if &total_obs > 1 %then
		%do;

			PROC SQL NOPRINT;
				SELECT * INTO :my_vAR FROM RESPONSE.RESULT;
			quit;

			proc sql noprint;
				create table mylib.active as 
					select * from RESPONSE.RESULT
						where 
							sys_class_name = "sc_task" and
							assigned_to = "" and
							active="true"
				;
			quit;

			data mylib.active_in_progress;
				set mylib.active;
			run;

			RUN;

		%end;
	%else
		%do;
			%PUT No unassigned tasks were found for workgroup MYASSIGNMENTGROUP.;
		%end;
%mend;

%get_unassigned_task()

I have been all over the ServiceNow site, so far, I have nothing:

Tables | ServiceNow Developers

Can someone please advise? Spinning my wheels a bit....

Re: PROC HTTP - ServiceNow URL help needed

Sajid01 — Mon, 09 May 2022 23:39:56 GMT

Hello @Bruno7
This is something your ServiceNow team will provide.

Re: PROC HTTP - ServiceNow URL help needed

Bruno7 — Tue, 10 May 2022 02:30:44 GMT

Yeah.....they don't provide that. Their policy is to give no guidance - none - zero - we're on our own - on writing APIs. So....question is still open to anyone who can help provide clues.

Re: PROC HTTP - ServiceNow URL help needed

ChrisHemedinger — Tue, 10 May 2022 11:28:58 GMT

Your URL as it would be resolved is:

https://mysite.service-now.com/api/now/table/task?sysparm_query=name%3D&assignment_group=MYASSIGNMENTGROUP

The %3D is a URL-encoded = symbol. This construct doesn't seem correct to me. I think you mean to provide a value for sysparm_query and that whatever follows has to be URL encoded to pass through to the API. So "empty value" for "name=" -- is that correct?

Usually I try working the problem with Postman to validate the API calls, then transcribe to SAS to operationalize them.

Maybe it's something like this?

	%let url_query=https://mysite.service-now.com/api/now/table/task?sysparm_query=;
	%let url_query_cont=%sysfunc(urlencode(name=%str(&)assignment_group=MYASSIGNMENTGROUP));
	%put &url_query.&url_query_cont;

Re: PROC HTTP - ServiceNow URL help needed

Bruno7 — Thu, 08 Dec 2022 18:35:14 GMT

Hoping Chris H sees this...

So it is many months later and we have been in production with our first SAS-to-ServiceNow API to automate a mundane task (hooray!),

but I have a new problem: how do I do the java "getDisplayValue()" but in SAS?

Results of my query in Postman. I need the display value of the "assigned_to" field, but I don't know how to code for that in SAS. Or is there some way I can modify my URL query string to get at that display value?

Here is my query string:

https://blah.service-now.com/api/now/table/sc_task?sysparm_query=active=true^assignment_group=3f4f95db13e712003794b86f3244b0d0&sysparm_display_value=true&sysparm_fields=assigned_to,due_date,number,sys_id

Re: PROC HTTP - ServiceNow URL help needed

Bruno7 — Thu, 08 Dec 2022 18:52:54 GMT

In case other SAS developers happen across this post - here was the solution for us. Hope it helps as starter code for someone! (Also: install Postman, as Chris H mentioned. Use it to build your basic query, then translate it in the SAS code, as shown in the "%let url_query = ...")

%macro get_task();
	%LET unpw =username:password;
	DATA _NULL_;
		call symput('encode',put("&unpw",$base64X76.));
	RUN;
	filename SSSNGIT1 TEMP;
	filename headers TEMP;
	%let url_query=%nrstr(https://blahtage.service-now.com/api/now/table/sc_task?sysparm_query=active=true^assignment_group=3f4f95db13e712003794b86f3244b0d0);
	%put &url_query;
	options set= SSLCALISTLOC="/sas/sas94m5install/SASSecurityCertificateFramework/1.1/cacerts/cacerts.pem";
	PROC HTTP
		url="&url_query."	

		out=SSSNGIT1
		headerout=headers;
		headers
			"Accept" = "application/json"
			"Content-Type" = "application/json"
			"Authorization" = " Basic &encode";
	RUN;
	LIBNAME SSSNGIT1 JSON fileref=SSSNGIT1;
	PROC SQL;
		SELECT count(*) into :total_obs FROM SSSNGIT1.ALLDATA;
	QUIT;
	%if	&total_obs > 1 %then
		%do;
			PROC SQL;
				SELECT * INTO :my_vAR FROM SSSNGIT1.RESULT;
			quit;
			libname SSSNGIT2 "/hpsasfin/prod/projects/inf/fnd/prod/data/SSSN/STAGE/";
			proc sql;
				create table SSSNGIT2.SSSN_GITHUB2_task_order as 
					select * from SSSNGIT1.RESULT
						where 
							sys_class_name = "sc_task" and
							active="true" and
							u_reference_2 = "SAS Code" and 
							u_reference_3 = "GitHub" and
/*							assigned_to = "" and*/
							state="1" 
						order by number
				;
			quit;
%end;
%mend;
%get_task();

(

Re: PROC HTTP - ServiceNow URL help needed

ChrisHemedinger — Thu, 08 Dec 2022 19:25:31 GMT

Sounds like you got it going and all solved? I had just worked up a simple JSON read example but your code looks like it's covered.

filename json temp; data _null_; file json; infile datalines; input; put _infile_; datalines; { "result": [{ "number": "SCTASK3054528", "sys_id": "02b09ba4db6f59d0acd5aa2el39619b3", "due_date": "12/01/2022 14:02:51", "assigned_to": { "display_value": "the display value", "link": "https://snlink.com/api/now/table/sysuser/9d5779034f5a7604df3497dd0210c7e3" } }] } run; data _null_; rc=jsonpp('json','log'); run; libname resp JSON fileref=json; proc print data=resp.alldata; run;