Data is not proper post transformation

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

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?

@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

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

@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

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.

@Badger have tried giving a dummy value to the date field but still its failing :frowning:

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

If I remember correctly, elasticsearch does not accept that there is a year 0000.

@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 :frowning:

Thanks
Gauti

hey @Badger i even tried remove field also but no luck :frowning:

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

Insert a valid date. Year 0000 is not valid.

@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

Try doing the mutate+replace before the date filter.

@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}, #<LogStash::Event:0x28e30dfc>], :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

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.

@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

Can you try changing that to

if [result][closed_at]

@Badger this is working only in rubydebug once i try sending data to elasticsearch then back to same error.

Thanks
Gauti

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