Record and timestamp problem in audit.log

MySQL & MariaDB Percona Server for MySQL 8.0
1

Hello everyone,

we use audit_log_plugin from Percona (Percona XtraDB Cluster (GPL), Release rel15, Revision 8638bb0, WSREP version 26.4.3 ) + innodb_version | 8.0.25-15 the plugin is working.
I have problem , record time is not valid. record and timestamp is three hours behind system clock.
I am sending the audit log content to qradar using syslog. I need one of two solutions here. the first is to remove the record and timestamp from the Auditlog. the second is to ensure that the times are seen correctly in the auditlog.

{“audit_record”:{“name”:“Query”,“record”:“412200305_2022-03-30T07:42:03”,“timestamp”:“2022-03-30T07:58:47Z” 


root@mysql8:date
Wed 30 Mar 2022 10:59:42 AM +03

I request your information about this problem.

Best regards.

1 Like
2

@bthnklc Can you do SELECT NOW() and compare that timestamp to the OS and audit log entries? Have you also checked the OS timezone compared to MySQL’s timezone setting? If every entry is always 3 hrs off, then I suspect you have some TZ-config-related issue.

1 Like
3

Hi @matthewb , thank for your reply.
The timezone I specified on the operating system and database is used. There is no historical problem in the database.“Time zone: Europe/Istanbul (+03, +0300)”
Below are the timezone outputs related to the problem. I haven’t been able to make any progress on what to do about it. :frowning:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1135
Server version: 8.0.25-15.1 Percona XtraDB Cluster (GPL), Release rel15, Revision 8638bb0, WSREP version 26.4.3

Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> SELECT NOW();
±--------------------+
| NOW() |
±--------------------+
| 2022-03-30 19:29:15 |
±--------------------+
1 row in set (0.00 sec)

mysql> show variables like ‘%zone%’;
±-----------------±-------+
| Variable_name | Value |
±-----------------±-------+
| system_time_zone | +03 |
| time_zone | SYSTEM |
±-----------------±-------+
2 rows in set (0.01 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
±-------------------±--------------------+
| @@global.time_zone | @@session.time_zone |
±-------------------±--------------------+
| SYSTEM | SYSTEM |
±-------------------±--------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
root@mysql8-03:~# timedatectl
Local time: Wed 2022-03-30 19:30:27 +03
Universal time: Wed 2022-03-30 16:30:27 UTC
RTC time: Wed 2022-03-30 16:30:27
Time zone: Europe/Istanbul (+03, +0300)
System clock synchronized: yes
NTP service: n/a
RTC in local TZ: no
root@mysql8-03:~# date
Wed 30 Mar 2022 07:31:03 PM +03

1 Like
4

Hi @matthewb ,

It look likes a issue. Percona has any plan about this issue?
https://bugs.mysql.com/bug.php?id=84961

Thanks for your time.

2 Likes
5

According to the bug you provided, it is not an issue/bug. Timestamp information in the audit log is always logged in UTC. The manual states this. The bug you linked is a feature request to have the audit timestamp be timezone sensitive. For now, the solution seems to have your qradar parse the timestamp in UTC and convert it on import.

3 Likes
6

Thanks for your information.

1 Like