Archiving Data from a CA SiteMinder Audit Store

Document ID : KB000030200
Last Modified Date : 14/02/2018
Show Technical Document Details

You have configured CA Siteminder Audit Logs to use an ODBC Audit Store.  Over time the Audit Store database continues to grow.  You no longer need the oldest audit data and would like to purge the oldest data to remove it from the database.

 

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)


CA SiteMinder 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.


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 which 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 orgranization.


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 schedule to run at regularly recurring intervals and constantly prune the data from the Audit Store which is older than the number of day specified.

 

References for OEM syntax:

Oracle DELETE Statement:
http://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems014.htm

Oracle Operators:
http://docs.oracle.com/html/A95915_01/sqopr.htm

MS SQL(T-SQL) DELETE Statement:
http://msdn.microsoft.com/en-us/library/ms189835.aspx

MS SQL(T-SQL) Operators:
http://msdn.microsoft.com/en-us/library/ms174986.aspx