How to change the timestamp format of percona mysql audit logs

Question

I have a RHEL linux server where I have installed mysql percona and set the auditing.

The Queries received in the audit record has timestamp in below format.

{"audit_record":{"name":"Query","record":"179295580_2024-10-03T10:56:38","timestamp":"2024-10-03T10:57:54 UTC","command_class":"drop_table","connection_id":"2","status":0,"sqltext":"drop table DPT","user":"abc[abc] @ localhost []","host":"localhost","os_user":"","ip":"","db":""}}

In the above record , timestamp has UTC. ""timestamp":"2024-10-03T10:57:54 UTC"," And I want it in this format: "timestamp":"2024-10-03T10:57:54Z". Basically I want to replace UTC with Z.

I tried changing the timezone of the server , but its no help. Please help me with this.

Thankyou

Answer 1

Modifying Percona's audit plugin is tricky. You'd need to dig into the C++ source and alter the timestamp formatting logic. Doable, but a pain.

A less invasive approach could be to use a log processing tool like Logstash or fluentd to transform the timestamps on ingestion. Something like:

filter {
  date {
    match => [ "timestamp", "ISO8601" ]
    target => "timestamp"
    timezone => "UTC"
  }
}

This would parse the existing timestamp and reformat it to your desired ISO8601 format with the Z suffix.

That said, if you're hitting limitations with Percona's audit capabilities, it might be time to look at more flexible solutions. Check out hoop.dev to get full control over stuff like this - custom audit log formats, real-time transformations, etc. Could be worth a look if you're dealing with complex compliance requirements.