SAS Management Console - how to extract a report for audit?

[Bruno7]

The "Search" tab of SMC has a nice search function to find all "Deployed Flows" for the current year, which is great for our internal audit purposes.  It lists each flow name, type, Last Modified date, and location.  The problem is there is no way to right-click-copy the list to Excel or to export this list.  We tried exporting to a package - an spk file - which gives a log at the end, but the log does not have all bits of information - it only has flow names.  How can we get the data from the search functionality OUT of SMC and into Excel for our auditors?

[gwootton · Accepted solution]

You could use Metadata DATA Step functions to pull attributes on any flows created within a given date range. This program pulls information on flows and their associated jobs, though it doesn't pull when they were created/modified, it could be modified to do so.

https://github.com/greg-wootton/sas-programs/blob/main/Scheduling/get_job_and_flow.sas

[gwootton]

I added the date by changing the flow data step to pull MetadataCreated as "flow_create" and then convert it to flow_create_num:

/* Create table of flow IDs and Names */
data flows (keep=flow_id flow_name flow_create_num);
  length type id flow_uri  $ 50 flow_name $ 255 flow_id $ 17 flow_create $ 18 flow_create_num 8;
  format flow_create_num datetime18.;
  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);
	rc=metadata_getattr(flow_uri,"MetadataCreated",flow_create);
	flow_create_num=input(flow_create,datetime18.);
    output;
  end;
run;

[Bruno7]

This is fantastic!  Thank you.   Do you know how I can obtain a listing of all possible variables I could extract?  In particular, I am looking for the flow MODIFIED date.

[gwootton]

The Metadata Model is described in this documentation:

SAS® 9.4 Metadata Model: Reference
https://support.sas.com/documentation/cdl/en/omamodref/67417/HTML/default/viewer.htm#titlepage.htm

The modified date attribute is called MetadataUpdated.

The documentation page for the JFJob type is here (flows are a JFJob type):

https://support.sas.com/documentation/cdl/en/omamodref/67417/HTML/default/viewer.htm#jfjob.htm

[Bruno7]

Bravo!  Thank you so much.  Being able to query metadata means less manual work for my team In complying with our internal auditors.  This information is GOLD.

[Nigel_Pain]

I also find the Metadata Browser in a full SAS session (from the menus, Solutions>Accessories>Metadata Browser) invaluable to work out associations between objects when writing a data step query.

[Bruno7]

Does anyone know if there is a way to find orphaned jobs?  As in jobs that are set up in DI Studio, but are not a part of any Flow in SAS Management Console?

[gwootton]

Something like this?

https://github.com/greg-wootton/sas-programs/blob/main/Scheduling/get_deployed_jobs_no_flow.sas

[Bruno7]

YES!!!! EXACTLY LIKE THIS!!! Thank you so much!! WONDERFUL!