Broadcom API Gateway - MySQL Audits Queries

Products

• CA API Gateway

Issue/Introduction

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

Environment

API Gateway 10.x

Resolution

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(*), a.name 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;