BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MariaD
Barite | Level 11

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, 

1 ACCEPTED SOLUTION

Accepted Solutions
gwootton
SAS Super FREQ

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;
--
Greg Wootton | Principal Systems Technical Support Engineer

View solution in original post

2 REPLIES 2
gwootton
SAS Super FREQ

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;
--
Greg Wootton | Principal Systems Technical Support Engineer
MariaD
Barite | Level 11

Thanks!

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 3065 views
  • 2 likes
  • 2 in conversation