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

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

book

Article ID: 42480

calendar_today

Updated On:

Products

CA API Gateway

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:

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

Environment

Release:
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:

  1. Open the script in a text editor.
  2. Navigate to the line containing "Configurable settings"
  3. 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
  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.

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,

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:
DBPWD="7layer"
 
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

(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) To edit cron job,
crontab -e
 
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).
 
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)
  • audit_purge_gw10.zip (Gateway 10.x and MySQL 8.0)

Attachments

1644842086928__audit_purge_gw10.zip get_app
1558534487429TEC0000001279.zip get_app