Layer7 API Management

  • 1.  How can I automate the purge of old audit events?

    Posted Oct 20, 2017 03:53 PM

    I read about the FTP Audit Archiver feature, but it implies that we want to keep the logs somewhere else, requires an external FTP server... and doesn't work with an embedded database.

     

    We have a dev cluster where we often have to manually delete the audit event logs because the database is full. Is there a way that we can automate this without using the FTP Archiver or switching to a MySQL database?

     

    We are using API Gateway 9.1



  • 2.  Re: How can I automate the purge of old audit events?
    Best Answer

    Broadcom Employee
    Posted Oct 23, 2017 08:56 PM

    Dear ygirouard_stm ,

    The KB article below will help you to install an audit purge script for automatically audit removal,

    Configuring and installing the audit record maintenance script 

     

    Regards,

    Mark



  • 3.  Re: How can I automate the purge of old audit events?

    Posted Oct 24, 2017 08:57 AM

    That's perfect! Thank you!



  • 4.  Re: How can I automate the purge of old audit events?

    Broadcom Employee
    Posted Oct 30, 2017 06:07 PM

    Just to remind that, for the first  time of audit purge, you may need to manually clean up the audit records, as the audit records may already accumulate to a big number, "Delete" SQL is costly and could take a long  time (hours or even days). Ask your DBA to truncate table (in seconds, but cannot keep any data), or rename table + insert (to keep x days data, should be finished in few minutes), after that, running audit_purge should have no any impact on gateway.



  • 5.  Re: How can I automate the purge of old audit events?

    Broadcom Employee
    Posted Feb 18, 2019 03:49 AM

    Hi,

    I find the corresponding kb article a bit misleading. I think main purpose of admins would like to get rid of older audits is reclaiming disk space, but default configuration of MySQL in Gateway uses InnoDB with innodb_file_per_table set to off. This causes a drop and re-create would not shrink ibdata file size but increase it.

    I think it could be stated in the article, that, if the motivation of the audit purge action is reclaiming disk space, mysqldump is the only option, at least for appliance gateways.

     

    Further information:

    MySQL :: MySQL 5.5 Reference Manual :: 14.15.5 Reclaiming Disk Space with TRUNCATE TABLE 



  • 6.  Re: How can I automate the purge of old audit events?

    Posted Feb 18, 2019 08:15 AM

    Assuming:

    - you don't have easy access to underlying system (such as Gateway in container and don't want to re-image)

    - optionally have all innodb tables on a per file basis (innodb_file_per_table=1)

    - you have a JDBC connection to local ssg database

     

    Create on first node of API Gateway cluster:

     

    1) a Web Service on "/common/ClearAudit" with:

     

    line 16: Date assigment:

    Line 17: 1st query:

    Line 22: 2nd query:

    Line 25: 3rd query:

     

     

    Add some security before, whitelisting IP address, basic creds, etc. so that this API is not called from unauthorized people.

     

    2) a policy-backed service "Audit Scheduled Clear Old Entries"  (background task) with:

     

    3) Schedule task

     

    run on one-node, 1st member of cluster, let's say one per day at 3:15am

     



  • 7.  Re: How can I automate the purge of old audit events?

    Posted Feb 19, 2019 01:03 PM

    Can you share this policy?  I am setting up gateway container using K8/s and need purge policy to be run on MySQL container.  

     

    -Prem Khedekar

    General Motors.



  • 8.  Re: How can I automate the purge of old audit events?

    Posted Feb 20, 2019 03:51 AM
      |   view attached

    I've cleaned it and only left a "only allow access from 127.0.0.1" as per access restrictions, feel free to modify.

    Won't attach other policy shown in my previous email, obvious one.

    Attachment(s)



  • 9.  Re: How can I automate the purge of old audit events?

    Broadcom Employee
    Posted Feb 25, 2019 08:06 AM

    So is it safe to turn on innodb_file_per_table on?

    If so, is there a downside of file per table on compared to off, which is the default configuration?



  • 10.  Re: How can I automate the purge of old audit events?

    Posted Feb 25, 2019 08:21 AM

    MySQL InnoDB - innodb_file_per_table cons? - Server Fault 

     

    FIY, innodb_file_per_table optioin is enabled as default in MariaDB (which will be soon supported ?  <--- subliminal remark for devs)