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

Hi,

 

I was wondering if anyone has come across the need to scan multiple workspace & batch server logs to extract datasets usage stats for a particular libname. i.e. over a monthly period which datasets were accessed, how many observation were read from a certain SAS library.

 

Has anyone solved this? Did you do this in SAS or did you write some sort of batch script to accomplish this task?

 

Any help on how to solve this quandry would be most welcome. Code examples would be even better...

 

regards

 

 

David

 

 FYI. I'm currently using SAS 9.4 M2 on AIX.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Timmy2383
Lapis Lazuli | Level 10

Ok, here's some rough code I drafted that works in my environment.  Main thing is to add a value for your log path. If you're going to scrape batch logs too, then you'll need to comment out the end of the first DATA step (i.e. IF FIND(LOG,'Workspace')). I'm running in Linux environment so I think this should all be compatible.

 

You may also want to gather other metrics, but this is basically just looking at lines in the logs where it says how many observations were read from a table, and then getting the number of records read and then library/table from which they were read.

 

ODS LISTING CLOSE;
ODS RESULTS OFF;

/*Enter your logs path here*/
%let LogsPath=/*Enter your logs path here. Example: /sas/config/compute/Lev1/Logs */;
/*Enter a temporary location and file name to which you can copy each log file when attempting to parse*/
%LET TempLogLoc= /tmp/WorkspaceLog.log;

/*Get listing of all workspace logs*/
FILENAME XCMD PIPE "ls -l &LogsPath.";

/*Create dataset of output from X command*/
DATA LOGS;
	INFILE XCMD DSD TRUNCOVER;
	LENGTH TEXT LOG FILEPATH $512.;
	INPUT @1 TEXT;		
	LOG = SCAN(TEXT,-1,' ');
	FILEPATH = "&LogsPath./"||STRIP(LOG);
	/*Ensuring that only root directory is included in output*/
	OWNER = SCAN(TEXT,3,' ');
	GROUP = SCAN(TEXT,4,' ');
	IF FIND(LOG,'Workspace');	
	DROP TEXT;
RUN;

PROC SQL NOPRINT;
SELECT COUNT(*) INTO: LOG_COUNT
FROM LOGS;
QUIT;

PROC SQL;
DROP TABLE LIB_DATA_FINAL;
QUIT;

%MACRO ReadLogs;

%DO I=1 %TO &LOG_COUNT.;

	DATA _NULL_;
		SET LOGS;
		IF _N_ = &I. THEN DO;
			CALL SYMPUTX('LOG',FILEPATH);
			CALL SYMPUTX('USER',OWNER);
			CALL SYMPUTX('GROUP',GROUP);
		END;
	RUN;

	/*Creating temporary copy of log file to avoid write-locks*/
	
	%SYSEXEC cp &LOG. &TempLogLoc.;
	
	DATA WORKSPACE_LOGS;
		LENGTH LOG $512. USER GROUP $12. ;
		LOG = "&LOG.";
		USER = "&USER.";
		GROUP = "&GROUP.";
	RUN;

	DATA WORK.LIB_DATA;
	INFILE "&TempLogLoc" TRUNCOVER DLM='~';
	LENGTH TEXT LOG $512.
		   TABLE $32. RECORDS 8.;
	INPUT @1 TEXT;
	LOG = "&LOG.";	
	IF FIND(TEXT,'observations read') THEN DO;
		TABLE=SCAN(TEXT,-1,' ');
		RECORDS=INPUT(SCAN(TEXT,-8,' '),BEST12.);
		RETAIN TABLE RECORDS;
		OUTPUT;
	END;
	/*
	IF FIND(TEXT,'created, with') THEN DO;
		LIBREF=SCAN(TEXT,-8,' ');
		RECORDS=INPUT(SCAN(TEXT,-5,' '),BEST12.);
		RETAIN LIBREF RECORDS;
		OUTPUT;
	END;
	*/
	KEEP /*TEXT LOG*/ TABLE RECORDS;
	RUN;
	
	PROC APPEND DATA=WORK.LIB_DATA BASE=LIB_DATA_FINAL FORCE;
	RUN;

%END;

/*Removing temporary log file*/
%SYSEXEC rm /gsasadm/Logs/WorkspaceLog.log;

%MEND;

%ReadLogs;


	


View solution in original post

7 REPLIES 7
Timmy2383
Lapis Lazuli | Level 10
David,

I've implemented something similar in my environment when attempting to gather library and table usage. You might want to try turning on the audit logs as described in this blog post:

http://blogs.sas.com/content/sgf/2015/09/30/part-1-auditing-data-access-who-did-what-and-when/
moorsd
Obsidian | Level 7

Hi,

 

Many thanks for pointing me in the direction of that information. Sadly I need to do this task retrospectively on existing logs so that solution won't work for my current issue 😞

 

Basically were moving some data out of SAS to Hadoop and I need to advise the network traffic team know how much data will go across the network. The best way i figured I could do this was to look at the existing use of the SAS datasets and apply some guestimates.

 

Hence the need to look through old workspace & batch server logs for reference to datasets in a specific library.

 

regards

 

David.

 

 

Timmy2383
Lapis Lazuli | Level 10
Oh ok, I see. I use SAS to scan through the logs directory to get a list of relevant logs to scan, then do a loop through the number of logs to scan each log file. I don't have code doing exactly this but I have something that may be able to be used. As a base. I may be able to get you something within the next hour when I get situated at the airport, but maybe someone else will chime in in the meantime.
moorsd
Obsidian | Level 7

Hi Timmy,

 

Thanks for getting back to me again. Any code you have that i could use as a base would be fantastic.

 

TBH, I don't need this is the next hour, but i was hoping to try and get something working by the end of this week early next week, so no rush.

 

thanks

 

David

Timmy2383
Lapis Lazuli | Level 10

Ok, here's some rough code I drafted that works in my environment.  Main thing is to add a value for your log path. If you're going to scrape batch logs too, then you'll need to comment out the end of the first DATA step (i.e. IF FIND(LOG,'Workspace')). I'm running in Linux environment so I think this should all be compatible.

 

You may also want to gather other metrics, but this is basically just looking at lines in the logs where it says how many observations were read from a table, and then getting the number of records read and then library/table from which they were read.

 

ODS LISTING CLOSE;
ODS RESULTS OFF;

/*Enter your logs path here*/
%let LogsPath=/*Enter your logs path here. Example: /sas/config/compute/Lev1/Logs */;
/*Enter a temporary location and file name to which you can copy each log file when attempting to parse*/
%LET TempLogLoc= /tmp/WorkspaceLog.log;

/*Get listing of all workspace logs*/
FILENAME XCMD PIPE "ls -l &LogsPath.";

/*Create dataset of output from X command*/
DATA LOGS;
	INFILE XCMD DSD TRUNCOVER;
	LENGTH TEXT LOG FILEPATH $512.;
	INPUT @1 TEXT;		
	LOG = SCAN(TEXT,-1,' ');
	FILEPATH = "&LogsPath./"||STRIP(LOG);
	/*Ensuring that only root directory is included in output*/
	OWNER = SCAN(TEXT,3,' ');
	GROUP = SCAN(TEXT,4,' ');
	IF FIND(LOG,'Workspace');	
	DROP TEXT;
RUN;

PROC SQL NOPRINT;
SELECT COUNT(*) INTO: LOG_COUNT
FROM LOGS;
QUIT;

PROC SQL;
DROP TABLE LIB_DATA_FINAL;
QUIT;

%MACRO ReadLogs;

%DO I=1 %TO &LOG_COUNT.;

	DATA _NULL_;
		SET LOGS;
		IF _N_ = &I. THEN DO;
			CALL SYMPUTX('LOG',FILEPATH);
			CALL SYMPUTX('USER',OWNER);
			CALL SYMPUTX('GROUP',GROUP);
		END;
	RUN;

	/*Creating temporary copy of log file to avoid write-locks*/
	
	%SYSEXEC cp &LOG. &TempLogLoc.;
	
	DATA WORKSPACE_LOGS;
		LENGTH LOG $512. USER GROUP $12. ;
		LOG = "&LOG.";
		USER = "&USER.";
		GROUP = "&GROUP.";
	RUN;

	DATA WORK.LIB_DATA;
	INFILE "&TempLogLoc" TRUNCOVER DLM='~';
	LENGTH TEXT LOG $512.
		   TABLE $32. RECORDS 8.;
	INPUT @1 TEXT;
	LOG = "&LOG.";	
	IF FIND(TEXT,'observations read') THEN DO;
		TABLE=SCAN(TEXT,-1,' ');
		RECORDS=INPUT(SCAN(TEXT,-8,' '),BEST12.);
		RETAIN TABLE RECORDS;
		OUTPUT;
	END;
	/*
	IF FIND(TEXT,'created, with') THEN DO;
		LIBREF=SCAN(TEXT,-8,' ');
		RECORDS=INPUT(SCAN(TEXT,-5,' '),BEST12.);
		RETAIN LIBREF RECORDS;
		OUTPUT;
	END;
	*/
	KEEP /*TEXT LOG*/ TABLE RECORDS;
	RUN;
	
	PROC APPEND DATA=WORK.LIB_DATA BASE=LIB_DATA_FINAL FORCE;
	RUN;

%END;

/*Removing temporary log file*/
%SYSEXEC rm /gsasadm/Logs/WorkspaceLog.log;

%MEND;

%ReadLogs;


	


moorsd
Obsidian | Level 7

Hi Tim,

 

First off I won't to apologise for not replying sooner. I've  got called onto doing some different work so didn't get around to checking up on this thread. Not great I know 😞

 

Thanks for the code. I'll give it a try. It looks quite comprehinsive though 🙂

 

I've accepted this as a solution so at least you'll get some credit for the effort you've put in. Thanks for sharing you're work with me. It's much appreciated.

 

kind regards

 

 

David

Timmy2383
Lapis Lazuli | Level 10
No worries. I hope it works for you!

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 

Get Started with SAS Information Catalog in SAS Viya

SAS technical trainer Erin Winters shows you how to explore assets, create new data discovery agents, schedule data discovery agents, and much more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 2565 views
  • 1 like
  • 2 in conversation