5.2.1 mysqlauditgrep — Allows users to search the current or an archived audit log

This utility allows you to search the current or archived audit logs, allowing you to display data from the audit log file according to the defined search criterion. It also allows you to output the results in different formats, namely GRID (default), TAB, CSV, VERTICAL, and RAW (the original XML format).

This utility allows you to search and filter the returned audit log records by: users (--users), date and time ranges (--start-date and --end-date), SQL query types (--query-type), logged event and record types (--event-type), status (--status), and matching patterns (--pattern). Any of these search options can be combined and used together, with the retrieved records resulting from all passed in options being true.

The --pattern supports two types of pattern matching: standard SQL, used with the SQL LIKE operator (SQL patterns), and standard REGEXP (POSIX regular expression patterns).

This utility always requires an audit log file to be passed in, so the AUDIT_LOG_FILE argument is searched as a full path and file name for the audit log file. If not specified, a notification concerning this requirement will be printed. And if --format is passed in without search parameters, then all the records of the audit log are displayed in the specified format.

The --file-stats option is not considered a search criteria, and is used to display the file statistics of a specified audit log. Other search options will be ignored when the --file-stats option is used, except the --format option will continue to format the results accordingly.

To specify the format of the generated results, use one of the following values with the --format option:

  1. GRID (default)

    Display output in grid or table format like that of the mysql monitor.

  2. CSV

    Display output in comma-separated values format.

  3. VERTICAL

    Display output in single-column format like that of the \G command for the mysql monitor.

  4. RAW

    Display output results in the original raw format of the audit log records, which is written in XML.

Standard SQL Pattern Matching

The simple patterns defined by the SQL standard enables users to use two characters with special meanings: % (percent) matches zero or more characters, and _ (underscore) matches exactly one arbitrary character. In standard SQL, these types of patterns are used with the LIKE comparison operator, and they are case-insensitive by default. This utility assumes that they are case-insensitive.

For example:

For documentation about the standard SQL pattern matching syntax, see Pattern Matching.

REGEXP Pattern Matching (POSIX)

Standard REGEXP patterns are more powerful than the simple patterns defined in the SQL standard. A regular expression is a string of ordinary and special characters specified to match other strings. Unlike SQL Patterns, REGEXP patterns are case-sensitive. The REGEXP syntax defines the following characters with special meaning:

For example:

This is a brief overview of regular expressions that can be used to define this type of patterns. The full syntax is described in the Python "re" module docs, supporting the definition of much more complex pattern matching expression.

OPTIONS

mysqlauditgrep accepts the following command-line options:

NOTES

This utility is available as of MySQL Utilities 1.2.0.

This utility can only be applied to servers with the audit log plugin enabled. And the audit log plugin is available as of MySQL Server versions 5.5.28 and 5.6.10.

This utility requires the use of Python version 2.6 or higher, but does not support Python 3.

Single or double quote characters (respectively, ' or ") can be used around option values. In fact, quotes are required to set some options values correctly, such as values with whitespace. For example, to specify the event types Create DB and Drop DB for the --event-type option, the following syntax must be used: --event-type='Create DB,Drop DB' or --event-type="Create DB,Drop DB".

EXAMPLES

To display the audit log file statistics and output the results in CSV format, run the following command:


shell> mysqlauditgrep --file-stats --format=CSV /SERVER/data/audit.log

  #
  # Audit Log File Statistics:
  #
  File,Size,Created,Last Modified
  audit.log,9101,Thu Sep 27 13:33:11 2012,Thu Oct 11 17:40:35 2012

  #
  # Audit Log Startup Entries:
  #

  SERVER_ID,STARTUP_OPTIONS,NAME,TIMESTAMP,MYSQL_VERSION,OS_VERSION,VERSION
  1,/SERVER/sql/mysqld --defaults-file=/SERVER/my.cnf,Audit,2012-09-27T13:33:11,5.5.29-log,x86_64-Linux,1

To display the audit log entries of specific users, use the following command:


shell> mysqlauditgrep --users=tester1,tester2 /SERVER/data/audit.log

To display the audit log file statistics, run the following command:


shell> mysqlauditgrep --users=tester1,tester2 /SERVER/data/audit.log

  +---------+------------+----------+----------------------+----------------+------------+----------+------------+------------+----------------------------------+
  | STATUS  | SERVER_ID  | NAME     | TIMESTAMP            | CONNECTION_ID  | HOST       | USER     | PRIV_USER  | IP         | SQLTEXT                          |
  +---------+------------+----------+----------------------+----------------+------------+----------+------------+------------+----------------------------------+
  | 0       | 1          | Connect  | 2012-09-28T11:26:50  | 9              | localhost  | root     | tester1    | 127.0.0.1  | None                             |
  | 0       | 1          | Query    | 2012-09-28T11:26:50  | 9              | None       | root     | tester1    | None       | SET @@session.autocommit = OFF   |
  | 0       | 1          | Ping     | 2012-09-28T11:26:50  | 9              | None       | root     | tester1    | None       | None                             |
  | 0       | 1          | Query    | 2012-09-28T11:26:50  | 9              | None       | root     | tester1    | None       | SHOW VARIABLES LIKE 'READ_ONLY'  |
  | 0       | 1          | Query    | 2012-09-28T11:26:50  | 9              | None       | root     | tester1    | None       | COMMIT                           |
  | 0       | 1          | Ping     | 2012-09-28T11:26:50  | 9              | None       | root     | tester1    | None       | None                             |
  | 0       | 1          | Query    | 2012-09-28T11:26:50  | 9              | None       | root     | tester1    | None       | COMMIT                           |
  | 0       | 1          | Quit     | 2012-09-28T11:26:50  | 9              | None       | root     | tester1    | None       | None                             |
  | 0       | 1          | Connect  | 2012-10-10T15:55:55  | 11             | localhost  | tester2  | root       | 127.0.0.1  | None                             |
  | 0       | 1          | Query    | 2012-10-10T15:55:55  | 11             | None       | tester2  | root       | None       | select @@version_comment limit 1 |
  | 0       | 1          | Query    | 2012-10-10T15:56:10  | 11             | None       | tester2  | root       | None       | show databases                   |
  | 1046    | 1          | Query    | 2012-10-10T15:57:26  | 11             | None       | tester2  | root       | None       | show tables test                 |
  | 1046    | 1          | Query    | 2012-10-10T15:57:36  | 11             | None       | tester2  | root       | None       | show tables test                 |
  | 0       | 1          | Query    | 2012-10-10T15:57:51  | 11             | None       | tester2  | root       | None       | show tables in test              |
  | 0       | 1          | Quit     | 2012-10-10T15:57:59  | 11             | None       | tester2  | root       | None       | None                             |
  | 0       | 1          | Connect  | 2012-10-10T17:35:42  | 12             | localhost  | tester2  | root       | 127.0.0.1  | None                             |
  | 0       | 1          | Query    | 2012-10-10T17:35:42  | 12             | None       | tester2  | root       | None       | select @@version_comment limit 1 |
  | 0       | 1          | Quit     | 2012-10-10T17:47:22  | 12             | None       | tester2  | root       | None       | None                             |
  +---------+------------+----------+----------------------+----------------+------------+----------+------------+------------+----------------------------------+

To display the audit log entries for a specific date/time range, use the following command:


shell> mysqlauditgrep --start-date=2012-09-27T13:33:47 --end-date=2012-09-28 /SERVER/data/audit.log

  +---------+----------------------+--------+----------------+---------------------------------------------------------------------------+
  | STATUS  | TIMESTAMP            | NAME   | CONNECTION_ID  | SQLTEXT                                                                   |
  +---------+----------------------+--------+----------------+---------------------------------------------------------------------------+
  | 0       | 2012-09-27T13:33:47  | Ping   | 7              | None                                                                      |
  | 0       | 2012-09-27T13:33:47  | Query  | 7              | SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'  |
  | 0       | 2012-09-27T13:33:47  | Query  | 7              | COMMIT                                                                    |
  | 0       | 2012-09-27T13:34:48  | Quit   | 7              | None                                                                      |
  | 0       | 2012-09-27T13:34:48  | Quit   | 8              | None                                                                      |
  +---------+----------------------+--------+----------------+---------------------------------------------------------------------------+

To display the audit log entries matching a specific SQL LIKE pattern, use the following command:


shell> mysqlauditgrep --pattern="% = ___"; /SERVER/data/audit.log

  +---------+----------------------+--------+---------------------------------+----------------+
  | STATUS  | TIMESTAMP            | NAME   | SQLTEXT                         | CONNECTION_ID  |
  +---------+----------------------+--------+---------------------------------+----------------+
  | 0       | 2012-09-27T13:33:39  | Query  | SET @@session.autocommit = OFF  | 7              |
  | 0       | 2012-09-27T13:33:39  | Query  | SET @@session.autocommit = OFF  | 8              |
  | 0       | 2012-09-28T11:26:50  | Query  | SET @@session.autocommit = OFF  | 9              |
  | 0       | 2012-09-28T11:26:50  | Query  | SET @@session.autocommit = OFF  | 10             |
  +---------+----------------------+--------+---------------------------------+----------------+

To display the audit log entries matching a specific REGEXP pattern, use the following command:


shell> mysqlauditgrep --pattern=".* = ..." --regexp /SERVER/data/audit.log

  +---------+----------------------+--------+---------------------------------+----------------+
  | STATUS  | TIMESTAMP            | NAME   | SQLTEXT                         | CONNECTION_ID  |
  +---------+----------------------+--------+---------------------------------+----------------+
  | 0       | 2012-09-27T13:33:39  | Query  | SET @@session.autocommit = OFF  | 7              |
  | 0       | 2012-09-27T13:33:39  | Query  | SET @@session.autocommit = OFF  | 8              |
  | 0       | 2012-09-28T11:26:50  | Query  | SET @@session.autocommit = OFF  | 9              |
  | 0       | 2012-09-28T11:26:50  | Query  | SET @@session.autocommit = OFF  | 10             |
  +---------+----------------------+--------+---------------------------------+----------------+

To display the audit log entries of specific query types, use the following command:


shell> mysqlauditgrep --query-type=show,SET /SERVER/data/audit.log

  +---------+----------------------+--------+-------------------------------------------------+----------------+
  | STATUS  | TIMESTAMP            | NAME   | SQLTEXT                                         | CONNECTION_ID  |
  +---------+----------------------+--------+-------------------------------------------------+----------------+
  | 0       | 2012-09-27T13:33:39  | Query  | SET NAMES 'latin1' COLLATE 'latin1_swedish_ci'  | 7              |
  | 0       | 2012-09-27T13:33:39  | Query  | SET @@session.autocommit = OFF                  | 7              |
  | 0       | 2012-09-27T13:33:39  | Query  | SHOW VARIABLES LIKE 'READ_ONLY'                 | 7              |
  | 0       | 2012-09-27T13:33:39  | Query  | SHOW VARIABLES LIKE 'datadir'                   | 7              |
  | 0       | 2012-09-27T13:33:39  | Query  | SHOW VARIABLES LIKE 'basedir'                   | 7              |
  | 0       | 2012-09-27T13:33:39  | Query  | SET NAMES 'latin1' COLLATE 'latin1_swedish_ci'  | 8              |
  | 0       | 2012-09-27T13:33:39  | Query  | SET @@session.autocommit = OFF                  | 8              |
  | 0       | 2012-09-27T13:33:39  | Query  | SHOW VARIABLES LIKE 'READ_ONLY'                 | 8              |
  | 0       | 2012-09-27T13:33:39  | Query  | SHOW VARIABLES LIKE 'basedir'                   | 8              |
  | 0       | 2012-09-28T11:26:50  | Query  | SET NAMES 'latin1' COLLATE 'latin1_swedish_ci'  | 9              |
  | 0       | 2012-09-28T11:26:50  | Query  | SET @@session.autocommit = OFF                  | 9              |
  | 0       | 2012-09-28T11:26:50  | Query  | SHOW VARIABLES LIKE 'READ_ONLY'                 | 9              |
  | 0       | 2012-09-28T11:26:50  | Query  | SET NAMES 'latin1' COLLATE 'latin1_swedish_ci'  | 10             |
  | 0       | 2012-09-28T11:26:50  | Query  | SET @@session.autocommit = OFF                  | 10             |
  | 0       | 2012-09-28T11:26:50  | Query  | SHOW VARIABLES LIKE 'READ_ONLY'                 | 10             |
  | 0       | 2012-09-28T11:26:50  | Query  | SET @@GLOBAL.audit_log_flush = ON               | 10             |
  | 0       | 2012-09-28T11:26:50  | Query  | SHOW VARIABLES LIKE 'audit_log_policy'          | 10             |
  | 0       | 2012-09-28T11:26:50  | Query  | SHOW VARIABLES LIKE 'audit_log_rotate_on_size'  | 10             |
  | 0       | 2012-10-10T15:56:10  | Query  | show databases                                  | 11             |
  | 1046    | 2012-10-10T15:57:26  | Query  | show tables test                                | 11             |
  | 1046    | 2012-10-10T15:57:36  | Query  | show tables test                                | 11             |
  | 0       | 2012-10-10T15:57:51  | Query  | show tables in test                             | 11             |
  +---------+----------------------+--------+-------------------------------------------------+----------------+

To display the audit log entries of specific event types, use the following command:


shell> mysqlauditgrep --event-type="Ping,Connect" /SERVER/data/audit.log

  +---------+----------+----------------------+----------------+------------+---------+------------+------------+
  | STATUS  | NAME     | TIMESTAMP            | CONNECTION_ID  | HOST       | USER    | PRIV_USER  | IP         |
  +---------+----------+----------------------+----------------+------------+---------+------------+------------+
  | 0       | Connect  | 2012-09-27T13:33:39  | 7              | localhost  | root    | root       | 127.0.0.1  |
  | 0       | Ping     | 2012-09-27T13:33:39  | 7              | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-27T13:33:39  | 7              | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-27T13:33:39  | 7              | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-27T13:33:39  | 7              | None       | None    | None       | None       |
  | 0       | Connect  | 2012-09-27T13:33:39  | 8              | localhost  | root    | root       | 127.0.0.1  |
  | 0       | Ping     | 2012-09-27T13:33:39  | 8              | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-27T13:33:39  | 8              | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-27T13:33:47  | 7              | None       | None    | None       | None       |
  | 0       | Connect  | 2012-09-28T11:26:50  | 9              | localhost  | root    | tester     | 127.0.0.1  |
  | 0       | Ping     | 2012-09-28T11:26:50  | 9              | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-28T11:26:50  | 9              | None       | None    | None       | None       |
  | 0       | Connect  | 2012-09-28T11:26:50  | 10             | localhost  | root    | root       | 127.0.0.1  |
  | 0       | Ping     | 2012-09-28T11:26:50  | 10             | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-28T11:26:50  | 10             | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-28T11:26:50  | 10             | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-28T11:26:50  | 10             | None       | None    | None       | None       |
  | 0       | Ping     | 2012-09-28T11:26:50  | 10             | None       | None    | None       | None       |
  | 0       | Connect  | 2012-10-10T15:55:55  | 11             | localhost  | tester  | root       | 127.0.0.1  |
  | 0       | Connect  | 2012-10-10T17:35:42  | 12             | localhost  | tester  | root       | 127.0.0.1  |
  +---------+----------+----------------------+----------------+------------+---------+------------+------------+

To display the audit log entries with a specific status, use the following command:


shell> mysqlauditgrep --status=1100-1199,1046 /SERVER/data/audit.log

  +---------+----------------------+--------+---------------------------------------------------------------------+----------------+
  | STATUS  | TIMESTAMP            | NAME   | SQLTEXT                                                             | CONNECTION_ID  |
  +---------+----------------------+--------+---------------------------------------------------------------------+----------------+
  | 1046    | 2012-10-10T15:57:26  | Query  | show tables test                                                    | 11             |
  | 1046    | 2012-10-10T15:57:36  | Query  | show tables test                                                    | 11             |
  | 1146    | 2012-10-10T17:44:55  | Query  | select * from teste.employees where salary > 500 and salary < 1000  | 12             |
  | 1046    | 2012-10-10T17:47:17  | Query  | select * from test_encoding where value = '<>"&'                    | 12             |
  +---------+----------------------+--------+---------------------------------------------------------------------+----------------+

Note: You can view all successful commands with --status=0 and all unsuccessful ones with --status=1-9999.

To display the audit log entries matching several search criteria, use the following command:


shell> mysqlauditgrep --users=root --start-date=0 --end-date=2012-10-10 --event-type=Query \
     --query-type=SET --status=0 --pattern="%audit_log%" /SERVER/data/audit.log

  +---------+------------+--------+----------------------+----------------+-------+------------+------------------------------------+
  | STATUS  | SERVER_ID  | NAME   | TIMESTAMP            | CONNECTION_ID  | USER  | PRIV_USER  | SQLTEXT                            |
  +---------+------------+--------+----------------------+----------------+-------+------------+------------------------------------+
  | 0       | 1          | Query  | 2012-09-28T11:26:50  | 10             | root  | root       | SET @@GLOBAL.audit_log_flush = ON  |
  +---------+------------+--------+----------------------+----------------+-------+------------+------------------------------------+

PERMISSIONS REQUIRED

The user must have permissions to read the audit log on disk.