# Add a new filed in csv based on a value in colum and cal the time diff
**URL:** https://discuss.elastic.co/t/add-a-new-filed-in-csv-based-on-a-value-in-colum-and-cal-the-time-diff/196117
**Category:** Logstash
**Created:** [August 21, 2019, 1:06pm UTC](https://discuss.elastic.co/t/add-a-new-filed-in-csv-based-on-a-value-in-colum-and-cal-the-time-diff/196117 "2019-08-21T13:06:46Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@kolli\_dilip](https://discuss.elastic.co/u/kolli_dilip)
#### Post date: [August 21, 2019, 1:06pm UTC](https://discuss.elastic.co/t/add-a-new-filed-in-csv-based-on-a-value-in-colum-and-cal-the-time-diff/196117/1 "2019-08-21T13:06:46Z")
Hi all
I have a set of csv data as below
i am interested in adding a new field 'status' based on the 'endtime' column value, i.e if its not 'null' then the status should be completed else open.
also need to calculate the timedifference between 'endtime' and 'createtime' if the endtime value is not 'null' and then add a new field 'timetaken'
can any one help me on this
Thanks
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [August 21, 2019, 1:26pm UTC](https://discuss.elastic.co/t/add-a-new-filed-in-csv-based-on-a-value-in-colum-and-cal-the-time-diff/196117/2 "2019-08-21T13:26:40Z")
Take at look at [this](https://discuss.elastic.co/t/problem-with-combining-xmlfilter-and-ruby-code-to-compute-a-time-difference/195924/2) post for calculating the difference. You can set the status based on whether you get a \_dateparsefailure on the endtime.
---
### Author:  [@kolli\_dilip](https://discuss.elastic.co/u/kolli_dilip)
#### Post date: [August 21, 2019, 5:56pm UTC](https://discuss.elastic.co/t/add-a-new-filed-in-csv-based-on-a-value-in-colum-and-cal-the-time-diff/196117/3 "2019-08-21T17:56:31Z")
Hi @Badger
i had the below config, its resulting only the "open' stats
filter
{
csv
{
separator =\> ","
columns =\> ["product","createtime","endtime"]
skip\_header =\> true
}
date { match =\> ["createtime", "dd/MM/yyyy HH:mm"] target =\> "@timestamp"}
```
date { match => ["createtime", "ISO8601"] target => "createtime" }
date { match => ["endtime", "ISO8601"] target => "endtime" }
ruby {
code => '
duration = 0.0
ended = event.get("endtime")
created = event.get("createtime")
if created and ended then
duration = ended.to_f - created.to_f
end
event.set("req_duration_sec", duration)
'
}
if (("_dateparsefailure" in [tags]))
{
mutate
{
add_field => { "Status" => "open" }
}
}
else
{
mutate
{
add_field => { "Status" => "closed" }
}
}
```
}
can u suggest if there are any mistakes
Thanks
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [August 21, 2019, 6:29pm UTC](https://discuss.elastic.co/t/add-a-new-filed-in-csv-based-on-a-value-in-colum-and-cal-the-time-diff/196117/4 "2019-08-21T18:29:37Z")
> [@kolli\_dilip](#):
>
> ```
> date { match => ["createtime", "dd/MM/yyyy HH:mm"] target => "@timestamp"}
> date { match => ["createtime", "ISO8601"] target => "createtime" }
> date { match => ["endtime", "ISO8601"] target => "endtime" }
>
> ```
Change the pattern in the last two to match the first. Your timestamps are not ISO8601.
---
### Author:  [@kolli\_dilip](https://discuss.elastic.co/u/kolli_dilip)
#### Post date: [August 22, 2019, 9:11am UTC](https://discuss.elastic.co/t/add-a-new-filed-in-csv-based-on-a-value-in-colum-and-cal-the-time-diff/196117/5 "2019-08-22T09:11:32Z")
I tried the changes, but still i'm not able to index the fileds with null value as endtime..
the output section is as below
output {
elasticsearch {
hosts =\> ["localhost:9200"]
document\_id =\> "%{productname}-%{pid}"
index =\> "records"
}
}
[2019-08-22T10:01:42,774][WARN][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=\>400, :action=\>["index", {:\_id=\>"Express product-pid", :\_index=\>"records", :\_type=\>"doc", :\_routing=\>nil}, #LogStash::Event:0x2cdf70c4], :response=\>{"index"=\>{"\_index"=\>"records", "\_type"=\>"doc", "\_id"=\>"Express product-pid", "status"=\>400, "error"=\>{"type"=\>"mapper\_parsing\_exception", "reason"=\>"failed to parse [endtime]", "caused\_by"=\>{"type"=\>"illegal\_argument\_exception", "reason"=\>"Invalid format: "NULL""}}}}}
from the "reason"=\>"Invalid format: "NULL"" , its surely because of null value, but i need this to be indexed to the same index "records"
can you please validate
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [August 22, 2019, 12:22pm UTC](https://discuss.elastic.co/t/add-a-new-filed-in-csv-based-on-a-value-in-colum-and-cal-the-time-diff/196117/6 "2019-08-22T12:22:08Z")
> [@kolli\_dilip](#):
>
> "type"=\>"mapper\_parsing\_exception", "reason"=\>"failed to parse [endtime]", "caused\_by"=\>{"type"=\>"illegal\_argument\_exception", "reason"=\>"Invalid format: "NULL""}
elasticsearch has learned, from the documents where endtime is a timestamp, that endtime should be a timestamp. "NULL" is not a valid timestamp.
I would configure logstash to delete [endtime] if it equal to the string "NULL".
```
if [endtime] == "NULL" {
mutate { remove_field => ["endtime"] }
}
```
---
### Author:  [@kolli\_dilip](https://discuss.elastic.co/u/kolli_dilip)
#### Post date: [August 22, 2019, 3:12pm UTC](https://discuss.elastic.co/t/add-a-new-filed-in-csv-based-on-a-value-in-colum-and-cal-the-time-diff/196117/7 "2019-08-22T15:12:17Z")
thank you @Badger
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [October 11, 2019, 11:07am UTC](https://discuss.elastic.co/t/add-a-new-filed-in-csv-based-on-a-value-in-colum-and-cal-the-time-diff/196117/9 "2019-10-11T11:07:17Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.