How to read the MySQL Audit.log file in a SQL table?

Posted on

Question :

I tried to read the Audit.log file of the MySQL Enterprise using a table or other more friendly format and easy to access and understand, but the xml format with which it is written has an irregular structure:

<AUDIT_RECORD>
   <TIMESTAMP>2019-02-17T12:53:23 UTC</TIMESTAMP>
   <RECORD_ID>1_2019-02-17T12:53:23</RECORD_ID>
   <NAME>Audit</NAME>
   <SERVER_ID>1</SERVER_ID>
   <VERSION>1</VERSION>
   <STARTUP_OPTIONS>./mysqld --basedir=/MYSQL_HOME/mysql</STARTUP_OPTIONS>
   <OS_VERSION>x86_64-linux-glibc2.12</OS_VERSION>
   <MYSQL_VERSION>5.6.38-enterprise-commercial-advanced-log</MYSQL_VERSION>
</AUDIT_RECORD>

<AUDIT_RECORD>
   <TIMESTAMP>2019-02-19T02:22:23 UTC</TIMESTAMP>
   <RECORD_ID>2_2019-02-17T12:53:23</RECORD_ID>
   <NAME>Connect</NAME>
   <CONNECTION_ID>460432</CONNECTION_ID>
   <STATUS>0</STATUS>
   <STATUS_CODE>0</STATUS_CODE>
   <USER>nbryan</USER>
   <OS_LOGIN/>
   <HOST/>
   <IP>172.17.3.150</IP>
   <COMMAND_CLASS>connect</COMMAND_CLASS>
   <PRIV_USER>nbryan</PRIV_USER>
   <PROXY_USER/>
   <DB/>
</AUDIT_RECORD>

<AUDIT_RECORD>
   <TIMESTAMP>2019-02-19T02:22:23 UTC</TIMESTAMP>
   <RECORD_ID>3_2019-02-17T12:53:23</RECORD_ID>
   <NAME>Query</NAME>
   <CONNECTION_ID>460432</CONNECTION_ID>
   <STATUS>0</STATUS>
   <STATUS_CODE>0</STATUS_CODE>
   <USER>flopez[nbryan] @  [172.17.3.150]</USER>
   <OS_LOGIN/>
   <HOST/>
   <IP>172.17.3.150</IP>
   <COMMAND_CLASS>set_option</COMMAND_CLASS>
   <SQLTEXT>/*!40101 set @@session.wait_timeout=28800 */</SQLTEXT>
</AUDIT_RECORD>

As you can see, the same tags are not always generated, so I have not been able to format it to a table from the SQL Server (there is a lot of information on how to read xml formats using sql). I wanted to know if there is another way, maybe from the same MySQL, although it is very difficult to find something of this manager in relation to other solutions.

Any help or idea would be very helpful.

Thank you.

Answer :

In this page you find information about Audit.log:

https://mysqlserverteam.com/audit-logs-json-format-logging/

Audit Log File Reading
Audit Log can store log files as the XML or JSON format, depending on the specified output format. When the JSON logging is enabled, we can read events using the provided audit_log_read() UDF.

  { 
  "timestamp": "2018-05-24 23:15:07",
  "id": 0,
  "class": "connection",
  "event": "connect",
  "connection_id": 12,
  "account": { "user": "user",
               "host": "localhost" },
  "login": { "user": "user",
             "os": "",
             "ip": "::1",
             "proxy": "" },
  "connection_data": { "connection_type": "tcp/ip",
                       "status": 0,
                       "db": "bank_db" } }

Leave a Reply

Your email address will not be published. Required fields are marked *