Controlling the Size of the Web Infrastructure Platform Database Audit Tables

4

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.

WIPAudit

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

WIPAudit2

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.

WIPAudit3

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:

WIPAudit4

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:

WIPAudit5

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.

WIPAudit6

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.

Share

About Author

Gerry Nelson

Principal Technical Consultant

Gerry Nelson is a Principal Technical Architect in the Global Enablement and Learning (GEL) Team within SAS R&D's Global Technical Enablement Division. His primary focus is on administration of SAS environments, particularly migration, promotion and upgrading.

Related Posts

4 Comments

  1. Great article. I recently had to do this as there was an issue with VA 7.1 as noted in http://support.sas.com/kb/55/899.html

    You mentioned that archiving of these audit records is configured by default and is set to run weekly. Is there any way to check that it is in fact configured? When I goto pgsql, the _archive tables are empty while the main tables have records all the way from February. I couldn't find any reference as to how one can verify whether default archiving is setup or not.

    Thanks!

    • Gerry Nelson

      Thanks, I may do a follow up blog around archiving.

      In postgres check the sas_audit_archive_rules table. If it does not have any rows then nothing will be archived. I have seen this happen in earlier versions of VA. You can add rules and it should archive based on the rules. The archive will happen when the web app server starts or at the next scheduled time.

      For guidance on the rules see SAS(R) 9.4 Intelligence Platform: Middle-Tier Administration Guide: Archive Process for Audit Records:

      http://support.sas.com/documentation/cdl/en/bimtag/68217/HTML/default/viewer.htm#n06skrc2rtwecsn14vi4qtfl54kx.htm

      It has some common rules you can add to the table.

      Hope that helps.

      • Thanks. I contacted TC and they gave me the default rules from 7.3 which were apparently missing in 7.1 (which was empty). I imported them and hopefully it should archive some to _archive tables next Monday. After that, I guess setup a cron to do what you have done, delete old data from these _archive tables. Again, very helpful post.

        Just want to add that for UNIX users and envs where one can't just download tools off from web (pgAdmin), the needed psql CLI utility is available within the install folder itself on the server. For example, you need to add the LIB path to LD_LIBRARY_PATH and run psql from the server itself.

        for example, if location of WIPDS is SASINSTALLLOC/sashome/SASWebInfrastructurePlatformDataServer/9.4/, then there will be a bin and lib folders under it. One needs to add lib to library path as below and then run the psql utility present in the bin folder.

        $ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/sasinstall/sashome/SASWebInfrastructurePlatformDataServer/9.4/lib

        $ /sasinstall/sashome/SASWebInfrastructurePlatformDataServer/9.4/bin/psql -P 9432 -U xxxxxx -d SharedServices

Leave A Reply

Back to Top