API Gateway: Managing audits in the database with the audit_purge.sh maintenance script.


Article ID: 42480


Updated On:


CA API Gateway


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:

  1. Any audit record older than a certain configurable age (in days).
  2. Any audit record that is of lower severity than SEVERE.


Component: APIGTW


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:

  1. Open the script in a text editor.
  2. Navigate to the line containing "Configurable settings"
  3. Modify the following variables:
AGEThe maximum allowable age of an audit record10 days
LIMITThe maximum number of audit records to delete in a single query5000
PURGEBoolean; Sets whether the audits records exceeding the limits are purgedYES
DBThe name of the database object containing audit recordsssg
DBHOSTThe fully qualified domain name of the database hostlocalhost
DBUSERThe unprivileged database user namegateway
DBPWDThe unprivileged database user password7layer
  1. Save the file and exit


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.

  1. Download the file attached to this article (audit_purge.sh) to a workstation.
  2. Upload the script to the Gateway appliance via SFTP or SCP as the ssgconfig user.
  3. Move the script from the ssgconfig user's home directory to the desired location.
  4. Make it executable (`chmod u+x audit_purge.sh`)
  5. Run the script manually or configure it to run from crond with the desired options.

NOTE: The available options can be seen by running the script with the -h option

Additional Information

Using stored password

To use stored password and avoid plaintext password in the purge script, try following steps,

1. Create stored password MySQLAdminPwd in policy manager, ensure "Permit use via context variable reference" option is checked
2. Publish the attached policy getPwd-1.0.xml, ensure the resolution path is /getPwd
3. In audit_purge.sh
comment out the line:
un-comment the line:
#DBPWD=`/usr/bin/wget -O- -q --no-check-certificate https://localhost:8443/getPwd?p=dbadminpwd`
4. (Optional )to test if the getPwd service and stored password is configured properly, run the command on gateway server:
/usr/bin/wget -O- -q --no-check-certificate https://localhost:8443/getPwd?p=dbadminpwd
It should show the password of the stored password MySQLAdminPwd


Cron job example

To edit cron job,
crontab -e
0 23 * * * /opt/SecureSpan/Appliance/bin/audit_purge.sh -v -p > /tmp/audit_purge.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)




1558534487429TEC0000001279.zip get_app