MariaDB audit

[Juju-fr34 (Juju)]

Hi everyone.

I'm trying to index my mariadb servers audit logs but I'm facing a problem. 90% off my logs are correctly indexed but some of them don't. It is like the produced csv is incorrect and message filed is empty.

Example of a malformed message :

May 25 14:30:20 Logstash-01 logstash[25643]: [2021-05-25T14:30:20,883][WARN ][logstash.filters.csv ][main][35cad0b777b2f15697ee7b33dcadaeae58eba0d321aa516f34a9240ad5e2085] Error parsing csv {:field=>"message", :source=>" MariaDB-Divers.local;eai_form;10.10.10.10;22331;70880458;QUERY;formation_infocentreForm;\"DELETE FROM formation_infocentreForm.par_c_echean_projetWHEREnumeroProjet = \"234\"\";0", :exception=>#<CSV::MalformedCSVError: Missing or stray quote in line 1>}

Her is my filter, partially created from informations of this topic : [Logstash csv filter plugin, with mariadb audit plugin logs - #3 by fabrizio73]

 if [program] == "mysql-server_auditing" {
     mutate {
       add_field => { "log_type" => "Audit_DB" }
       add_field => { "[@metadata][target_index]" => "audit-mariadb-%{+YYYY.MM.DD}" }
       gsub => [ "message", "(?<!\\)'", '"' ]
       gsub => [ 'message', ",", ";"]
     }

    csv {
        columns => ["serverhost", "username", "client_host", "connectionid", "queryid", "operation", "database", "object", "retcode"]
        separator => ";"
        convert => {
          "connectionid" => "integer"
          "queryid" => "integer"
          "retcode" => "integer"
        }
    }

Does someone have an idea of what is appening ?
Does anybody have succesfully index mariaDB audit logs ?

Thanks for your help
Juju

[Badger]

@Juju-fr34

MariaDB-Divers.local;eai_form;10.10.10.10;22331;70880458;QUERY;formation_infocentreForm;\"DELETE FROM formation_infocentreForm. par_c_echean_projet WHERE numeroProjet = \"234\"\";0

OK, so you actual message contains

MariaDB-Divers.local;eai_form;10.10.10.10;22331;70880458;QUERY;formation_infocentreForm;"DELETE FROM formation_infocentreForm.par_c_echean_projetWHEREnumeroProjet = "234"";0

In a CSV field any double quotes inside a field have to replaced with two double quotes. So if the actual field is

DELETE FROM ... `WHERE` numeroProjet ` = "234"

in a CSV file it would have to be

"DELETE FROM ... `WHERE` numeroProjet ` = ""234"""

What you have is not a valid CSV file, so ruby cannot parse it.