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

Hi SAS Experts,

I got a task how to track the users accessed waht tables in SAS 9.4 EBI.

Example: User- X accessed table 'Y' on 06/15/2015 09:30:45 using SAS forecast studio or EG  or SAS Studio.

I am sure that APM can produce reports for Most heavily used SAS Procedure No. of times each User accessed Metadata, top ten user WS server..etc..

Is there a way to track the user list who accessed what table and columns and present using APM portal?

Thanks,

Ram

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

Hi Ram

Find below as sample logconfig that I have used sucessfully. Please make sure, that all users starting a Workspace server do have write access to the location where the log file are written.

If something is wrong with the XML then one get an entry in the system log giving the name of a file that indicates the type of error. The sample blow was made for Windows, so you have to change the location of the log file, also make sure, that you get unique filename for each process.

<?xml version="1.0" encoding="UTF-8"?>

<logging:configuration xmlns:logging="http://www.sas.com/xml/logging/1.0/" debug="true">  

<!-- Audit.Data.Dataset File Appender Definition -->

<!--

<appender name="Normal" class="FileAppender">

   <param name="Append" value="true"/>

   <param name="ImmediateFlush" value="true"/>

   <param name="fileNamePattern" value="c:\temp\normal_%d_%S{hostname}_%S{pid}.log"/>

   <layout>

      <param name="ConversionPattern"

       value="DateTime=%d Logger=%c Msg=%m" />

   </layout>

</appender>

-->

<!-- Audit.Data.Dataset File Appender Definition -->

<appender name="AuditLibraryFile" class="FileAppender">

   <param name="Append" value="true"/>

   <param name="ImmediateFlush" value="true"/>

   <param name="fileNamePattern" value="c:\temp\audit_dataset\audit.dataset_%d_%S{hostname}_%S{pid}.log"/>

   <layout>

      <param name="ConversionPattern"

       value="%d!%c!%u!jobid=%S{jobid} Action=%E{Audit.Dataset.Action} Status=%E{Audit.Dataset.Status} Libref=%E{Audit.Dataset.Libref} Engine=%E{Audit.Dataset.Engine} Member=%E{Audit.Dataset.Member} NewMember=%E{Audit.Dataset.NewMember} MemberType=%E{Audit.Dataset.Memtype} Openmode=%E{Audit.Dataset.Openmode} Path=%E{Audit.Dataset.Path} Sysrc=%E{Audit.Dataset.Sysrc} Sysmsg=%E{Audit.Dataset.Sysmsg} username=%S{user_name} startup_cmd=%S{startup_cmd} " />

   </layout>

   <filter class="StringMatchFilter">

        <param name="StringToMatch" value="Libref=SASHELP"/>

        <param name="AcceptOnMatch" value="FALSE"/>

   </filter>

</appender>

<!-- Audit.Data.Dataset.Open logger definition -->

<logger name="Audit.Data.Dataset.Open" additivity="false">

   <appender-ref ref="AuditLibraryFile"/>

   <level value="Trace"/>

</logger>

<!-- Audit.Data.Dataset.Delete logger definition -->

<logger name="Audit.Data.Dataset.Delete" additivity="false">

   <appender-ref ref="AuditLibraryFile"/>

   <level value="Trace"/>

</logger>

<!-- Audit.Data.Dataset.Rename logger definition -->

<logger name="Audit.Data.Dataset.Rename" additivity="false">

   <appender-ref ref="AuditLibraryFile"/>

   <level value="Trace"/>

</logger>

<!-- Root logger -->

   <root>

      <!--

      <level value="ERROR"/>

      <appender-ref ref="Normal"/>     

      -->

   </root>

</logging:configuration>

View solution in original post

33 REPLIES 33
MichelleHomes
Meteorite | Level 14

Hi Ram,

For SAS 9.4 the supported audit, performance and measurement package is with SAS Environment Manager. Information can be found at SAS Audit, Performance and Measurement.

If you look at the list of out-of-the box reports, you'll see there is a Data Usage report. You many need to tailor a custom report using this as a basis. http://support.sas.com/rnd/emi/SASEnvMgr/EVSAF/Report_Center_Report_Listings.pdf

I wrote a paper at SAS Global Forum 2014 about using APM to build a SAS Visual Analytics dashboard. It may give you some other ideas... http://support.sas.com/resources/papers/proceedings14/1247-2014.pdf

Kind Regards,

Michelle

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
Ram4sas
Fluorite | Level 6

Hi Michelle,

Thank you very much for your reply.

The system is already configured with Environement Manager, but no APM configureation done.

No SAS BI Dashboard and Visual Analytics products configured as well.

Can I still generate the Data Usage report with environment manager if i initiate APM component?

Thanks,

Ram

MichelleHomes
Meteorite | Level 14

Hi Ram,

Yes the Data Usage reports are part of APM and ARM logging and rendered via Environment Manager. You don't need BI dashboard or SAS Visual Analytics.

Kind Regards,

Michelle

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
Ram4sas
Fluorite | Level 6

Hi Michelle,

The Data usage report is for most heavily used data table.

The same I could see on " SAS Admins Need a Dashboard, Too" document page 5, right pic under performance Reports section.

if I enable APM component in the system, would i be able to see the data table usage by users like which user accessed what table?

Thanks,

Ram.

Ram4sas
Fluorite | Level 6

Hi Michelle,

My customer dont want to see most heavily used table, customer would like to see always what are the tables being access by user.

For Audit purpose need the users list who are all accessing the list of tables with datetime stamp.

Please guide me how to implement this. can you please share if any sample report you have like data usage.html report? any link for that?

Thanks,

Ram

MichelleHomes
Meteorite | Level 14

Hi Ram,

The links above provide you access to how you implement it and the documentation. Sample reports are in my SAS Global Forum paper and other papers I'm sure. I'd suggest looking at the ARM tables created as this will contain the data to build the custom report you are after.

Kind Regards,

Michelle

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
Kurt_Bremser
Super User

Hi!

How is the data usage report compiled? Is the data taken from analysing all logs, so that access to datasets by "manually" entering libnames and using them in data/sql steps is also caught? What happens when users simply start a DMS SAS, or run a batch job?

jakarman
Barite | Level 11

Logging can be done and configured with arm for the resources inside SAS. With external resources like a dbms or at the os level you have those resources. A complete audit and logging requirement for all data is needing a holistic view.

That requirement is coming as of regulations.

It is rather pittyfull SAS is missing those basic principles for the why's of these guidelines and regulations.

---->-- ja karman --<-----
Ram4sas
Fluorite | Level 6

Hi Jaap,

Do you mean that we can not get the report for data table usage reports using SAS APM with Environment Manager. yes i agree like for DBMS those are having this audit access monitor tools. my customer wanna to see the reports what users accessing which reports.

I have check with SAS Techsupport as well, i could see the below one

"The ARTIFACT.AUDIT_ACCESSC table contains audit records for object access events. If the feed to SAS Visual Analytics is enabled, this table is copied to the drop zone."

But we dont have SAS VA configured. I am wondering either i can generate report for data table usage in SAS or not.

Thanks,

Ram

Ram4sas
Fluorite | Level 6

Hi Jaap,

there was typo in my previous reply like..

my customer wanna to see the reports what users accessing which all tables (not report).

thanks,

Ram

jakarman
Barite | Level 11

Your question is getting more complicated.

Apm better classified arm logging can be configured to log all kind of events with using SAS in a controlled environment.

You can tailor that to any kind  of access event to tables using SAS on the server. It can be tailored to see any SAS code usage as long that is run on your server. It would be weird for me to have the SAS code (is that what you men by report?) being logged with accessing tables as that combination looks to me meaningless. That combination could be a result of reverse engineering when the design of code is missing.

SAS will never be able to log the access to tables using a rdbms like oracle using something like TOAD. That access of data is not having anything of SAS involved. The same could be for downloaded SAS datasets to another machine that is not in control of access monitoring.

---->-- ja karman --<-----
Ram4sas
Fluorite | Level 6

Hi Jaap,

Let me put like as simplest...System is SAS 9.4 already configured with environement manager its working fine for environement web reports.

I would like to see the report for data tables accessed users list with datetime stamp like who accessed tables using sas metadata library or using EG quering libanem statement. the data set can be from Oracle or internal data set to SASApp server.

The report can be a environment manager web reprot or other.

If i enable SAS APM, would i be able to get that report what i am looking for?

Thanks,

Ram

jakarman
Barite | Level 11

Ram4sas  the environment manager is the successor of apm. Both are based on arm logging. The environment manager is adding some things of vfabric vm tools and another dbms postgres as of that. It comes with a lot of sample reports and starters first access screens as of operational support.

When the operational support is an segregated department as is normal ib bigger professional organizations there is business challenge to alignment.

Those sample reports can be added to new ones when having access to the collected data. That is what Michelle posted with admins are needing a dashboard too. Building can be done in any tool and as SAS support staff it makes sense to use the SAS tools. But that is not really a hard one.

As you are mentioning eguide Libnames with oracle, that is a bad description. The sas sessions being in control at the server with monitoring can arm tailored. Using eguide with a local sas session on a desktop will not. Both are eguide and sas usage.

Eguide is more a terminal aaproach your question on monitoring and auditing is about data governance and structuring environments.

---->-- ja karman --<-----
Kurt_Bremser
Super User

Serious operating systems (and I an NOT talking about Windows here!) provide auditing mechanisms at the OS level, so you can record every simple open/close/read/write event. This is the only method of reliably catching ALL accesses to resources, as there cannot be access outside of the system calls provided by the kernel.

I seriously doubt that the Environment Manager will be able to report about dataset access that is being done in a SAS session that does not connect to the metadata sever and the EBI structure at all (batch, DMS).

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
  • 33 replies
  • 8241 views
  • 2 likes
  • 6 in conversation