How to purge older data from AuditRecords in SOI Db
book
Article ID: 190716
calendar_today
Updated On:
Products
CA Service Operations Insight (SOI)
Issue/Introduction
We found 133 million records in AuditRecords in SAMStore Database, due to this high number of records, SOI console doesn't show Audit Records for CIs & services How to delete older records from this table?
Environment
Release : 4.2
Component : Service Operations Insight (SOI) Manager
Resolution
Please follow this procedure to delete older records from this table
1. select count(*) from AuditRecordActions (note down the count)
2. select * into AuditRecords_temp from AuditRecords (this will move all records from AuditRecords to temp table)
3. truncate table AuditRecords (truncate all records from this table, this will be faster as this table has too many records)
4. insert into AuditRecords(ID,Type, TypeDetail, InternalID, TenantID, Action, ActionDetail, Component, UserName, TimeStamp) select * from AuditRecords_temp where TimeStamp > '2020-05-01'
Note: You may change the above where condition value if required, the above query will copy records > 1st May 2020
5. select * from AuditRecords (this is to confirm to see it has records after 1st May 2020)
Note: It is recommend to run these queries while SOI manager is shutdown also recommend to try this in test environment first