Audit Log Retention and Cleanup Procedure in VCF Automation

Products

• VCF Automation

Issue/Introduction

This article provides instructions on how to clean up the ebs_audit_log table in VCF Automation. Performing this cleanup is necessary if the table grows too large, which can cause upgrade deployments (such as upgrading from VMware Aria Automation 8.18.1 to VCF Automation 9.0.2) to fail.

Currently, there is no automated audit trail retention policy enforced for Audit Logs. By default, all audit logs are retained indefinitely.

Symptoms of an overgrown audit log table during an upgrade include:

• Deployment Error Code: LCMVMSP10002 ("Failed to deploy product on the application platform").

• Pod State: The ebs-app pod enters a continuous CrashLoopBackOff state due to readiness probe failures.

Environment

 Aria Automation 8.18.x

VCF Automation 9.0.x

VCF Automation 9.1.x

Cause

Over time, the ebs_audit_log table can accumulate an excessive number of records. If the table exceeds the recommended limit of approximately 2 million rows, database schema changes during an upgrade take longer than 30 seconds to execute. This results in a database connection timeout, which ultimately causes the ebs-app pod to fail and prevents the system from starting up. Cleaning up older records reduces the table size and ensures database operations complete within the required timeout threshold.

Resolution

The recommended maximum size for the ebs_audit_log table is ~2 million records. If the table contains significantly more data, perform the steps below to reduce audit logs.

 Note: Take a snapshot of all the Aria Automation nodes and perform these steps prior to starting the upgrade from Aria Automation 8.18.1 to VCF Automation 9.0.2 during a scheduled maintenance window.

Steps to reduce audit log records in Aria Automation 8.18.x

1.vracli dev psql

2. #Connect to the ebs-db
\c ebs-db;

3. #Check the count
select count(*), partition_year from ebs_audit_log group by partition_year;


4. #Backup only a specific year (e.g. 2024):
CREATE TABLE ebs_audit_log_2024_bkp AS
SELECT id, message, event_type, user_name, org_id, project_id, trace_id, event_date, severity, project_name, partition_year
FROM ebs_audit_log
WHERE partition_year = '2024';


5. DELETE FROM ebs_audit_log WHERE partition_year = '2024';

6. #Check the count
select count(*), partition_year from ebs_audit_log group by partition_year;

7. #If you want to recover db space drop the temporary table
drop table ebs_audit_log_2024_bkp;

Steps to reduce audit log records in VCF Automation 9.x

        Note: Take a On-Demand Backup of VCF Automation and VCF Management Services before starting the cleanup procedure. 

1. #Set the kube context, use the kubeconfig.
export KUBECONFIG=/etc/kubernetes/admin.conf

2. #Connect to the leader node

kubectl -n prelude exec -it $(kubectl exec -n prelude vcfapostgres-0 -- patronictl list 2>/dev/null | awk '/Leader/ {print $2}') -c postgres -- psql -U postgres

3. #Connect to ebs_db.
\c ebs_db;

4. #Check the count
select count(*),partition_year from ebs_audit_log group by partition_year;

5. #Backup only a specific year (e.g. 2024):
CREATE TABLE ebs_audit_log_2023_bkp AS
SELECT
    id,
    message,
    event_type,
    user_name,
    org_id,
    project_id,
    trace_id,
    event_date,
    severity,
    project_name,
    partition_year,
    entity_name,
    entity_id,
    entity_type
FROM ebs_audit_log
WHERE partition_year = '2024';

6. #Delete the records
DELETE FROM ebs_audit_log WHERE partition_year = '2024';

7. #Check the count
select count(*),partition_year from ebs_audit_log group by partition_year;

8. #If you want to recover db space drop the temporary table
drop table ebs_audit_log_2024_bkp;