Configuring and installing the audit record maintenance script

Document ID : KB000042480
Last Modified Date : 16/08/2018
Show Technical Document Details

By default, the CA 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, CA Technologies prescribes minimizing the number and size of audit records that are maintained within the Gateway database for performance and availability reasons. CA Technologies 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.

    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 ( 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`)
    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
    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/ -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)



    File Attachments: