There is a known issue related to data not purging based on retention settings. To confirm whether this issue is occurring, check value in logapprtrpt.stDataFiles where external_expiration_time contains a mixture of valid time values and NULLs.

1. Log in to the CLI as "toor".
2. Execute both of the following queries:
$ mysql logapprtrpt -e "select count(*) from stDataFiles where external_expiration_time is NULL;"
$ mysql logapprtrpt -e "select count(*) from stDataFiles where external_expiration_time is not NULL;"

If both query results are greater than zero then you have a mix of Snaplock and non-Snaplock entries. This is fixed in LMI 5.1.0.

 

This article describes a known issue with LMI not properly purging data using the configured retention settings.

The workaround is to manually set the external_expiration_time based on the collection times so that engine_archive will properly purge them. In order to get the retention settings to automatically be utilized again you must determine the number of seconds equal to your retention period.

For example, if retention is set to one year:
    60 (sec/min) * 60 (min/hr) * 24 (hr/day) * 365 (day/YY) = 31,536,000

1. Backup the existing stDataFiles table:
$ mysqldump logapprtrpt stDataFiles > stDataFiles

2. Now we need to set all the external_expiration_time values for all the files containing real time (i.e. syslog) data. The "31536000" is the number of seconds for 365 days of retention. Fill in the number of seconds for your data's actual retention period.
mysql> UPDATE stDataFiles SET external_expiration_time=(time+31536000) WHERE filename LIKE "%rawdata%" AND external_expiration_time IS NULL;

3. This query pair is used to set all the external_expiration_time for all the file-based logs. Replace "31536000" with actual retention measured in seconds.
mysql> CREATE TEMPORARY TABLE aaa SELECT DISTINCT h.time, s.filename FROM logapprtrpt.stDataFiles s, logapprtrpt.FileTransferHistory h WHERE h.size>0 AND s.checksum=h.checksum AND s.entryType=2;

mysql> UPDATE stDataFiles SET external_expiration_time=((SELECT time FROM aaa WHERE aaa.filename=stDataFiles.filename)+31536000) WHERE filename LIKE "%filedata%" AND external_expiration_time IS NULL;

4. Drop the temporary table created from the first query in the previous step:
mysql> DROP TABLE aaa;