RE: How to track who accessed what table and columns

[BrunoMueller]

Ram

You would need to have a logger for all the App.Program messages and check if there is an easy way to match both.  I do not have an example for that.

I suggest to add another logger for all the App messages at the Info level and see which messages will help you to achieve what you need.

Bruno

[Ram4sas]

Hi Bruno,

i have tried to add attached logconfig file to generate log file for user code what is being executing and list of tables accessed.

as i replied i was able to generate tables details which user access when. but not able to grab the user executed code information.

what is wrong with the attached code which show workspace server validation error? am i droping smoething?

===============================================================================

<?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="AuditLibraryFile" class="FileAppender">

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

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

   <param name="fileNamePattern" value="/opt/sas/Config/Lev1/Audit/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} " />

   </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>

====================================================================================

Thanks,

Ram

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

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

   <appender-ref ref="AuditLibraryFile"/>

   <level value="Trace"/>

</logger>

<-- Send App.Program messages directly to the log file -->

<logger name-"App.Program" additivity="false">

<appender-ref ref="file" />

<level value="INFO" />

</logger>

<!-- Root logger -->

   <root>

      <!--

      <level value="ERROR"/>

      <appender-ref ref="Normal"/>    

      -->

   </root>

</logging:configuration>

[Ram4sas]

once again sharing the logconfig content here:

==========================================================

<?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="AuditLibraryFile" class="FileAppender">

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

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

   <param name="fileNamePattern" value="/opt/sas/Config/Lev1/Audit/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} " />

   </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>

<-- Send App.Program messages directly to the log file -->

<logger name-"App.Program" additivity="false">

<appender-ref ref="file" />

<level value="INFO" />

</logger>

  <!-- Root logger -->

   <root>

     <!--

      <level value="ERROR"/>

      <appender-ref ref="Normal"/>    

      -->

   </root>

</logging:configuration>

[PaulHomes]

The <logger name="App.Program"> you posted has a minus sign instead of an equals sign. Additionally it contains an <appender-ref ref="file" />, but you have no <appender> tag with a name="file". You need to either change the ref="file" to point to an existing appender by its name, or add a new <appender> block with name="file".