Audit records in ssg db

Document ID : KB000010253
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

It can be useful to obtain:-

  • The size of the DBs in the MySQL instance
  • The amount of data per table
  • The newest and oldest audit record
  • The number of audit records it contains
  • The date range of these records

This information can be then be used to determine how to manage the removal of older records. 

Environment:
APIM Gateway 7.x - 9.x, RHEL, Mysql
Instructions:

To run an SQL statement log in as root, and either place the sql statement on a mysql command, eg:-

mysql ssg -e "SELECT count(*) FROM audit_main"

or launch mysql session via the command:-

mysql ssg

This will display a mysql prompt where you can type the statements directly.  The statements you can use are listed below.

The size of the DBs in the MySQL instance:-

SELECT table_schema, sum((data_length+index_length)/1024/1024) AS MB FROM information_schema.tables GROUP BY 1;

The amount of data per table:-

SELECT  table_schema AS`Database`,  table_name AS `Table`,  round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`  FROM information_schema.TABLES  ORDER BY (data_length + index_length) DESC; 

The newest and oldest audit record:-

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

The number of audit records it contains:-

SELECT count(*) FROM audit_main;

The date range of these records

SELECT x.From, x.To, x.NumberOfAudtis FROM ( SELECT time AS intervals, from_unixtime(time/1000) AS 'From', from_unixtime(time/1000 + 2629743) AS 'To', count(*) AS 'NumberOfAudtis' FROM audit_main GROUP BY intervals div 2629743000 )x;