# Logstash CSV Timestamp extract

**URL:** <https://discuss.elastic.co/t/logstash-csv-timestamp-extract/292188>\
**Category:** Logstash\
**Created:** [December 16, 2021, 4:56pm UTC](https://discuss.elastic.co/t/logstash-csv-timestamp-extract/292188 "2021-12-16T16:56:59Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Robsen\_Inc](https://avatars.discourse-cdn.com/v4/letter/r/d26b3c/32.png) [@Robsen\_Inc](https://discuss.elastic.co/u/Robsen_Inc)\
**Post date:** [December 16, 2021, 4:56pm UTC](https://discuss.elastic.co/t/logstash-csv-timestamp-extract/292188/1 "2021-12-16T16:56:59Z")

</div>

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!

```auto
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"
  }  
}

```

---

<div class="post-metadata">

**Author:** ![yaauie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yaauie/32/23363_2.png) [@yaauie](https://discuss.elastic.co/u/yaauie)\
**Post date:** [December 16, 2021, 5:08pm UTC](https://discuss.elastic.co/t/logstash-csv-timestamp-extract/292188/2 "2021-12-16T17:08:16Z")

</div>

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:

```auto
  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"
  }

```

---

<div class="post-metadata">

**Author:** ![Robsen\_Inc](https://avatars.discourse-cdn.com/v4/letter/r/d26b3c/32.png) [@Robsen\_Inc](https://discuss.elastic.co/u/Robsen_Inc)\
**Post date:** [December 17, 2021, 8:03am UTC](https://discuss.elastic.co/t/logstash-csv-timestamp-extract/292188/3 "2021-12-17T08:03:23Z")

</div>

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".

> **[A hardware-in-the-loop water distribution testbed (WDT) dataset for...](https://ieee-dataport.org/open-access/hardware-loop-water-distribution-testbed-wdt-dataset-cyber-physical-security-testing)**
>
> This dataset supports researchers in the validation process of solutions such as Intrusion Detection Systems (IDS) based on artificial intelligence and machine learning techniques for the detection and categorization of threats in Cyber Physical...

---

<div class="post-metadata">

**Author:** ![Robsen\_Inc](https://avatars.discourse-cdn.com/v4/letter/r/d26b3c/32.png) [@Robsen\_Inc](https://discuss.elastic.co/u/Robsen_Inc)\
**Post date:** [December 17, 2021, 8:39am UTC](https://discuss.elastic.co/t/logstash-csv-timestamp-extract/292188/4 "2021-12-17T08:39:00Z")

</div>

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.

```auto
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"
  }
}

```

---

<div class="post-metadata">

**Author:** ![yaauie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yaauie/32/23363_2.png) [@yaauie](https://discuss.elastic.co/u/yaauie)\
**Post date:** [December 17, 2021, 7:37pm UTC](https://discuss.elastic.co/t/logstash-csv-timestamp-extract/292188/5 "2021-12-17T19:37:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Robsen\_Inc](https://avatars.discourse-cdn.com/v4/letter/r/d26b3c/32.png) [@Robsen\_Inc](https://discuss.elastic.co/u/Robsen_Inc)\
**Post date:** [December 20, 2021, 9:38am UTC](https://discuss.elastic.co/t/logstash-csv-timestamp-extract/292188/6 "2021-12-20T09:38:42Z")

</div>

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!

---

<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 17, 2022, 9:39am UTC](https://discuss.elastic.co/t/logstash-csv-timestamp-extract/292188/7 "2022-01-17T09:39:19Z")

</div>

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