CSV Output not what expected

Elastic Stack Logstash
1

Hello,

I have to parse an XML file and extract certain variables from it. The parsing part is solved. And from the XML filter I get three variables in this way:

"DX_METERNAME" => [
[0] "DX_KILOM",
[1] "DX_KILOM",
[2] "DX_KILOM"
],
"DX_NEWREADING" => [
[0] "26680",
[1] "27818",
[2] "22026"
],
"DX_EXTERNAL_REFERENCE" => [
[0] "CRA20-2",
[1] "CRA62",
[2] "CRA66"
]
Now I want to save them in a CSV File with, obsiously three rows (this number may vary depending on the information in the XML). What I want is something like:

DX_KILOM, 26680, CRA20-2
DX_KILOM, 27818, CRA62
DX_KILOM, 22026, CRA66

But the output in the file is like (in one single line):

"[""DX_KILOM"", ""DX_KILOM"", ""DX_KILOM""]","[""CRA20-2"", ""CRA62"", ""CRA66""]","[""26680"", ""27818"", ""22026""]"

I have also tried using the join filter, but the result is more or less the same (everything in the same line):
"DX_KILOM,DX_KILOM,DX_KILOM","CRA20-2,CRA62,CRA66","26680,27818,22026"

Here is my code for the output (LOGSTASH Version 7.1):

    output{
    stdout{}
    csv{  
       fields => ["DX_METERNAME", "DX_EXTERNAL_REFERENCE" , "DX_NEWREADING"]
      
      path => "/output_path/output.csv"
       csv_options => {
            "write_headers" => true
            "headers" =>["DX_METERNAME","DX_NEWREADING","DX_EXTERNAL_REFERENCE"]
            "col_sep" => ","        	
            }
    }
    }

Can anyone help me?
Thanks in advance.

2

I would do that using ruby.

    ruby {
        code => '
            meterName = event.get("DX_METERNAME")
            newReading = event.get("DX_NEWREADING")
            extRef = event.get("DX_EXTERNAL_REFERENCE")
            if meterName and newReading and extRef
                a = []
                meterName.each_index { |x|
                    h = {}
                    h["DX_METERNAME"] = meterName[x]
                    h["DX_NEWREADING"] = newReading[x].to_i
                    h["DX_EXTERNAL_REFERENCE"] = extRef[x]
                    a << h
                }
                event.set("arrayOfHashes", a)
            end
        '
        remove_field => [ "DX_METERNAME", "DX_NEWREADING", "DX_EXTERNAL_REFERENCE" ]
    }
    split { field => "arrayOfHashes" }
    ruby {
        code => '
            event.get("arrayOfHashes").each { |k, v|
                event.set(k, v)
            }
            event.remove("arrayOfHashes")
        '
    }
3

First of all, tanks for your quick response. The code works perfectly when there is no headers. But when I add the header option this is the output in the csv file:

DX_METERNAME,DX_EXTERNAL_REFERENCE,DX_NEWREADING
DX_KILOM,CRA20-2,26680
DX_METERNAME,DX_EXTERNAL_REFERENCE,DX_NEWREADING
DX_KILOM,CRA62,27818
DX_METERNAME,DX_EXTERNAL_REFERENCE,DX_NEWREADING
DX_KILOM,CRA66,22026

I really don't undersand why it inserts the header between every line.

4

It is just the way it works. Each event is output by a separate call to to_csv. If csv_options says to add a header then every call will add it and you will get one per event.

5

Thank you, very much. Very helpfull!

I am still thinking this CSV output feature should create a new row for every element in the arrays in "fields" section.

This could be a new feature.

6

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