How to get audit log filter output which we got in legacy audit plugin. Please check below example

MySQL & MariaDB
1

Hello Team,

 Anyone share explanation about how we can get audit log filter (new component based) output like Legacy audit plugin ?

Please share audit filter definition if possible.

– Legacy Plugin Output before/ and in Percona-8.0

{ “timestamp”: “2023-03-29 11:17:05”, “id”: 2, “class”: “general”, “event”: “log”, “connection_id”: 11, “account”: {“user”: “root[root] @ localhost ”, “host”: “localhost”}, “login”: {“user”: “root[root] @ localhost ”, “os”: “”, “ip”: “”, “proxy”: “”}, “general_data”: { “command”: “Query”, “sql_command”: “create_table”, “query”: “CREATE TABLE t1 (c1 INT)”, “status”: 0 } }

– New audit log filter
Its a class based, how to get user@host & sql query in same JSON block ?

Thanks

2

Hi @krunal

Our documentation for Percona Server for MySQL 8.0 seems to be missing the page on how to write an audit log filter definition.

You can find the steps to create an audit log filter in the documentation for MySQL 8.4, which is also applicable for MySQL 8.0: Write audit_log_filter definitons - Percona Server for MySQL

To answer your question

Its a class based, how to get user@host & sql query in same JSON block ?

You can use a filter definition like this to log SQL commands against the server

{
    "filter": {
        "log": false,
        "class" : [
            {"name": "general"},
            {"name": "table_access"}
        ]
    }
}

To create the filter and apply it to all users

SELECT audit_log_filter_set_filter('log_sql_commands', '{
    "filter": {
        "log": false,
        "class" : [
            {"name": "general"},
            {"name": "table_access"}
        ]
    }
}');
SELECT audit_log_filter_set_user('%', 'log_sql_commands');
SELECT audit_log_filter_flush();

Please let me know if you have any other questions.

Best,

Hieu