1

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.

1 Answer 1

0

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" } }
1
  • Please consider adding the salient points from your link to the answer itself, lest it becomes useless when the link goes stale.
    – mustaccio
    Mar 7, 2019 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.