Parsing SASAPP_WorkspaceServer Logs for table names

[Denise]

Have any of you parsed Workspace logs to find information on the queries that run on your server?

I'd like to parse mine to find the most frequently used tables from SAS to another DB (like Oracle or Netezza).

I'm looking for some high level direction to build this custom code if no other tools exist.  Right now, all that comes to mind is to search for libnames and then the "set" or "from" keywords throughout the code.

Thanks!

Denise

[gwootton]

Have you looked into the SAS Environment Manager Service Architecture Framework's reports to see if they have the desired level of detail?

SAS® Environment Manager 2.5: User’s Guide - Using the Report Center - Finding the Reports You Need

[Denise]

Thanks, Greg.  I did review it after you provided the link.  I don't clearly see that type of reporting provided out-of-the-box.  I'd need to look for custom librefs and names to parse.

I did see some reports that I'd be interested in using for different purposes so I will be setting up the data mart to read the log for other purposes.

[gwootton · Accepted solution]

Thanks Denise, there is a report "Resource - Data Usage" that reports on table frequency.

[Denise]

Greg,

I've turned on Environment manager service architecture framework in my UAT environment and it's been collecting some data.  This is exactly what I was looking for and more!  Thanks so much for the guidance.

Denise

[Kurt_Bremser]

I have our workspace server running with a customized logconfig.xml that catches the logs sent back to the client to files. I therefore do such searches (where I know the libname) with

grep "LIBNAME." /sasconf/Lev1/SASApp/WorkspaceServer/Logs/*

You can run that with INFILE PIPE to read the results into a dataset for further analysis.

[JuanS_OCS]

Really good question. There are many ways. 

In SAS Environment Manager there is an out of the box method, and you can also parse a log with its own log parser. It can be used to trigger alerts, or just to collect the data and later on count.

My favorite methods are usually external ones. One of these 2 options:

1- Log from the OS level (and network) and parse it with external tools.

2- Enable SAS Workspace (and web apps) to log what I want (this needs a bit additional knowledge on Log4J components and SAS jars), then parse those logs with external tools.

See the pattern, i like to parse with external tools. Here some really useful ones: https://stackoverflow.com/questions/2327073/parse-a-log4j-log-file

This is focused on log parsers based on Log4J, just because SAS uses Log4J and it is easy and quick to benefit from that design. 

However, any log parser tool can work actually. See google results:

https://www.google.com/search?rlz=1C1GCEU_nlNL854NL854&sxsrf=ALeKk01YjUY7_i16siyEHhITkbBy-Wpv1A%3A15...

If you are interested in learning about the SAS logging facility (option 2), have a read to https://documentation.sas.com/?docsetId=logug&docsetTarget=p1ux9lxccgetcgn1hmkhm64m3ud0.htm&docsetVe...

Last but not the least, if Auditing is really important, you might be interested in the SAS Federation Server. Of course it comes at a cost, and it many case it can be more costly than what you can get from it. In other hand Security and Data Officers love it. So I guess it all depends on who will pay for it.

[ronan]

There is another way, by enabling ARM log files which requires to modify the Log4j SAS Workspace configuration (not an easy task). Using ARM logging, you can even drill down to the column level.

https://support.sas.com/rnd/scalability/tools/arm/v9armlog.html

The predefined reports installed with the SAS Environment Manager Framework Kit are the easiest way to go imho (these reports are based on ARM logging as regards Data access monitoring). Enabling full logging for Workspace Server sessions is usually not recommended : this consumes both computing ressources and storage. This said, if your SLA requires a high level of application tracing then the full log is inescapable.