search cancel

Broadcom API Gateway - MySQL Audits Queries


Article ID: 111628


Updated On:


STARTER PACK-7 CA Rapid App Security CA API Gateway


If we sink audit records into the Gateway's default DB, it can be useful to execute some SQL queries in order to verify what has been stored in time. 
Such information can then dictate how we decide to run maintenance against the Audit tables like, for example, deciding what/how many records to purge out as not more valuable to be kept


API Gateway 10.x


NOTE: Following queries are intended to be ran within MySQL Client console, at the SSG database. 
So connect to it with: # mysql -u root -p ssg 

  • Query to check what tables are filling up the Database (to be executed within MySQL console)

select table_name, round(((data_length + index_length) / (1024*1024)),2) as size_in_megs from information_schema.tables where table_schema = "ssg" order by size_in_megs; 

  • Check the total number of stored audit records:

SELECT count(*) FROM audit_main;

  • Check the newest and oldest audit record:

SELECT from_unixtime(min(time)/1000) as 'Oldest Record', from_unixtime(max(time)/1000) AS 'Newest Record' FROM audit_main;

  • Check audits generated PER SERVICE in a FIXED period of TIME (replace the two YEAR-MONTH-DAY with a valid starting and end date, for example 2018-04-01 and 2018-04-16). This will give you an indication of average ibdata grown based on the number of audit the services generate

select count(*), from audit_main a, audit_detail b where a.goid=b.audit_goid and a.time between unix_timestamp('YEAR-MONTH-DAY')*1000 and unix_timestamp('YEAR-MONTH-DAY')*1000 group by name;