# Data is not proper post transformation
**URL:** https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716
**Category:** Logstash
**Created:** [August 16, 2018, 12:28pm UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716 "2018-08-16T12:28:50Z")
**Posts on this page:** 19
**Page:** 1
### Author:  [@Gauti](https://discuss.elastic.co/u/Gauti)
#### Post date: [August 16, 2018, 12:28pm UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/1 "2018-08-16T12:28:50Z")
Hi All,
I'm pulling data from service now through logstash, i have converted few fields like created on, resolved at and closed at, to convert into date values using filter option.
Lets say there are a total of 100 ticekts in servicenow incidents, half of them doesnot have the value for resolved at and closed at because those tickets have not been worked on, and those data are not loaded into elasticsearch.
How do i get complete data into servicenow?
**This is my config file.**
input {
http\_poller {
urls =\> {
url =\> "[https://demo1.service-now.com/api/now/table/incident?sysparm\_display\_value=True&sysparm\_exclude\_reference\_link=True&sysparm\_fields=number%2Ccategory%2Cpriority%2Cstate%2Cassignment\_group%2Cassigned\_to%2Cchild\_incidents%2Cclose\_code%2Cclosed\_by%2Cclosed\_at%2Ccompany%2Ccmdb\_ci%2Ccontact\_type%2Csys\_created\_on%2Csys\_created\_by%2Cdescription%2Cescalation%2Cimpact%2Cknowledge%2Clocation%2Cproblem\_id%2Creassignment\_count%2Creopen\_count%2Cresolved\_at%2Cseverity%2Curgency%2Cu\_repeat\_incident](https://demo1.service-now.com/api/now/table/incident?sysparm_display_value=True&sysparm_exclude_reference_link=True&sysparm_fields=number%2Ccategory%2Cpriority%2Cstate%2Cassignment_group%2Cassigned_to%2Cchild_incidents%2Cclose_code%2Cclosed_by%2Cclosed_at%2Ccompany%2Ccmdb_ci%2Ccontact_type%2Csys_created_on%2Csys_created_by%2Cdescription%2Cescalation%2Cimpact%2Cknowledge%2Clocation%2Cproblem_id%2Creassignment_count%2Creopen_count%2Cresolved_at%2Cseverity%2Curgency%2Cu_repeat_incident)"
}
request\_timeout =\> 60
proxy =\> { host =\> "1.1.1.2" port =\> "9090" scheme =\> "http"}
user =\> "\*\*\*\*\*\*"
password =\> "\*\*\*\*\*_"
schedule =\> { cron =\> "_ \* \* \* \*"}
codec =\> "json"
metadata\_target =\> "http\_poller\_metadata"
}
}
filter
{
split
{
field =\> "result"
}
}
filter
{
mutate { convert =\> { "result.reassignment\_count" =\> "integer" } }
mutate { convert =\> { "result.reopen\_count" =\> "integer" } }
date { match =\> ["result.closed\_at", "YYYY-MM-dd HH:mm:ss"]
target =\> "result.closed\_at"
}
date { match =\> ["result.resolved\_at", "YYYY-MM-dd HH:mm:ss"]
target =\> "result.resolved\_at"
}
date { match =\> ["result.sys\_created\_on", "YYYY-MM-dd HH:mm:ss"]
target =\> "result.sys\_created\_on"
}
}
output {
elasticsearch {
hosts =\> ["1.1.1.6:9200"]
index =\> "servicenow"
}
}
Any advice on how to deal this situation?
Thanks
Gautham
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [August 16, 2018, 1:39pm UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/2 "2018-08-16T13:39:56Z")
You could make the parsing conditional on the value in the field. Are the fields present in the JSON? Are they an empty string? What does an event look like in the JSON tab of Kibana/Discover?
---
### Author:  [@Gauti](https://discuss.elastic.co/u/Gauti)
#### Post date: [August 16, 2018, 2:12pm UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/3 "2018-08-16T14:12:37Z")
@Badger its an empty field in servicenow, to check in kibana/Discover page those data are not indexed in elasticsearch, looks like logstash is sending only the field which has value.
Also i'm seeing errors in logstash logs file states it cannot index data, may be its not sending the empty field data into elasticsearch.
**Error for reference:**
[2018-08-16T15:38:01,581][WARN][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=\>400, :action=\>["index", {:\_id=\>nil, :\_index=\>"servicenow", :\_type=\>"doc", :\_routing=\>nil}, #LogStash::Event:0x1a65db1d], :response=\>{"index"=\>{"\_index"=\>"servicenow", "\_type"=\>"doc", "\_id"=\>"UFg0QmUB8Wi87zUazStO", "status"=\>400, "error"=\>{"type"=\>"mapper\_parsing\_exception", "reason"=\>"failed to parse [result.closed\_at]", "caused\_by"=\>{"type"=\>"illegal\_argument\_exception", "reason"=\>"Invalid format: """}}}}}
Thanks
Gautham
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [August 16, 2018, 2:54pm UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/4 "2018-08-16T14:54:49Z")
The problem is that elasticsearch has decided that result.closed\_at should be a date, and when it gets the string "" it cannot parse it as a date. I suggest you remove the field if it is empty. Something like
```
if [someField] == "" { mutate { remove_field => ["someField"] } }
```
The field is nested right? It does not have a dot in its name...
---
### Author:  [@Gauti](https://discuss.elastic.co/u/Gauti)
#### Post date: [August 16, 2018, 3:19pm UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/5 "2018-08-16T15:19:49Z")
@Badger if the field is removed, then there will be problem while creating the dashoards,
we are trying to show dashboards like how many incidents closed and how many are open and when it has been closed stuffs like that.
If the field is removed then i'll not be able to create the dashboard, is there a way where i can mention if the field is empty then leave the field as empty or update as NA or NIL?
Thanks
Gauti
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [August 16, 2018, 4:12pm UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/6 "2018-08-16T16:12:52Z")
If the field exists it has to be a date. You could use a special value for the date to indicate it has not been closed, like 1 Jan 1970.
---
### Author:  [@Gauti](https://discuss.elastic.co/u/Gauti)
#### Post date: [August 21, 2018, 10:09am UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/7 "2018-08-21T10:09:57Z")
@Badger have tried giving a dummy value to the date field but still its failing ☹
Here is my config file:
input {
http\_poller {
urls =\> {
url =\> "[https://demo1.service-now.com/api/now/table/incident?sysparm\_display\_value=True&sysparm\_exclude\_reference\_link=True&sysparm\_fields=number%2Ccategory%2Cpriority%2Cstate%2Cassignment\_group%2Cassigned\_to%2Cchild\_incidents%2Cclose\_code%2Cclosed\_by%2Cclosed\_at%2Ccompany%2Ccmdb\_ci%2Ccontact\_type%2Csys\_created\_on%2Csys\_created\_by%2Cdescription%2Cescalation%2Cimpact%2Cknowledge%2Clocation%2Cproblem\_id%2Creassignment\_count%2Creopen\_count%2Cresolved\_at%2Cseverity%2Curgency%2Cu\_repeat\_incident](https://demo1.service-now.com/api/now/table/incident?sysparm_display_value=True&sysparm_exclude_reference_link=True&sysparm_fields=number%2Ccategory%2Cpriority%2Cstate%2Cassignment_group%2Cassigned_to%2Cchild_incidents%2Cclose_code%2Cclosed_by%2Cclosed_at%2Ccompany%2Ccmdb_ci%2Ccontact_type%2Csys_created_on%2Csys_created_by%2Cdescription%2Cescalation%2Cimpact%2Cknowledge%2Clocation%2Cproblem_id%2Creassignment_count%2Creopen_count%2Cresolved_at%2Cseverity%2Curgency%2Cu_repeat_incident)"
}
request\_timeout =\> 60
proxy =\> { host =\> "1.1.1.2" port =\> "9292" scheme =\> "http"}
user =\> "_**"
password =\> "**_"
schedule =\> { cron =\> "\* \* \* \* \*"}
codec =\> "json"
metadata\_target =\> "http\_poller\_metadata"
}
}
filter
{
split
{
field =\> "result"
}
}
filter
{
if [result.closed\_at]== ""
{
mutate {
replace =\>["result.closed\_at","0000-00-00 00:00:00"]
}
}
else
{
date { match =\> ["result.closed\_at","YYYY-MM-dd HH:mm:ss"]} }
}
output {
elasticsearch {
hosts =\> ["1.1.1.3:9200"]
index =\> "servicenow"
}
}
The data is not getting indexed, any suggestions?
**This is the warning message i'm getting in log files:**
[2018-08-21T13:51:02,047][WARN][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=\>400, :action=\>["index", {:\_id=\>nil, :\_index=\>"servicenow", :\_type=\>"doc", :\_routing=\>nil}, #LogStash::Event:0x99f00bf], :response=\>{"index"=\>{"\_index"=\>"servicenow", "\_type"=\>"doc", "\_id"=\>"Rm6SW2UB8Wi87zUaomKQ", "status"=\>400, "error"=\>{"type"=\>"mapper\_parsing\_exception", "reason"=\>"failed to parse [result.closed\_at]", "caused\_by"=\>{"type"=\>"illegal\_argument\_exception", "reason"=\>"Invalid format: """}}}}}
Thanks
Gauti
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [August 21, 2018, 10:59am UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/8 "2018-08-21T10:59:25Z")
If I remember correctly, elasticsearch does not accept that there is a year 0000.
---
### Author:  [@Gauti](https://discuss.elastic.co/u/Gauti)
#### Post date: [August 21, 2018, 11:20am UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/9 "2018-08-21T11:20:13Z")
@Badger what if i use the replace plugin to replace the filed value to nil
filter
{
if [result.closed\_at]== ""
{
mutate {
replace =\> ["result.closed\_at","nil"]
}
}
else
{
date { match =\> ["result.closed\_at","YYYY-MM-dd HH:mm:ss"]} }
}
Is this a correct approach? will this work? actually i tried too it didnt work ☹
Thanks
Gauti
---
### Author:  [@Gauti](https://discuss.elastic.co/u/Gauti)
#### Post date: [August 21, 2018, 3:37pm UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/10 "2018-08-21T15:37:20Z")
hey @Badger i even tried remove field also but no luck ☹
Getting Same Error:
[2018-08-21T19:34:01,497][WARN][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=\>400, :action=\>["index", {:\_id=\>nil, :\_index=\>"nowtest1", :\_type=\>"doc", :\_routing=\>nil}, #LogStash::Event:0x4d4f953a], :response=\>{"index"=\>{"\_index"=\>"nowtest1", "\_type"=\>"doc", "\_id"=\>"iG\_MXGUB8Wi87zUaqVxt", "status"=\>400, "error"=\>{"type"=\>"mapper\_parsing\_exception", "reason"=\>"failed to parse [result.closed\_at]", "caused\_by"=\>{"type"=\>"illegal\_argument\_exception", "reason"=\>"Invalid format: """}}}}}
**My filter config now:**
```
if [result.closed_at] != "" {
date {
match => ["result.closed_at", "YYYY-MM-dd HH:mm:ss"]
target => "result.closed_at"
}
}
if [result.closed_at] == "" {
mutate{
remove_field => ["result.closed_at"]
}
}
```
Any Advice?
Thanks
Gauti
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [August 21, 2018, 6:10pm UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/11 "2018-08-21T18:10:33Z")
Insert a valid date. Year 0000 is not valid.
---
### Author:  [@Gauti](https://discuss.elastic.co/u/Gauti)
#### Post date: [August 23, 2018, 3:01pm UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/12 "2018-08-23T15:01:56Z")
@Badger tried with a value also still no luck
**here is the filter**
```
if [result.closed_at] != "" {
date {
match => ["result.closed_at", "YYYY-MM-dd HH:mm:ss"]
target => "result.closed_at"
}
}
if [result.closed_at] == "" {
mutate{
replace => {"result.closed_at" => "1970-01-01 12:12:12"}
}
}
```
**Same error:**
[2018-08-23T18:40:03,582][WARN][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=\>400, :action=\>["index", {:\_id=\>nil, :\_index=\>"snow5", :\_type=\>"doc", :\_routing=\>nil}, #LogStash::Event:0x3941827e], :response=\>{"index"=\>{"\_index"=\>"snow5", "\_type"=\>"doc", "\_id"=\>"\_UvnZmUBckKxGWef-KGi", "status"=\>400, "error"=\>{"type"=\>"mapper\_parsing\_exception", "reason"=\>"failed to parse [result.closed\_at]", "caused\_by"=\>{"type"=\>"illegal\_argument\_exception", "reason"=\>"Invalid format: """}}}}}
Thanks
Gauti
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [August 23, 2018, 3:57pm UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/13 "2018-08-23T15:57:09Z")
Try doing the mutate+replace before the date filter.
---
### Author:  [@Gauti](https://discuss.elastic.co/u/Gauti)
#### Post date: [August 25, 2018, 1:37pm UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/14 "2018-08-25T13:37:30Z")
@Badger still the same error.
**Error:**
`[2018-08-25T16:56:01,848][WARN][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"snow5", :_type=>"doc", :_routing=>nil}, #], :response=>{"index"=>{"_index"=>"snow5", "_type"=>"doc", "_id"=>"HlPVcGUBckKxGWefcqWr", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse [result.closed_at]", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"Invalid format: \"\""}}}}}`
**Changes in my configfile**
```
if [result.closed_at] == "" {
mutate{
replace => {"result.closed_at" => "1970-01-01 12:12:12"}
}
}
if [result.closed_at] != "" {
date {
match => ["result.closed_at", "YYYY-MM-dd HH:mm:ss"]
target => "result.closed_at"
}
}
```
What will be the mistake i'm doing, have been struggling for weeks together to get this resolved.
Thanks
Gauti
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [August 25, 2018, 2:05pm UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/15 "2018-08-25T14:05:53Z")
Can you show us what an event looks like using
```
output { stdout { codec => rubydebug } }
```
The error message '"Invalid format: """' suggests that the field has the value "".
Also, field names with . in the name are not supported. They mostly work, but odd things here and there break. You might want to look at the de\_dot filter, possibly with the nested option set.
---
### Author:  [@Gauti](https://discuss.elastic.co/u/Gauti)
#### Post date: [August 25, 2018, 2:49pm UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/16 "2018-08-25T14:49:40Z")
@Badger i'm not getting any error or warning while running rubydebug
I think the filter is not working, bcoz in the output i can see the documents with date field where ever date is available and for empty fields i'm getting document as same empty.
**sample of field containing both values:**
**closed\_at** is my field
{
"@timestamp" =\> 2018-08-25T12:47:03.425Z,
"result" =\> {
"knowledge" =\> "false",
"contact\_type" =\> "Chat Bot",
"assignment\_group" =\> "Network",
"reopen\_count" =\> "",
"escalation" =\> "Normal",
**"closed\_at" =\> "",**
"close\_code" =\> nil,
"description" =\> "User can't get to any of his files on the file server.",
"sys\_created\_on" =\> "2014-02-08 14:30:06",
"impact" =\> "1 - High",
"company" =\> "",
"resolved\_at" =\> "",
"category" =\> "Network",
"cmdb\_ci" =\> "FileServerFloor2",
"closed\_by" =\> "",
"state" =\> "Awaiting Problem",
"sys\_created\_by" =\> "pat",
"priority" =\> "1 - Critical",
"assigned\_to" =\> "Howard Johnson",
"location" =\> "Salem OR",
"child\_incidents" =\> "",
"problem\_id" =\> "PRB0000007",
"severity" =\> "1 - High",
"number" =\> "INC0000002",
"urgency" =\> "1 - High",
"reassignment\_count" =\> "1",
"u\_repeat\_incident" =\> "false"
},
This is another document which had date field value
{
"@timestamp" =\> 2018-08-25T12:47:03.425Z,
"result" =\> {
"knowledge" =\> "false",
"contact\_type" =\> nil,
"assignment\_group" =\> "Service Desk",
"reopen\_count" =\> "",
"escalation" =\> "Moderate",
**"closed\_at" =\> "2015-07-29 17:40:24"** ,
"close\_code" =\> "Solved (Work Around)",
"description" =\> "User forgot their email password.",
"sys\_created\_on" =\> "2014-02-19 14:34:12",
"impact" =\> "1 - High",
"company" =\> "",
"resolved\_at" =\> "2015-10-21 13:16:24",
"category" =\> "Request",
"cmdb\_ci" =\> "",
"closed\_by" =\> "Don Goodliffe",
"state" =\> "Closed",
"sys\_created\_by" =\> "pat",
"priority" =\> "1 - Critical",
"assigned\_to" =\> "Bud Richman",
"location" =\> "",
"child\_incidents" =\> "",
"problem\_id" =\> "",
"severity" =\> "1 - High",
"number" =\> "INC0000004",
"urgency" =\> "1 - High",
"reassignment\_count" =\> "1",
"u\_repeat\_incident" =\> "false"
},
Thanks
Gauti
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [August 25, 2018, 3:51pm UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/17 "2018-08-25T15:51:19Z")
> [@Gauti](#):
>
> if [result.closed\_at]
Can you try changing that to
```
if [result][closed_at]
```
---
### Author:  [@Gauti](https://discuss.elastic.co/u/Gauti)
#### Post date: [August 27, 2018, 10:02am UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/18 "2018-08-27T10:02:35Z")
@Badger this is working only in rubydebug once i try sending data to elasticsearch then back to same error.
Thanks
Gauti
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [September 24, 2018, 10:06am UTC](https://discuss.elastic.co/t/data-is-not-proper-post-transformation/144716/19 "2018-09-24T10:06:01Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.