# Filter a CSV with a CSV

**URL:** <https://discuss.elastic.co/t/filter-a-csv-with-a-csv/228091>\
**Category:** Logstash\
**Created:** [April 15, 2020, 10:54am UTC](https://discuss.elastic.co/t/filter-a-csv-with-a-csv/228091 "2020-04-15T10:54:54Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![bazza](https://avatars.discourse-cdn.com/v4/letter/b/54ee81/32.png) [@bazza](https://discuss.elastic.co/u/bazza)\
**Post date:** [April 15, 2020, 10:54am UTC](https://discuss.elastic.co/t/filter-a-csv-with-a-csv/228091/1 "2020-04-15T10:54:55Z")

</div>

Hi,  
I have a log entry from mysql\_audit.so that creates a CSV based log file.  
I have been able to filter this in to ELK no problems, but I have one type of log entry that contains comma delimited entries within the column.

an example would be:  
`<field1>, <field2>, \'<field3, field3.1, field3.2, field3.3, field3.4>\', <field4>`

The CSV filter doesn't recognize the escaped characters but does recognize the commas within the escaped field.  
This number of entries for is variable I have seen field3.1 - 3.6.

I can create a separate sub filter for this entry type based on fields within the entry. But currently cannot find a way to join all of the 3.x fields together into a single column.

I have tried using add\_field, but any extra columns become a string in the log entry and I would like to keep in its own column.

My filter defines the name for each column and when the filter hits this entry type I end up with system defined columns ie "column4 column5 .... " as I end up with an overflow of column names.

I suspect that I may have to use the ruby filter to sort this out, only issue is my ruby.foo is not strong.  
Unfortunately this system is air gapped from the internet and I have to manually type any data across.

Just wondering if the brains trust, may be able to help out in solving this problem.

---

<div class="post-metadata">

**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:** [April 15, 2020, 2:38pm UTC](https://discuss.elastic.co/t/filter-a-csv-with-a-csv/228091/2 "2020-04-15T14:38:59Z")

</div>

You could try

```
mutate { gsub => ["message", ", ", ",", "message", "\\'", '"'] }

```

Once the entire field is double quoted the csv filter should handle it.

---

<div class="post-metadata">

**Author:** ![bazza](https://avatars.discourse-cdn.com/v4/letter/b/54ee81/32.png) [@bazza](https://discuss.elastic.co/u/bazza)\
**Post date:** [April 15, 2020, 9:07pm UTC](https://discuss.elastic.co/t/filter-a-csv-with-a-csv/228091/3 "2020-04-15T21:07:05Z")

</div>

Thanks for that, will give it a try today and let you know.  
i take it this works by  
`["message",` is the whole entry  
`,"` is for each column into the entry  
` ",",` not quite sure what this part does but will test  
`"message", "\\'", '"' ]` magic happens here, it does the find and replace.

---

<div class="post-metadata">

**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:** [April 15, 2020, 9:14pm UTC](https://discuss.elastic.co/t/filter-a-csv-with-a-csv/228091/4 "2020-04-15T21:14:33Z")

</div>

The entire field has to be in double quotes, so you need to remove the spaces after the commas. That is what the first gsub is doing.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [May 13, 2020, 9:14pm UTC](https://discuss.elastic.co/t/filter-a-csv-with-a-csv/228091/5 "2020-05-13T21:14:41Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.