SAS Visual Analytics contains an Administrator Overview report which provides for Visual Analytics usage information by application, user, and object.

To provide data for the report you must enable collection of key actions audit data in the Visual Analytics Middle-Tier. Auditing doesn’t just apply to Visual Analytics, it can be enabled for most SAS web applications and other SAS middle-tier services.

For complete details on how to set up auditing see Configuring Auditing for SAS Web Applications in the SAS(R) 9.4 Intelligence Platform: Middle-Tier Administration Guide. When auditing is enabled, audit records are continuously generated when user activity occurs in the environment, and stored in the Web Infrastructure Platform service database.

Audit data is written to the SAS_AUDIT and SAS_AUDIT_ENTRY tables in the public schema of the SharedServices database. Audit data can grow large quickly. For that reason it is recommended that audit records are:

• Archived regularly
• Purged when no longer needed

Archiving of records to the SAS_AUDIT_ARCHIVE and SAS_AUDIT_ENTRY_ARCHIVE tables is configured by default and runs weekly. Purging of data to recover space is not configured. The documentation recommends that you connect to the database using a database client and use SQL to purge the data.

In this blog I will show you how to purge archive data from the database using the pgAdmin client. This is appropriate when the WIP database is stored in its default PostgreSQL database.

pgAdmin is a design and management interface for the PostgreSQL database. It provides a graphical interface to all the PostgreSQL features including creating queries and editing database tables. PgAdmin is a free tool which can be downloaded from this site.

The first thing you need to do to use the tool is create a connection to a database server. To create a server connection select File > Add Server. The new server registration box will be displayed and you can enter the details of the server. To connect to the server enter the host and port where the database server is running (for SAS typically on the compute tier). Also enter the username and the password you specified when deploying your environment.

When connection information is defined right-click on the server and select Connect. You can then navigate the server and view the databases available.

Note that the red “X” on a database simply means that you haven’t yet connected to it. Using the tree you can view the database objects. For example, if we select SharedServices > Schemas >Public > Tables a list of tables in the SharedServices database is displayed. Right-click on a table to view the data or perform other actions.

I don’t need to tell you (do I?) that you should be very careful when accessing the Web Infrastructure Platform databases with this tool. The pgAdmin tool is powerful, and deleting or changing the wrong thing could corrupt your database and break your SAS deployment. Before using the tool use the SAS Backup facility, which by default creates a backup of the Web Infrastructure Platform databases, to create a backup of the deployment.

To the task at hand! We want to delete any records in the audit archive table that are more than 90 days old. To do that we need to understand the structure of the audit tables.  If you select the table you are interested in you can view the table structure by selecting columns:

If you need more detail, select the table, right-click and select View Data > Top 100 Rows to view the first 100 rows in the table:

To create and run a query select Tools > Query Tool. First we will run a simple query to see how many records in SAS_AUDIT_ARCHIVE are older than 90 days. Write the query in the SQL editor window and select Query > Execute. This query lists all records from sas_audit_archive that are older than 90 days.  The result of the query is displayed in the Output pane.

There are example queries in the Purging Audit Records section of Configuring Auditing for SAS Web Applications in the SAS(R) 9.4 Intelligence Platform: Middle-Tier Administration Guide. The example queries show that there is a relationship between the two tables. To purge the data we will need to delete records in:

• SAS_AUDIT_ARCHIVE where the timestamp is more than 90 days ago
• SAS_AUDIT_ENTRY_ARCHIVEwere the audit_id matches the records deleted from SAS_AUDIT_ARCHIVE.

To purge from sas_audit_entry_archive run this query which deletes records where the audit_id from the sas_audit_entry_archive table is one of the audit_id’s in the sas_audit_archive table that was created more than 90 days ago.

DELETE FROM public.sas_audit_entry_archive
where audit_id in (select distinct audit_id from public.sas_audit_archive where
sas_audit_archive.timestamp_dttm < NOW() – INTERVAL ’90 DAYS’) ;

Then once this is completed run the following query to purge from the sas_audit_archive table.

DELETE FROM public.sas_audit_archive
WHERE  sas_audit_archive.timestamp_dttm < NOW() – INTERvAL ’90 DAYS’ ;

Done! To make your life easier you would want to automate the process. To do that you can save the queries to a file and schedule them to run periodically.  With the queries saved to a file named delete_audit.sql schedule a batch job that includes the following command:

c:\pginstalldir\psql -p 9432 -h xxxxxxx.xxxx.sas.com -d SharedServices -U dbmsowner -f C:\Users\sasinst\Documents\delete_audit.sql -o c:\temp\output.txt

Using these techniques you can prevent your SharedServices database from consuming too much disk space when middle-tier auditing is enabled.