How to retrieve status of scheduled jobs

[viswmmo]

Hi Community,

Do you know if there is a way to get a list of last day's scheduled job and their status. That is, in the same way as you can see in the job monitoring tab in Environment Manager, but as a list for further processing. In order e.g. to build a data set of which jobs usually go wrong or to send alerts with failed jobs in the last 24 hours, etc.

We are using SAS Viya 3.5 running on Linux Rhel 7

Regards 

Mattias

[gwootton · Accepted solution]

You could query the same endpoint that Environment Manager does using PROC HTTP in SAS Studio, reading the JSON response into a data set:

/* Specify the URL for the environment. */
%let baseurl=https://viya.example.com;

/* Define a macro */
%macro getjobs();

/* Initialize files */
filename headout;
filename headout temp;
filename resp;
filename resp temp;

/* Call the jobExecution REST API using whatever filter you want to limit the results (this one is from Nov 28th - Dec 7th)  */
proc http oauth_bearer=sas_services url="&baseurl/jobExecution/jobs?start=0%nrstr(&limit)=20%nrstr(&filter)=and(ge(creationTimeStamp,%272021-11-28T05:00:00.000Z%27),lt(creationTimeStamp,%272021-12-07T05:00:00.000Z%27))" out=resp headerout=headout HEADEROUT_OVERWRITE;
    headers "Accept"="application/json";
run;

/* Read in the response using the JSON libname engine. */
libname resp;
libname resp json fileref=resp;

/* Define a data set with the information we want. */
data work.jobs;
	stop;
	length name $ 255 state createdBy $ 50;
run;

/* Check for a next link (meaning there are more results not in the initial response) */
data _null_;
    set resp.links;
    if rel="next" then call execute('%let next=%nrstr('||href||')');
run;

/* Add the current response into the data set. */
proc sql;
	insert into work.jobs select a.name,b.state,b.createdBy from resp.items_jobrequest as a,resp.items as b where a.ordinal_items = b.ordinal_items;
quit;

/* If we found a next link, call it and add its results into the data set, looping until there is no next link. */
%do %while (%length(&next)>0);
	filename resp;
	filename resp temp;
	proc http oauth_bearer=sas_services url="&baseurl&next" out=resp headerout=headout HEADEROUT_OVERWRITE;
	    headers "Accept"="application/json";
	run;
	libname resp;
	libname resp json fileref=resp;
	proc sql;
		insert into work.jobs select a.name,b.state,b.createdBy from resp.items_jobrequest as a,resp.items as b where a.ordinal_items = b.ordinal_items;
	quit;
	%let next=;
	data _null_;
	    set resp.links;
	    if rel="next" then call execute('%let next=%nrstr('||href||')');
	run;
%end;

%mend getjobs;

%getjobs();

[viswmmo]

Thanks so much! It was exactly what I was looking for

[shkmap]

I tried this code but table resp.items_jobrequest not creating

[gwootton]

That suggests that there are no jobRequests within the filter range provided, or none you have permission to see. We are using the sas_services oauth token, are you running in SAS Studio? Did you update the baseURL?