In my last blog (What is going on with my Visual Analytics audit data collection) I reviewed how middle-tier auditing works and where to look for logs to debug issues. In this follow up blog I will do the same for the Visual Analytics audit data archiving process. The archive process is important, to quote the Visual Analytics Administration guide “CAUTION! Audit data can consume significant amounts of disk space and processing capacity.”

By default middle-tier auditing includes logon and logoff records. A SAS Deployment will always record logon and logoff activity in the audit tables. When Visual Analytics auditing is enabled a lot of additional activity is recorded, and the size of the audit tables in the Shared Services database can grow quickly. The first step in managing the space that is consumed is to archive data from the audit tables. The archive process is documented in the SAS Middle Tier Administration Guide.

To determine what records to archive an archive job reads the archive rules in the SAS_AUDIT_ARCHIVE_RULE table. The archive job always starts when SAS Web Infrastructure Platform Services starts.  In addition, the default archive job is scheduled to start every Monday at the start of the day, but the archive job schedule can be configured. To change the frequency of the archive see the SAS Middle Tier Administration Guide .

The SAS_AUDIT_ARCHIVE_RULE  table includes the object_type_id and action_type_id and the frequency to archive. As my previous blog noted, to understand what objects and actions are being archived view SAS_TYPE_OBJECT table, which maps an object_type_id to a SAS metadata object, and the  SAS_TYPE_ACTION table, which maps the action_type_id to a user action.

In SAS 9.4 M3 common rules are added to the table so that the process will archive records from SAS_AUDIT to SAS_AUDIT_ARCHIVE, and SAS_AUDIT_ENTRY to SAS_AUDIT_ENTRY_ARCHIVE.

Let's look at the rules. In the SAS_AUDIT_ENTRY_ARCHIVE table the object_type_id and action_type_id identify the object and action to archive. The frequency_no identifies the age of the record to archive. Data is archived if there is an action for that object older than the frequency_no. The frequency is recorded in milliseconds with a default value of 2592000000 which is 30 days.

For example,  rows 1 and 2 below show that for actions where the object  is unknown (-1), and the action is logon(8) and logoff(9), data will be archived when records are older than 30 days based on the timestamp of the action.

Rows 6 thru 9 are for the  object_type_id=32 which is a table and action codes 36=ADD, 45=READ, 48=RELEASE and 49=REGISTER.  This data will be archived when the records are older than 30 days. You can modify this table to change the retention period in the database or to add additional objects and actions.

You can confirm the archive job runs and reads the archive rules by adding a logging context to com.sas.svcs.audit at the INFO level.

In the /Lev1/Web/Common/LogConfig/SASPrincipalServices-log4j.xml add the following XML.

<category name="com.sas.svcs.audit">
        <priority value="DEBUG"/>
        <appender-ref ref="SAS_CONSOLE"/>
        <appender-ref ref="SAS_FILE"/>
 </category>

This will result in the archive process writing messages to the log as it processes the archive rules.

In addition, if you turn on additional logging on the Web Infrastructure Platform data server you can see the SQL queries which insert into the archive tables and delete from the audit tables. To do this edit the Lev1/WebInfrastructurePlatformDataServer/data/postgresql.conf file and set

• log_statement = ‘all’
• log_min_messages = info

For your logging changes to be implemented execute

D:\Program Files\SASHome\SASWebInfrastructurePlatformDataServer\9.4\bin>pg_ctl reload -D D:\SAS\vaconfig\Lev1\WebInfrastructurePlatformDataServer\data

CAUTION: You should only set this temporarily as the Web Infrastructure Platform data server logs grow very large with enabled.

As I mentioned previously, archiving is turned on by default. However, prior to Visual Analytics 7.3 there were no records in the SAS_AUDIT_ARCHIVE_RULE table. In deployments prior to SAS 9.4 M3, the archive process would run but no records would be archived since no rules were defined in SAS_AUDIT_ARCHIVE_RULE.

To fix this issue in these deployments you can add rules to the table based on the common rules defined in the documentation. This is even more important if you have  enabled auditing for Visual Analytics because the auditing collects many more events to the SAS_AUDIT and SAS_AUDIT_ENTRY tables. If no archive work is done then the SAS_AUDIT and SAS_AUDIT_ENTRY tables can grow large and eventually  have a  negative impact on the Visual Analytics web application performance. The web applications can start to run very slowly, and in some cases may not be accessible.

As a result of the archive process runnning when the Web Infrastructure Platform Data Server starts, the initial run of the archive can cause problems for upgrading SAS Deployments.  During an upgrade to Visual Analytics 7.3 the  SAS_AUDIT_ARCHIVE_RULE table will be populated and the archive process will attempt to archive any data which matches the new rules. If the audit tables are very large then this process can significantly slow the upgrade process and may even cause the WIP machine to max out CPU. This issue may not only apply to Visual Analytics environments, but other SAS environments with heavy usage of Web Applications where auditing of logon and logoff is recorded by default.

A SASNOTE has been published about this issue and has already helped.

Two ways you can avoid the issue. Firstly, prior to an upgrade enable auditing on the source environment by populating the SAS_AUDIT_ARCHIVE_RULE table with rules from the SAS Middle Tier Administration Guide . The documentation contains a subset of the rules defined in 9.4 M3 and VA 7.3.

Secondly, check the size of the audit tables prior to the upgrade and run queries to archive and purge data.

1) Check the size of the data in the tables

select count(*) from SAS_AUDIT;
select count(*) from SAS_AUDIT_ENTRY;

2) Archive and then delete records from the SAS_AUDIT and SAS_AUDIT_ENTRY tables

insert into sas_audit_archive select * from sas_audit;
DELETE FROM sas_audit;”

insert into sas_audit_entry_archive select * from sas_audit_entry;”
DELETE FROM sas_audit_entry;”

When the audit archive tables grow too large you can use the techniques in my blog Controlling the Size of the Web Infrastructure Platform Database Audit Tables to purge audit records from those tables.