topic Re: SAS Management Console - how to extract a report for audit? in Administration and Deployment

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

Bruno7 — Wed, 30 Mar 2022 18:58:52 GMT

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?

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

gwootton — Wed, 30 Mar 2022 20:12:48 GMT

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

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

gwootton — Wed, 30 Mar 2022 21:15:43 GMT

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;

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

Bruno7 — Wed, 06 Apr 2022 18:28:06 GMT

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.

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

gwootton — Wed, 06 Apr 2022 18:35:15 GMT

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

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

Bruno7 — Wed, 06 Apr 2022 18:51:05 GMT

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.

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

Nigel_Pain — Thu, 07 Apr 2022 08:20:18 GMT

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.

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

Bruno7 — Sat, 09 Apr 2022 16:02:43 GMT

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?

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

gwootton — Mon, 11 Apr 2022 12:22:05 GMT

Something like this?

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

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

Bruno7 — Mon, 11 Apr 2022 13:28:39 GMT

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