How to clear and delete audit records from the Audit database

Products

• CA Single Sign On Secure Proxy Server (SiteMinder)
• CA Single Sign On SOA Security Manager (SiteMinder)
• CA Single Sign-On

Issue/Introduction

Historical audit data can be archived and deleted from the Siteminder and IdentityMinder Audit database tables.

This task will require DBA access privileges to set up a script, based on the information provided here, and to then determine how often  the script should be run

 

Environment

CA SiteMinder all applicable releases

Resolution

If Audit logs are suspected to be causing slowness in SiteMinder and IdentityMinder it may be advisable to clear some of the audit logs in the corresponding Auditing database tables. In this respect, the tables which will contribute more to slowness and disk space usage are the following:

SMACCESSLOG4
SMOBJLOG4
IMSAUDITPROFILES5
IMSAUDIT5

The basic rules for deletion of old Audit log data (with SQL syntax includes) are:

SiteMinder Audit Data Purge

For the two Siteminder tables:

SMACCESSLOG4
SMOBJLOG4

delete from smaccesslog4 where sm_timestamp < 'dd-mmm-yy' >
delete from smobjlog4 where sm_timestamp < 'dd-mmm-yy' >

Identity Minder Audit Data Purge

For the two Identity Minder tables:

IMSAUDITPROFILES5
IMSAUDIT5

The SQL command syntax in this case is a little bit more complex since the timestamp is present in imsaudit5 but not in imsAuditProfile5, so it is necessary to link both via table  ims_eventId tojoin both. The resulting  command would be in this case

delete from imsauditprofiles5
where ims_eventid in (select ims_eventid
from imsaudit5
where ims_audittime < 'dd-mmm-yy' >)

delete from imsaudit5 where ims_audittime < 'dd-mmm-yy' >

The above scripts are based on an Oracle format database. Scripts may have to be adjusted to suit the current environment and it is strongly recommended in all cases to test them with a Development or Test system to prove they work properly before engaging a production system.