# Logstash csv filter plugin, with mariadb audit plugin logs **URL:** \ **Category:** Logstash\ **Created:** [July 28, 2020, 5:24pm UTC](https://discuss.elastic.co/t/logstash-csv-filter-plugin-with-mariadb-audit-plugin-logs/242953 "2020-07-28T17:24:50Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![fabrizio73](https://avatars.discourse-cdn.com/v4/letter/f/ecccb3/32.png) [@fabrizio73](https://discuss.elastic.co/u/fabrizio73)\ **Post date:** [July 28, 2020, 5:24pm UTC](https://discuss.elastic.co/t/logstash-csv-filter-plugin-with-mariadb-audit-plugin-logs/242953/1 "2020-07-28T17:24:50Z")
Hi, we are develeping our log management ELK Platform, and we managed almost everything we need in order to get logs with filebeat from percona, mariadb, ssh and SAMBA4 ADC access. The only issue we are facing is that logs from mariadb audit plugin are available only in csv format, and only with comma separated fields, and the problem happens when we log queries, connects and disconnects are perfect, but queries that contains commas inside their VALUE statement , for example (FIELD1,FIELD2) VALUES (data1,data2) , cause logstash csv filter plugin to break the query itself into multiple columns, because each "comma" is seen as a field separator, we have tried with mutate plugin, it modifies the record, but not the behaviour , we cannot succeed in managing correctly the queries Thanks in advance ---
**Author:** ![kelk](https://avatars.discourse-cdn.com/v4/letter/k/13edae/32.png) [@kelk](https://discuss.elastic.co/u/kelk)\ **Post date:** [July 29, 2020, 7:59am UTC](https://discuss.elastic.co/t/logstash-csv-filter-plugin-with-mariadb-audit-plugin-logs/242953/2 "2020-07-29T07:59:34Z")
can you please provide example data/payload & your configuration please. Otherwise it will be just guess on how it looks like etc ---
**Author:** ![fabrizio73](https://avatars.discourse-cdn.com/v4/letter/f/ecccb3/32.png) [@fabrizio73](https://discuss.elastic.co/u/fabrizio73)\ **Post date:** [July 29, 2020, 8:31am UTC](https://discuss.elastic.co/t/logstash-csv-filter-plugin-with-mariadb-audit-plugin-logs/242953/3 "2020-07-29T08:31:21Z")
Hi, here is our configuration and how data are INGESTED, in this case I have used mutate BEFORE the filter to convert commas with semicolon, , I've tried also AFTER the filter, obtaining the "message" filed apparently correct, but the object is interupted and broken in multiple columns exactly like this. filter { mutate { gsub =\> [ 'message', ",", ";" ] } csv { separator =\> ";" columns =\> ["timestamp","serverhost","username","host","connectionid","queryid","operation","database","object","retcode"] } ``` } ``` { "\_index": "logstash-2020.07.28", "\_type": "doc", "\_id": "\_WiplXMBt5BZnqx7LsLv", "\_version": 1, "\_score": null, "\_source": { "beat": { "hostname": "mbc-db-vodafone", "version": "6.3.2", "name": "mbc-db-vodafone" }, "connectionid": "464232271", "column28": " \'FISSI E MOBILI NAZIONALI SENZA LIMITI\'", "column41": " \'NO\')'", "column27": " \'13\'", "column29": " \'-\'", "source": "/var/lib/mysql/server\_audit.log", "input": { "type": "log" }, "column19": " `iccid_sim`", "column33": " \'-\'", "column34": " \'\'", "column21": " `piano_telefonico_sim`", "@timestamp": "2020-07-28T13:43:23.779Z", "column11": " `id_promo`", "offset": 3792527, "prospector": { "type": "log" }, "message": "20200728 15:43:23;mbc-db-vodafone;root;192.168.2.165;464232271;9113436875;QUERY;vodafone\_en;'INSERT INTO `tb_promo_attivate` (`id_customer`; `id_phone_calls`; `id_promo`; `descrizione_promo`; `costo_promo`; `id_agent_crm`; `nome_agent`; `telefono_sconto`; `tipo_scelta_sim`; `numero_sim`; `iccid_sim`; `vecchio_gestore_sim`; `piano_telefonico_sim`; `data_inserimento`; `campagna`;`tipo`; `scelta_rete_sicura`)\n\t\tVALUES (\'13285100\'; \'0\'; \'13\'; \'FISSI E MOBILI NAZIONALI SENZA LIMITI\'; \'-\'; \'83\'; \'Ledda Marcellino\'; \'\'; \'-\'; \'\'; \'\'; \'-\'; \'-\'; CURRENT\_TIMESTAMP; \'Richiami su Numeri Uscenti C2C 4u Italia\';\'DSL\'; \'NO\')';0", "username": "root", "column16": " `telefono_sconto`", "column15": " `nome_agent`", "column14": " `id_agent_crm`", "column26": " \'0\'", "column13": " `costo_promo`", "serverhost": "mbc-db-vodafone", "column38": " CURRENT\_TIMESTAMP", "tags": [ "beats\_input\_codec\_plain\_applied" ], "retcode": " `id_phone_calls`", "object": "'INSERT INTO `tb_promo_attivate` (`id_customer`", "column23": " `campagna`", "@version": "1", "column31": " \'XXXXXXXXXXX\'", "queryid": "9113436875", "column32": " \'\'", "operation": "QUERY", "column25": " `scelta_rete_sicura`)\n\t\tVALUES (\'13285100\'", "column40": "\'DSL\'", "column37": " \'-\'", "database": "vodafone\_en", "column22": " `data_inserimento`", "column35": " \'\'", "column20": " `vecchio_gestore_sim`", "column24": "`tipo`", "column30": " \'83\'", "column42": "0", "host": "192.168.2.165", "timestamp": "20200728 15:43:23", "column39": " \'Richiami su Numeri Uscenti XXXXXXXX\'", "column17": " `tipo_scelta_sim`", "column36": " \'-\'", "column18": " `numero_sim`", "column12": " `descrizione_promo`" }, "fields": { "@timestamp": [ "2020-07-28T13:43:23.779Z" ] }, "highlight": { "message": [ "20200728 15:43:23;mbc-db-vodafone;root;192.168.2.165;464232271;9113436875;QUERY;vodafone\_en;'@kibana-highlighted-field@INSERT@/kibana-highlighted-field@ INTO `tb_promo_attivate` (`id_customer`; `id_phone_calls`; `id_promo`; `descrizione_promo`; `costo_promo`; `id_agent_crm`; `nome_agent`; `telefono_sconto`; `tipo_scelta_sim`; `numero_sim`; `iccid_sim`; `vecchio_gestore_sim`; `piano_telefonico_sim`; `data_inserimento`; `campagna`;`tipo`; `scelta_rete_sicura`)\n\t\tVALUES (\'13285100\'; \'0\'; \'13\'; \'FISSI E MOBILI NAZIONALI SENZA LIMITI\'; \'-\'; \'83\'; \'XXXXXXXXX\'; \'\'; \'-\'; \'\'; \'\'; \'-\'; \'-\'; CURRENT\_TIMESTAMP; \'Richiami su Numeri Uscenti XXXXXXX\';\'DSL\'; \'NO\')';0" ], "operation": [ "@kibana-highlighted-field@QUERY@/kibana-highlighted-field@" ], "object": [ "'@kibana-highlighted-field@INSERT@/kibana-highlighted-field@ INTO `tb_promo_attivate` (`id_customer`" ] }, "sort": [ 1595943803779 ] } ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [July 29, 2020, 5:37pm UTC](https://discuss.elastic.co/t/logstash-csv-filter-plugin-with-mariadb-audit-plugin-logs/242953/4 "2020-07-29T17:37:35Z")
If a field is surrounded by double quotes then it is allowed to contain the separator. So you would need your data to look like ```auto 20200728 15:43:23;mbc-db-vodafone;root;192.168.2.165;464232271;9113436875;QUERY;vodafone_en;"INSERT INTO tb_promo_attivate (id_customer; id_phone_calls; id_promo; descrizione_promo; costo_promo; id_agent_crm; nome_agent; telefono_sconto; tipo_scelta_sim; numero_sim; iccid_sim; vecchio_gestore_sim; piano_telefonico_sim; data_inserimento; campagna;tipo; scelta_rete_sicura)\n\t\tVALUES (\'13285100\'; \'0\'; \'13\'; \'FISSI E MOBILI NAZIONALI SENZA LIMITI\'; \'-\'; \'83\'; \'Ledda Marcellino\'; \'\'; \'-\'; \'\'; \'\'; \'-\'; \'-\'; CURRENT_TIMESTAMP; \'Richiami su Numeri Uscenti C2C 4u Italia\';\'DSL\'; \'NO\')";0 ``` with double quotes around the SQL. The following mutate will replace any single quote not preceded by a backslash with a double quote. That reduces the number of columns that the csv filter produces from 42 to 9. ``` mutate { gsub => ["message", "(? ";" } ``` ---
**Author:** ![fabrizio73](https://avatars.discourse-cdn.com/v4/letter/f/ecccb3/32.png) [@fabrizio73](https://discuss.elastic.co/u/fabrizio73)\ **Post date:** [July 29, 2020, 6:29pm UTC](https://discuss.elastic.co/t/logstash-csv-filter-plugin-with-mariadb-audit-plugin-logs/242953/5 "2020-07-29T18:29:43Z")
Great ! It works, I still get some parse error (warning) in the log stash log file, but my "json formatted" records are there, available also with kibana 🙂 here is late in Italy, tomorrow I will check better if these warnings are minor issues and close the post ! thank yoo so much! ---
**Author:** ![fabrizio73](https://avatars.discourse-cdn.com/v4/letter/f/ecccb3/32.png) [@fabrizio73](https://discuss.elastic.co/u/fabrizio73)\ **Post date:** [July 30, 2020, 11:04am UTC](https://discuss.elastic.co/t/logstash-csv-filter-plugin-with-mariadb-audit-plugin-logs/242953/6 "2020-07-30T11:04:20Z")
can you please explain me lucene syntax ? ok for ( .. ) which create a group, the ? means 0 or more , the ! preceding "\" means not a "Literal" "" , I don't understand the meaning of "\<" character, for what I've read, it's normally user for numeric ranges ( for example \<0-10\> ) , but without it's "closing" character "\>" I can't understand exactly the regexp, it works, but as I told yesterday I have some minor error in the logs, and I'm wondering if these can be related to this ! Thanks in advance ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [July 30, 2020, 5:29pm UTC](https://discuss.elastic.co/t/logstash-csv-filter-plugin-with-mariadb-audit-plugin-logs/242953/7 "2020-07-30T17:29:32Z")
It's nothing to do with lucene. It's a ruby regular expression. [This](https://www.regular-expressions.info/lookaround.html) article discusses negative lookbehind assertions. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [August 27, 2020, 5:29pm UTC](https://discuss.elastic.co/t/logstash-csv-filter-plugin-with-mariadb-audit-plugin-logs/242953/8 "2020-08-27T17:29:35Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.