Archiving Data from Audit Store when in use with Policy Server

Products

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

Issue/Introduction

 

Having configured Audit Logs to use an ODBC Audit Store with Policy Server. Over time the Audit Store database continues to grow. The oldest Audit data may not be needed anymore. How to purge the oldest data to remove it from the database?

 

Resolution

 

The CA SiteMinder Audit Store is comprised of the following tables when stored in an ODBC Database:

  SMACCESSLOG4
  SMOBJLOG4
 
Both the SMACCESSLOG4 and SMOBJLOG4 tables contain the "sm_timestamp" column.

SM_TIMESTAMP format:

  'YYYY-MM-DD hh-mm-ss.sss'

  YYYY = Year (4-digit)
  MM = Month (2-digit)
  DD = Day (2-digit)
  hh = Hour (2-digit)
  mm = Minute (2-digit)
  ss = Second (2-digit)
  sss = Millisecond (3-digit)

Policy Server does not have a mechanism to delete this data.

This data may need to be periodically archived or purged from the database.

This can be done either manually using a Query tool, or by configuring a recurring job to run (1)(2)(3)(4).

Manual Deletion of Data Based on a Specific Date

These commands will manually delete any records which are EQUAL TO the date/time specified in the command.

  DELETE FROM smaccesslog4 WHERE sm_timestamp = 'YYYY-MM-DD hh:mm:ss.sss'
  DELETE FROM smobjlog4 WHERE sm_timestamp = 'YYYY-MM-DD hh:mm:ss.sss'

These commands will manually delete any records which are OLDER than the date/time specified in the command.

  DELETE FROM smaccesslog4 WHERE sm_timestamp  < 'YYYY-MM-DD hh:mm:ss.sss'
  DELETE FROM smobjlog4 WHERE sm_timestamp < 'YYYY-MM-DD hh:mm:ss.sss'

These commands will manually delete any records which are EQUAL TO OR OLDER than the date/time specified in the command.

  DELETE FROM smaccesslog4 WHERE sm_timestamp  <= 'YYYY-MM-DD hh:mm:ss.sss'
  DELETE FROM smobjlog4 WHERE sm_timestamp <= 'YYYY-MM-DD hh:mm:ss.sss'

Automated Deletion of Data

The following is an example of a query that will use the Database specified in the <Audit Store DB> value, and delete any records from the smaccesslog4 table as well as the smobjlog4 table which have a date in the sm_timestsamp field older than 180 days.

The number of days can be modified to accommodate the needs of the organization.

  USE <Audit Store DB>
  go
  DELETE FROM smaccesslog4 WHERE sm_timestamp < GETDATE()-180
  go
  DELETE FROM smobjlog4 WHERE sm_timestamp < GETDATE()-180
  go

This query can be scheduled to run at regularly recurring intervals and constantly prune the data from the Audit Store which is older than the number of days specified.

 

Additional Information

 

(1)

    Oracle DELETE Statement:
    

(2)

    Oracle Operators:
    

(3)

    DELETE (Transact-SQL)
    

(4)

    Operators (Transact-SQL)