API Gateway: Managing audits in the database with the audit_purge.sh maintenance script.
Article ID: 42480
Updated On:
Products
Issue/Introduction
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:
- Any audit record older than a certain configurable age (in days).
- Any audit record that is of lower severity than SEVERE.
Environment
Gateway 9.x 10.x 11.x
Component: APIGTW
Resolution
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:
- Open the script in a text editor.
- Navigate to the line containing "Configurable settings"
- Modify the following variables:
| VALUE | DESCRIPTION | DEFAULT |
|---|---|---|
| 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 |
- 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.
- Download the file attached to this article (audit_purge.sh) to a workstation.
- Upload the script to the Gateway appliance via SFTP or SCP as the ssgconfig user.
- Move the script from the ssgconfig user's home directory to the desired location.
- Make it executable (`chmod u+x audit_purge.sh`)
- Run the script manually or configure it to run from crond with the desired options.
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
Additional Information
Using stored password
To use stored password and avoid plaintext password in the purge script, try following steps,
2. Publish the attached policy getPwd-1.0.xml, ensure the resolution path is /getPwd
3. In audit_purge.sh
DBPWD="7layer"
un-comment the line:
#DBPWD=`/usr/bin/wget -O- -q --no-check-certificate https://localhost:8443/getPwd?p=dbadminpwd`
Cron job example
(Gateway 9.x) To edit cron job,
crontab -e
0 23 * * * /opt/SecureSpan/Appliance/bin/audit_purge.sh -v -p > /tmp/audit_purge.log
(Gateway 10.x and 11.x) To edit cron job,
crontab -e
0 23 * * * /opt/SecureSpan/Appliance/bin/audit_purge_gw11.sh -v -p > /tmp/audit_purge_gw11.log
It runs audit purge daily at midnight(11pm).
NOTE:
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)
Attachments:
- audi_purge_getPwd-1.0.zip (Gateway 9.x and MySQL 5.x) (Note that this file is also applicable for Gateway 11.x)
- audit_purge_gw11.zip (Gateway 10.x / 11.x and MySQL 8.0)
Optional purge ALL audit records - truncate/analyze audit tables
# mysql
mysql> use ssg
mysql>
SET FOREIGN_KEY_CHECKS = 0;
truncate table audit_admin;
truncate table audit_detail;
truncate table audit_detail_params;
truncate table audit_main;
truncate table audit_message;
truncate table audit_system;
SET FOREIGN_KEY_CHECKS = 1;
analyze table audit_admin;
analyze table audit_detail;
analyze table audit_detail_params;
analyze table audit_main;
analyze table audit_message;
analyze table audit_system;
Attachments
Feedback
