Periodic Cleanup of Historic Data in ISD Database

Note: Please ensure to configure periodic backup of the database before enabling periodic cleanup.

Periodic cleanup of historic data in ISD DB

The Periodic ISD DB cleanup is the process in which the data in the DB tables of ISD across multiple databases are cleaned on a regular basis. To keep the audit DB size under control for optimal performance, this feature is enabled.
Important: Please note that the periodic DB cleanup is disabled by default.
The list of tables that are cleaned up during this process are:
Approval_gate_instance → parent table
LogAnalysis Related Tables
· Contextualcluster
· Contextualtemplate
· Userlogfeedback
· Tagsfeedback
· Patternfeedback
· Entropy
· Logclusterscorereduction
· Logcluster
· loganalysis
Metric Analysis Related Tables
· versionstats
· metricscore
· logclusterdetails
Common tables
· serviceriskanalysis
· riskanalysis_imageid_info
· riskanalysisjob
· riskanalysis

Configuration for DB cleanup in ISD:

To enable DB cleanup in ISD, you need to set up a few configurations in ISD as mentioned below.
  1. 1.
    Edit the isd-feature-flag-config config map and set db-cleanup flag as true. Use the command below to edit the config map.
kubectl edit cm isd-feature-flag-config -n <namespace name>
  1. 2.
    Edit the platform secret and modify the database section as per your requirement for cleaning up the database. Use the command below to edit the platform secret.
kubectl edit secret oes-platform-config -n <namespace name>
  1. 3.
    Modify the database section configuration in platform secret as below.
    • database:
    • Cleanup:
    • Scheduler
    • enabled: true
    • daysInterval: 18
    • cron: 0 0 0 * * *
daysInterval: The number of day intervals in which you want ISD to clean the database should be mentioned here. For example if the value is given as 18 days then ISD will delete 18 days old data in the database.
cron: To trigger the database cleanup,you should give the cron expression. Database cleanup cron job will trigger based on the value provided in the cron expression.
For example if daysInterval and cron expression are given as mentioned below:
daysInterval: 18
cron: 5 * * * * *
Then, database cleanup will happen every 5 minutes and it will delete 18 days old data.
You can refer to the image below on how to set up cron expression.
  1. 4.
    Now restart the platform pod in your namespace. The database cleanup is set in your ISD successfully. Use the command given below to restart your platform pod.
kubectl rollout restart deploy oes-platform -n <namespace name>
To verify the database cleanup is done, you can connect to your database and execute the below commands.
To enter into platform db
\c platformdb
To get the total record count
select COUNT(*) from service_deployments_history;
To get the record count which is of 40 days older
select COUNT(*) from service_deployments_history where created_at < now() - interval '40 days';
\c visibilitydb
select COUNT(*) from approval_gate_instance;
select COUNT(*) from approval_gate_instance where activated_time < now() - interval '40 days';
Autopilot DB
\c opsmx
select COUNT(*) from riskanalysis;
select COUNT(*) from riskanalysis where opsmxcreateddate < now() - interval '40 days';
Sapor DB
\c oesdb
select COUNT(*) from policy_gate_status;
select COUNT(*) from policy_gate_status where created_date < now() - interval '40 days';
Audit DB
\c auditdb
select COUNT(*) from audit_events;
select COUNT(*) from audit_events where created_at < now() - interval '40 days';