Logstash CSV Timestamp extract

Elastic Stack Logstash
1

Hello everyone,
I have a problem with my log data. After hours of try-and-error I am now looking for help from you.

An exemplary line looks like this:

09/04/2021 11:30:53 0 0 0 0 0 0 0 0 false false false false false false 0 0 0 0 false false false false false false false false false false false false false false false false false false false false false false 0 normal

And my pipeline so is shown below.
I can't get it to convert the "Time" field from "text" to "date".

I hope you can help me.

Thanks and bye!

input{
  file {
     path => "/logs/logdata"
     start_position => "beginning" 
  }
}
 
filter {
  csv {
    separator => "	"
    skip_header => "true"
    columns => ["Time", "Tank_1", "Tank_2", "Tank_3", "Tank_4", "Tank_5", "Tank_6", "Tank_7","Tank_8", "Pump_1","Pump_2", "Pump_3","Pump_4","Pump_5","Pump_6","Flow_sensor_1", "Flow_sensor_2", "Flow_sensor_3","Flow_sensor_4","Valv_1","Valv_2","Valv_3","Valv_4","Valv_5","Valv_6","Valv_7", "Valv_8", "Valv_9", "Valv_10", "Valv_11", "Valv_12", "Valv_13", "Valv_14", "Valv_15", "Valv_16", "Valv_17", "Valv_18", "Valv_19", "Valv_20","Valv_21", "Valv_22", "Label_n", "Label"]
  }

  
  mutate {
    convert => {
        "Tank_1" => "integer"
    }
  }

  date {
    match => [ "Time", "dd/MM/yyyy HH:mm:ss" ]
    target => "date_format"
  }
 
}

output {
  elasticsearch {
    hosts => "http://xxx:9200"
    index => "xxx"
    user => "xxx"
    password => "xxx"
  }  
}
2

Since your timestamp has a space in it, and your CSV filter is using a space as a delimiter, the timestamp ends up being split into two fields. If we add a Date the field to the list, we can add in a mutate filter that composes them into a single field (I picked a metadata field so that the end-result wouldn't include it), and then use the value of that field in our date filter:

  csv {
    separator => "	"
    skip_header => "true"
    columns => ["Date", "Time", "Tank_1", "Tank_2", "Tank_3", "Tank_4", "Tank_5", "Tank_6", "Tank_7","Tank_8", "Pump_1","Pump_2", "Pump_3","Pump_4","Pump_5","Pump_6","Flow_sensor_1", "Flow_sensor_2", "Flow_sensor_3","Flow_sensor_4","Valv_1","Valv_2","Valv_3","Valv_4","Valv_5","Valv_6","Valv_7", "Valv_8", "Valv_9", "Valv_10", "Valv_11", "Valv_12", "Valv_13", "Valv_14", "Valv_15", "Valv_16", "Valv_17", "Valv_18", "Valv_19", "Valv_20","Valv_21", "Valv_22", "Label_n", "Label"]
  }
  mutate {
    add_field {
      "[@metadata][composed_timestamp]" => "%{Date} %{Time}"
    }
  }
  date {
    match => [ "[@metadata][composed_timestamp]", "dd/MM/yyyy HH:mm:ss" ]
    target => "date_format"
  }
1 Like
3

Hi and thanks for your quick feedback! (I was unfortunately not so fast)

That is a good idea. Actually the separator should be a "TAB". (Hard to distinguish)

\t dont work so i have to use "TAB"

Kibana also shows me the whole timestamp.

I'm not getting anywhere right now, also I tried to eleminate the "TAB" with an upstream "gsub" but to no avail.

If someone wants to try the whole, the data is "Opendata".

4

I followed your idea again further to have no more "TAB" problems.

I have actually used the " " as a separator and then also connected the fields accordingly. In addition, when connecting also waived " ". But it does not work. It is to cry.

filter {
  csv {
    separator => " "
    skip_header => "true"
    columns => ["Date", "Time", "Tank_1", "Tank_2", "Tank_3", "Tank_4", "Tank_5", "Tank_6", "Tank_7","Tank_8", "Pump_1","Pump_2", "Pump_3","Pump_4","Pump_5","Pump_6","Flow_sensor_1", "Flow_sensor_2", "Flow_sensor_3","Flow_sensor_4","Valv_1","Valv_2","Valv_3","Valv_4","Valv_5","Valv_6","Valv_7", "Valv_8", "Valv_9", "Valv_10", "Valv_11", "Valv_12", "Valv_13", "Valv_14", "Valv_15", "Valv_16", "Valv_17", "Valv_18", "Valv_19", "Valv_20","Valv_21", "Valv_22", "Label_n", "Label"]
  }

  mutate {
    add_field => {"composed_timestamp" => "%{Date}:%{Time}"}
    
    convert => {
        "Tank_1" => "integer"
    }
  }
  
  date {
    match => [ "composed_timestamp", "dd/MM/yyyy:HH:mm:ss" ]
    target => "date_format"
  }
}
5

On the records that have a _dateparsefailure tag, what is the value of their composed_timestamp field?

6

Hi,
sorry for the late feedback.

The problem was actually quite different.
It was because the CSV file was not parsed as expected.
Preprocessing the record to UTF-8 fixed the problem.

Thanks for your efforts!

7

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