List deployed Jobs which are not in a Flow

[FK1]

Hi Everyone,

is there a way to run a SAS Code which list deployed jobs, which are NOT part of a flow?

I know, you can see visually this list when right clicking on the Scheduling Server plug-in in SMC >> "Manage deployed jobs"...

Cheers,

FK1

[gwootton · Accepted solution]

This code would write to the SAS Log Deployed Jobs names without an associated flow. Remove the [not(Steps/*)] bit to list all deployed jobs.

data _null_;
	length type id uri $ 50 name $ 255;
	call missing ( of _character_ );
	obj="omsobj:JFJob?JFJob[@PublicType='DeployedJob'][not(Steps/*)]";
	jobcount=metadata_resolve(obj,type,id);
	put "NOTE: Found " jobcount "Deployed Jobs not assiged to a flow.";
	if jobcount > 0 then do i = 1 to jobcount;
	rc=metadata_getnobj(obj,i,uri);
	rc=metadata_getattr(uri,"Name",name);
	put name=;
	end;
run;

[FK1]

Perfect! Exactly, what I was looking for!

@gwootton  : you seem to be a Grand Master regarding the Meta Data Model of SAS!

[FK1]

@gwootton : I forgot to ask a quick follow-up question:
Is it possible to get the value of the "Tree" and "PartentTree" Metadata Typ, so that I can see, where in the SAS Metdata Tree the respective "unassigned Flow jobs" are located?

[gwootton]

You can loop through the Tree association of the job and it's ParentTree association to build a path:

data _null_;
	length type id uri turi puri $ 50 name folder pfolder path $ 255;
	call missing ( of _character_ );
	obj="omsobj:JFJob?JFJob[@PublicType='DeployedJob'][not(Steps/*)]";
	jobcount=metadata_resolve(obj,type,id);
	put "NOTE: Found " jobcount "Deployed Jobs not assiged to a flow.";
	if jobcount > 0 then do i = 1 to jobcount;
		rc=metadata_getnobj(obj,i,uri);
		rc=metadata_getattr(uri,"Name",name);
		rc=metadata_getnasn(uri,"Trees",1,turi);
		rc=metadata_getattr(turi,"Name",folder);
		path=folder;
		parent_rc=metadata_getnasn(turi,"ParentTree",1,puri); /* Determine if the metadata folder is top-level */
		if parent_rc > 0 then do while (parent_rc > 0); /* If not, this loop assembles the metadata path, as these are nested "Tree" objects. */
			rc=metadata_getattr(puri,"Name",pfolder);
			path=cats(pfolder,"\",path);
			parent_rc=metadata_getnasn(puri,"ParentTree",1,puri);
		end;
		path=cats("\",path);
		put name= path=;
	end;
run;

[rldaven]

Would you be able to associate the deployed job with a user who deployed the job? I'm thinking we could use that to contact the person to see if the job is still needed or if it should be added to a flow.

[gwootton]

You could look at the ResponsibleParties association for the job.

data _null_;
	length type id uri turi puri ruri ouri $ 50 name folder pfolder path ownerdn ownern $ 255;
	call missing ( of _character_ );
	obj="omsobj:JFJob?JFJob[@PublicType='DeployedJob'][not(Steps/*)]";
	jobcount=metadata_resolve(obj,type,id);
	put "NOTE: Found " jobcount "Deployed Jobs not assiged to a flow.";
	if jobcount > 0 then do i = 1 to jobcount;
		rc=metadata_getnobj(obj,i,uri);
		rc=metadata_getattr(uri,"Name",name);
		rp_rc=metadata_getnasn(uri,"ResponsibleParties",1,ruri);
		if rp_rc > 0 then do;
			rc=metadata_getnasn(ruri,"Persons",1,ouri);
			rc=metadata_getattr(ouri,"Name",ownern);
			rc=metadata_getattr(ouri,"DisplayName",ownerdn);
			if ownerdn ne "" then do;
				ownern = ownerdn;
			end;
		end;
		rc=metadata_getnasn(uri,"Trees",1,turi);
		rc=metadata_getattr(turi,"Name",folder);
		path=folder;
		parent_rc=metadata_getnasn(turi,"ParentTree",1,puri); /* Determine if the metadata folder is top-level */
		if parent_rc > 0 then do while (parent_rc > 0); /* If not, this loop assembles the metadata path, as these are nested "Tree" objects. */
			rc=metadata_getattr(puri,"Name",pfolder);
			path=cats(pfolder,"\",path);
			parent_rc=metadata_getnasn(puri,"ParentTree",1,puri);
		end;
		path=cats("\",path);
		put name= path= ownern=;
	end;
run;

[FK1]

Hi @gwootton I hope I don't overstretch your help. But could you also outline, how to get a list of jobs that are WITHIN a flow - and if possible - taking into account the order in which the are executed . I know there is a way in SMC to create a dataset of a flow. But is it also possible in a "plain" SAS runtime environment like, EG?

Let me elaborate a little bit:

Given a Flow named "Flow A", let us assume this flow has 7 jobs which are in a "serial order", which means, that job2 within the flow, runs AFTER job1 has finished and in turn job3 runs AFTER job2 has finished, an so on..This is how we usually create jobs in DI Studio.

Is it possible to run a SAS Code that lists the jobs of this Flow A (if possible, taking into account the order of the jobs in which they run) in a sas table?

[gwootton]

While you can get the jobs associated with a flow fairly easily (flows are JFJob objects of type "DeployedFlow", that have an association JobActivities/TransformationActivity/Steps/TransformationStep/Transformations/JFJob with a type of "DeployedJob"), getting the organization of the flow is more complicated (i.e. job a runs after job b versus job a and job b run at the same time, or job b only runs if job a completes successfully).

Those TransformationStep objects that contain the JFJob as a Transformation association share a gate object (i.e. job completes successfully) under the SuccessorDependency and PredecessorDependency association.

It's possible, but I do not have any code demonstrating this.

[gwootton]

This program builds some tables you can use to associate jobs and flows, jobs and commands, flows and schedules, and jobs and their code locations.

/* This program creates a set of tables from Metadata containing information about jobs and flows. */
/* These tables are: Flows - a tables of defined flows, Flowsched - a table of scheduled flows and their schedules */
/* Jobs - a table of deployed jobs and their associated data servers, dsbs - a table of data step batch servers and */
/* their attributes, and cmd - a table of any customized commands for jobs. */
/* Author: Greg Wootton Date: 22JUN2020 */

/* Provide connection to Metadata */
%let mduser=sasadm@saspw;
%let mdpass=password;
%let mdserver=meta.demo.sas.com;
%let mdport=8561;

/* Specify Data Step Batch Server values */
/* 9.4 Values */
%let dsbslogprop=BatchServer.DataStep.Property.LogDir.xmlKey.txt;
%let dsbslogext=BatchServer.DataStep.Property.LogExt.xmlKey.txt;
%let dsbscmdline=BatchServer.DataStep.Property.CmdLine.xmlKey.txt;

/* 9.3 Values */
/*%let dsbs=%str('SASCompute1 - SAS Data Step Batch Server');*/
/*%let dsbslogprop=%str(Logs Directory);*/
/*%let dsbslogext=%str(Rolling Log Options);*/
/*%let dsbscmdline=%str(Command Line);*/

/* Set Metadata connection options based on macro variables set above. */
options metaserver="&mdserver"
metaport=&mdport
metaprotocol='bridge'
metauser="&mduser"
metapass="&mdpass"
metarepository='Foundation'
metaconnect='NONE'
;

/* Pull properties for the Data Step Batch Server(s) */
data dsbs (keep=dsbs_id dsbsname dsbscmdline dsbslogpath dsbslogext);
	length type id prop_uri dsbs_uri $ 50 prop_name $ 255 propval dsbsname dsbscmdline dsbslogpath dsbslogext  $ 1024 dsbs_id $ 17;
	call missing (of _character_);
	dsbsobj="omsobj:ServerComponent?@PublicType='Server.DataStepBatch'";
	dsbscount=metadata_resolve(dsbsobj,type,id);
	if dsbscount ge 1 then do j=1 to dsbscount;
		rc=metadata_getnobj(dsbsobj,j,dsbs_uri);
		rc=metadata_getattr(dsbs_uri,"Id",dsbs_id);
		rc=metadata_getattr(dsbs_uri,"Name",dsbsname);
		propcount=metadata_getnasn(dsbs_uri,"Properties",1,prop_uri);
		if propcount > 0 then do i=1 to propcount;
			rc=metadata_getnasn(dsbs_uri,"Properties",i,prop_uri);
			rc=metadata_getattr(prop_uri,"Name",prop_name);
			rc=metadata_getattr(prop_uri,"DefaultValue",propval);
			if prop_name="&dsbscmdline" then do;
				dsbscmdline=propval;
			end;
			else if prop_name="&dsbslogprop" then do;
				dsbslogpath=propval;
			end;
			else if prop_name="&dsbslogext" then do;
				dsbslogext=propval;
			end;
		end;
		output;
	end;
run;

/* Create table of flow IDs and schedule details */

data flowsched (keep=flow_id event_condition schedule);
	length type id flow_uri step_uri event_uri prop_uri prop_name $ 50 flow_name $ 255 event_condition prop_val schedule $ 1024 flow_id $ 17;
	call missing(of _character_);
	flow_obj="omsobj:JFJob?@PublicType='DeployedFlow'";
	flowcount=metadata_resolve(flow_obj,type,id);
	put "NOTE: Found " flowcount "flows.";
	if flowcount ge 1 then do i=1 to flowcount;
		rc=metadata_getnobj(flow_obj,i,flow_uri);
		rc=metadata_getattr(flow_uri,"Id",flow_id);
			rc=metadata_getnasn(flow_uri,"Steps",1,step_uri);
			rc=metadata_getnasn(step_uri,"TriggeringEvents",1,event_uri);
				rc=metadata_getattr(event_uri,"Condition",event_condition);
				propcount=metadata_getnasn(event_uri,"Properties",1,prop_uri);
				if propcount ge 1 then do l=1 to propcount;
					rc=metadata_getnasn(event_uri,"Properties",l,prop_uri);
					rc=metadata_getattr(prop_uri,"Name",prop_name);
					if prop_name = "Definition" then do;
					rc=metadata_getattr(prop_uri,"DefaultValue",prop_val);
					schedule=trim(prop_val);
					output;
					end;
		end;
	end;
run;

/* Create table of flow IDs and Names */

data flows (keep=flow_id flow_name);
	length type id flow_uri  $ 50 flow_name $ 255 flow_id $ 17;
	call missing(of _character_);
	flow_obj="omsobj:JFJob?@PublicType='DeployedFlow'";
	flowcount=metadata_resolve(flow_obj,type,id);
	put "NOTE: Found " flowcount "flows.";
	if flowcount ge 1 then do i=1 to flowcount;
		rc=metadata_getnobj(flow_obj,i,flow_uri);
		rc=metadata_getattr(flow_uri,"Name",flow_name);
		rc=metadata_getattr(flow_uri,"Id",flow_id);
		output;
	end;
run;

/* Create table of job details. */
data jobs (keep=job_name dir_name file_name dsbs_id job_id);
length type id job_uri file_uri dir_uri dsbs_uri $ 50  job_name dir_name file_name $ 255 dsbs_id job_id $ 17;
call missing(of _character_);
jobobj="omsobj:JFJob?@PublicType='DeployedJob'";
jobcount=metadata_resolve(jobobj,type,id);
if jobcount ge 1 then do i=1 to jobcount;
	rc=metadata_getnobj(jobobj,i,job_uri);
	rc=metadata_getattr(job_uri,"Name",job_name);
	rc=metadata_getattr(job_uri,"Id",job_id);
	rc=metadata_getnasn(job_uri,"SourceCode",1,file_uri);
	rc=metadata_getattr(file_uri,"FileName",file_name);
	rc=metadata_getnasn(file_uri,"Directories",1,dir_uri);
	rc=metadata_getattr(dir_uri,"DirectoryName",dir_name);
	rc=metadata_getnasn(job_uri,"TargetSpecifications",1,dsbs_uri);
	rc=metadata_getattr(dsbs_uri,"Id",dsbs_id);
	output;
end;
run;

/* Create table of custom command lines and their associated job. */
data cmd (keep=job_id cmd_val);
	length type id cmd_uri step_uri job_uri $ 50 cmd_val $ 1024 job_id $ 17;
	call missing (of _character_);
	cmdobj="omsobj:Property?Property[@Name='CmdLine'][AssociatedObject/TransformationStep/Transformations/JFJob]";
	cmdcount=metadata_resolve(cmdobj,type,id);
	if cmdcount ge 1 then do i=1 to cmdcount;
		rc=metadata_getnobj(cmdobj,i,cmd_uri);
		rc=metadata_getattr(cmd_uri,"DefaultValue",cmd_val);
		rc=metadata_getnasn(cmd_uri,"AssociatedObject",1,step_uri);
		rc=metadata_getnasn(step_uri,"Transformations",1,job_uri);
		rc=metadata_getattr(job_uri,"Id",job_id);
		output;
	end;
run;

/* Get Flow/Job associations */
data flowjobs (keep=flow_id job_id);
length type id flow_uri act_uri step_uri job_uri $ 50 flow_name $ 255 flow_id job_id $ 17;
call missing (of _character_);
flowobj="omsobj:JFJob?@PublicType='DeployedFlow'";
flowcount=metadata_resolve(flowobj,type,id);
if flowcount ge 1 then do i=1 to flowcount;
	rc=metadata_getnobj(flowobj,i,flow_uri);
	rc=metadata_getattr(flow_uri,"Id",flow_id);
	actcount=metadata_getnasn(flow_uri,"JobActivities",1,step_uri);
	if actcount ge 1 then do j=1 to actcount;
		rc=metadata_getnasn(flow_uri,"JobActivities",j,act_uri);
		stepcount=metadata_getnasn(act_uri,"Steps",1,step_uri);
		if stepcount ge 1 then do k=1 to stepcount;
			rc=metadata_getnasn(act_uri,"Steps",k,step_uri);
			jobcount=metadata_getnasn(step_uri,"Transformations",1,job_uri);
			if jobcount ge 1 then do l=1 to jobcount;
				rc=metadata_getnasn(step_uri,"Transformations",l,job_uri);
				rc=metadata_getattr(job_uri,"Id",job_id);
				output;
			end;
		end;
	end;
end;
run;

/* Extract sysin and log values from custom command. This is currently limited to UNIX-like paths. */

data cmd (keep=job_id cmd_val log sys);
	set cmd;
	if _N_=1 then do;
	retain logpatternID syspatternID;
	logpattern='/-log[[:space:]]+\/[A-z0-9\/\.]+/';
	syspattern='/-sysin[[:space:]]+\/[A-z0-9\/\.]+/';
	logpatternID=prxparse(logpattern);
	syspatternID=prxparse(syspattern);
	end;
	call prxsubstr(logpatternID,cmd_val,position,length);
	log=substr(cmd_val,position+5,length-5);
	call prxsubstr(syspatternID,cmd_val,position,length);
	sys=substr(cmd_val,position+7,length-7);
run;

[FK1]

Hello @gwootton ,

you are very generous to share all this code with me and the others! Thank you for that. 

It definitley helps me a lot, as I can get for starters "the connection" between jobs and flows.

Again, thank you!

[TBarker]

This was a fantastically useful thread! My thanks to both of you @FK1 and @gwootton for already asking and answering a question I had and others I didn't even know I had until I saw them here. 😁🏆