By default, the Layer7 API Gateway will store audit records in the local MySQL database for ease of use and simple storage. This allows the Gateway to quickly access audit records from the local database for review by administrators and operators. These audit records will consume an increasingly large amount of database tablespace unless the quantity of audit records generated is adjusted appropriate for the environment.
Traditionally, Broadcom recommended minimizing the number and size of audit records that are maintained within the Gateway database for performance and availability reasons. Broadcom understands that some of our customers operate under strict regulatory requirements that mandate a certain quantity or quality of audit records be maintained for a certain period of time.
A script was authored by the CA API Gateway team that will manage audit records based on certain criteria. Specifically:
The audit record maintenance script can be configured to use a pre-determined series of settings each time it is executed. For example, the MySQL server connection settings and audit age restrictions are set within this script and are leveraged each time the script is run. To configure these values:
|AGE||The maximum allowable age of an audit record||10 days|
|LIMIT||The maximum number of audit records to delete in a single query||5000|
|PURGE||Boolean; Sets whether the audits records exceeding the limits are purged||NO|
|DB||The name of the database object containing audit records||ssg|
|DBHOST||The fully qualified domain name of the database host||localhost|
|DBUSER||The unprivileged database user name||gateway|
|DBPWD||The unprivileged database user password||7layer|
This script should be stored in a central location. Traditionally, the script is located in /usr/local/bin/ or /opt/SecureSpan/Appliance/bin/. The invocation of this script is typically handled by the Gateway appliance's default scheduled task handler--crond. It is expected that this script will be configured to run via crontab. If you need assistance with configuring the Gateway appliance to run this script via cron then please open a new Support request.
This script is intended to run while replication is functioning and its intention is to delete items from the database. As such, it is imperative that the audit record maintenance script be run on only one database node in the cluster. If the script is run on multiple database nodes at the same time then each database node will attempt to delete the same audit record. This race condition will result in a failure in MySQL replication.
To run the script command line and purge you should run,
# cd /opt/SecureSpan/Appliance/bin
#./audit_purge.sh -v -p
-v is optional its verbose output.
-p is required if you want actual records deleted. Either you must specify PURGE=YES in the script to ensure its always purging OR -p must always be on the command line for actual deletion of records.
NOTE: The available options can be seen by running the script with the -h option
To use stored password and avoid plaintext password in the purge script, try following steps,
(Gateway 9.x) To edit cron job,
0 23 * * * /opt/SecureSpan/Appliance/bin/audit_purge.sh -v -p > /tmp/audit_purge.log
(Gateway 10.x) To edit cron job,
0 23 * * * /opt/SecureSpan/Appliance/bin/audit_purge_gw10.sh -v -p > /tmp/audit_purge_gw10.log
It runs audit purge daily at midnight(11pm).
1. database batch jobs will always impact the DB performance, so the cron job should be always scheduled at non-business hours.
2. the more frequent of the purge job, the time it needs will be less for each time, so daily job should be a good idea, or weekly job at weekend.
3. NOTE, the first time of purge could take long time, hence the gateway might look like outage, recommend to run first purge manually at a scheduled time window.
(by default, it only purges 5000 records each time, so specify the -l option for the first time of purge. Or run it few times)