Library - Extract metadata

[MariaD]

Hi folks,

There is any easy way to extract the following information for all libraries defined on SAS MC?

1. Library: Name, id

2. Engine: SAS or DBMS. In case o DBMS if is SQL, Oracle, etc.

3. Path: for SAS libraries

4. Authorization: which user/group has authorisation to read, write, etc.

Regards, 

[gwootton · Accepted solution]

You can use the Metadata Security Macros to get permissions on objects.
%MDSECDS Security Report Macro 

You can use Metadata DATA Step Functions or PROC METADATA to pull information down from Metadata and report on it.

Overview of DATA Step Functions for Metadata 

These sample programs may be helpful.

SAS Libraries

data work.libinfo;

/*declare and initialize variables */
	length
		type $ 20
		lib_ref $ 8
		lib_uri lib_name app_uri app_name dir_uri $ 50
		id $ 17
		path $ 255;
	keep lib_ref lib_name app_name path;
	call missing(type,id,lib_uri,lib_name,app_uri,app_name,dir_uri,path);

	obj="omsobj:SASLibrary?@Id contains '.'"; /* Define library search parameters. */

	/* Search Metadata for libraries */

	libcount=metadata_resolve(obj,type,id);
	put "INFO: Found " libcount "libraries.";
	if libcount > 0 then do n=1 to libcount; /* for each library found, extract name and associated properties (first associated application server, path) */

	rc=metadata_getnobj(obj,n,lib_uri);
	rc=metadata_getattr(lib_uri,"Name",lib_name); 
	rc=metadata_getattr(lib_uri,"Libref",lib_ref);
	rc=metadata_getnasn(lib_uri,"DeployedComponents",1,app_uri);
	rc=metadata_getattr(app_uri,"Name",app_name);
	rc=metadata_getnasn(lib_uri,"UsingPackages",1,dir_uri);
	rc=metadata_getattr(dir_uri,"DirectoryName",path);
	output; /* Push results to table  */
	call missing (path); /* clear path variable. */
	end;
	else put "INFO: No libraries to resolve.";
run;

Database Libraries

data work.libinfo;

/*declare and initialize variables */
	length
		type user schema $ 20
		lib_uri lib_name app_uri app_name schema_uri login_uri dbms_uri dbms_name conn_uri prop_uri datasrc $ 50
		id $ 17;
	keep lib_name app_name user schema dbms_name datasrc;
	call missing(type,id,lib_uri,lib_name,app_uri,app_name,schema_uri,login_uri,user,schema_uri,schema,dbms_uri,dbms_name,con_uri,prop_uri,datasrc);

	obj="omsobj:SASLibrary?@IsDBMSLibname = '1'";

	/* Search Metadata for libraries */

	libcount=metadata_resolve(obj,type,id);
	put "INFO: Found " libcount "database libraries.";
	if libcount > 0 then do n=1 to libcount; /* for each library found, extract name and associated properties (default login, first associated application server, schema, database server) */

	rc=metadata_getnobj(obj,n,lib_uri);
	rc=metadata_getattr(lib_uri,"Name",lib_name); 
	rc=metadata_getnasn(lib_uri,"DefaultLogin",1,login_uri);
	rc=metadata_getattr(login_uri,"UserID",user);
	rc=metadata_getnasn(lib_uri,"DeployedComponents",1,app_uri);
	rc=metadata_getattr(app_uri,"Name",app_name);
	rc=metadata_getnasn(lib_uri,"UsingPackages",1,schema_uri);
	rc=metadata_getattr(schema_uri,"SchemaName",schema);
	rc=metadata_getnasn(schema_uri,"DeployedComponents",1,dbms_uri);
	rc=metadata_getattr(dbms_uri,"Name",dbms_name);
	rc=metadata_getnasn(dbms_uri,"SourceConnections",1,conn_uri);
	rc=metadata_getnasn(conn_uri,"Properties",1,prop_uri);
	rc=metadata_getattr(prop_uri,"DefaultValue",datasrc);
	output; /* Push results to table  */

	end;
	else put "INFO: No libraries to resolve.";
run;

[MariaD]

Thanks!