# Kindly suggest a filter strategy for "csv in csv" data

**URL:** <https://discuss.elastic.co/t/kindly-suggest-a-filter-strategy-for-csv-in-csv-data/69833>\
**Category:** Logstash\
**Created:** [December 22, 2016, 6:04pm UTC](https://discuss.elastic.co/t/kindly-suggest-a-filter-strategy-for-csv-in-csv-data/69833 "2016-12-22T18:04:46Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![cprior](https://avatars.discourse-cdn.com/v4/letter/c/a9a28c/32.png) [@cprior](https://discuss.elastic.co/u/cprior)\
**Post date:** [December 22, 2016, 6:04pm UTC](https://discuss.elastic.co/t/kindly-suggest-a-filter-strategy-for-csv-in-csv-data/69833/1 "2016-12-22T18:04:46Z")

</div>

Dear all,

I believe I have read all current filter docs, but still I'd like to get input from more knowledgeable folks: I want to parse with logstash curency counter information, structured like:

The input is a text file, shipped in via filebeat:  
2016-12-22 18:59|wallet|USD:100:8,USD:200:4,USD:500:0,USD:1000:1,USD:2000:6,USD:5000:1,USD:10000:3  
2016-12-22 18:59|piggybank|USD:100:47,USD:200:1,USD:500:0,USD:1000:8,USD:2000:15,USD:5000:0,USD:10000:1

My loglines may contain all ISO currency codes.  
Depending on the currency there may be varying repetitions.

The output should enable me to generate stacked bar charts over time, either in Kibana or maybe even with Jupyter Notebooks utilizing matplotlib.

I will import my own data and maybe my wive's -- but I will have an identifier field to distinguish.

After reading the docs I am tempted to use a **ruby filter**.  
Is that the best fitting strategy?

(I will be perfectly able to dabble with the code myself, but rather want an educated hint if that is the right direction.)

---

<div class="post-metadata">

**Author:** ![cprior](https://avatars.discourse-cdn.com/v4/letter/c/a9a28c/32.png) [@cprior](https://discuss.elastic.co/u/cprior)\
**Post date:** [December 22, 2016, 8:44pm UTC](https://discuss.elastic.co/t/kindly-suggest-a-filter-strategy-for-csv-in-csv-data/69833/2 "2016-12-22T20:44:49Z")

</div>

I am not familiar with ruby, but as my data looks quite recursive I found sample code for recursive "split" at [http://billpatrianakos.me/blog/2015/05/31/turn-a-string-into-a-hash-with-string-dot-to-hash-in-ruby/](http://billpatrianakos.me/blog/2015/05/31/turn-a-string-into-a-hash-with-string-dot-to-hash-in-ruby/)

```
line1 = 'EUR:500:1,EUR:1000:2,EUR:2000:3,EUR:5000:4,EUR:10000:5,EUR:20000:6,EUR:50000:7'
class String
  def to_hash(arr_sep=',', key_sep=':')
    array = self.split(arr_sep)
    currency = {}
    denomination = {}

    array.each do |e|
      key_value = e.split(key_sep)
      denomination[key_value[1]] = key_value[2]
      currency[key_value[0]] = denomination
    end

    return currency
  end
end
line1.to_hash

```

produces  
=\> {"EUR"=\>{"500"=\>"1", "1000"=\>"2", "2000"=\>"3", "5000"=\>"4", "10000"=\>"5", "20000"=\>"6", "50000"=\>"7"}}

Obviously that is a bit heavy to put it into each filters per message, and the 'denomination' array should maybe just become a tag or field.

But before I am tempted to write a filter plugin I'd still like to hear other opinions.

---

<div class="post-metadata">

**Author:** ![cprior](https://avatars.discourse-cdn.com/v4/letter/c/a9a28c/32.png) [@cprior](https://discuss.elastic.co/u/cprior)\
**Post date:** [December 23, 2016, 1:07pm UTC](https://discuss.elastic.co/t/kindly-suggest-a-filter-strategy-for-csv-in-csv-data/69833/3 "2016-12-23T13:07:35Z")

</div>

It seems as if this codes does give me "grapheable" data:

```
if [loganstate] == "counters_total" {
    ruby{
        init => "
        "
        code => '
            array = event.get("counters_total").split(",")
            array.each do |e|
                key_value = e.split(":")
                event.set("currency", key_value[0])
                event.set(key_value[0] + "_" + key_value[1], key_value[2].to_i)
            end
        '
    }
}

```

At least I could then select ~7 fields for a stacked bar graph. _hooray_

---

<div class="post-metadata">

**Author:** ![jsvd](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsvd/32/6203_2.png) [@jsvd](https://discuss.elastic.co/u/jsvd)\
**Post date:** [December 23, 2016, 5:14pm UTC](https://discuss.elastic.co/t/kindly-suggest-a-filter-strategy-for-csv-in-csv-data/69833/4 "2016-12-23T17:14:41Z")

</div>

@cprior you approach is sane, I only suggest a slight tweak to the ruby code:

```ruby
currency, value, quantity = e.split(":")
event.set("currency", currency)
event.set("#{currency}_#{value}", quantity.to_i)

```

---

<div class="post-metadata">

**Author:** ![cprior](https://avatars.discourse-cdn.com/v4/letter/c/a9a28c/32.png) [@cprior](https://discuss.elastic.co/u/cprior)\
**Post date:** [December 23, 2016, 7:39pm UTC](https://discuss.elastic.co/t/kindly-suggest-a-filter-strategy-for-csv-in-csv-data/69833/5 "2016-12-23T19:39:04Z")

</div>

Ah, that is a much cleaner syntax indeed!

These # are [http://ruby-doc.org/docs/ruby-doc-bundle/Manual/man-1.4/syntax.html#string](http://ruby-doc.org/docs/ruby-doc-bundle/Manual/man-1.4/syntax.html#string) I just learned -- good to know, concatting with + never feels right.

Many thanks for your opinion!

By the way, when I figured out to use .to\_i I was mightily impressed that it ended up in "visualization" as a selectable filed in the drop down list because it turned into a number in elasticsearch.  
(I deleted the index after each test, so I also got a fresh "manage index" on every test run.)

Another gotcha was the class variables with @@, explained in [http://www.railstips.org/blog/archives/2006/11/18/class-and-instance-variables-in-ruby/](http://www.railstips.org/blog/archives/2006/11/18/class-and-instance-variables-in-ruby/)  
I use that in the init part to keep track if I am in a multi-currency logfile or not.

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [December 25, 2016, 3:24am UTC](https://discuss.elastic.co/t/kindly-suggest-a-filter-strategy-for-csv-in-csv-data/69833/6 "2016-12-25T03:24:19Z")

</div>

Why not just do two csv filters, then a kv?

---

<div class="post-metadata">

**Author:** ![cprior](https://avatars.discourse-cdn.com/v4/letter/c/a9a28c/32.png) [@cprior](https://discuss.elastic.co/u/cprior)\
**Post date:** [December 25, 2016, 6:10am UTC](https://discuss.elastic.co/t/kindly-suggest-a-filter-strategy-for-csv-in-csv-data/69833/7 "2016-12-25T06:10:27Z")

</div>

I skipped that filter because the smallest units are not pairs but a triple. [https://github.com/logstash-plugins/logstash-filter-kv/blob/master/lib/logstash/filters/kv.rb#L350](https://github.com/logstash-plugins/logstash-filter-kv/blob/master/lib/logstash/filters/kv.rb#L350) returns that single value, it seemed to me, although a bit (for me) funky ruby goes on with this key, v1, v2, ... v6 above.

---

<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:** [January 22, 2017, 6:10am UTC](https://discuss.elastic.co/t/kindly-suggest-a-filter-strategy-for-csv-in-csv-data/69833/8 "2017-01-22T06:10:44Z")

</div>

Closed on January 22, 2017, 6:10am UTC

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