Hi all, I am trying to import a CSV file which is an export from a Service Now ticketing system into ES using Logstash 6.6.2 - but it works if I use manually entered test data - but as soon as I use actual data it fails. I suspect this may be due to the strange characters included in the CSV data such as * / . : etc but am struggling to find a way of resolving. I would be happy to automatically remove all special characters from the problem fields as they are not required.
My conf file is below. any help appreciated.
input {
file {
id => "newtestv2"
path => "C:/elk6/logstash-6.6.2/bin/import/inc-*.csv"
type => "csv"
start_position => "beginning"
type => "csv"
mode => "read"
}
}
filter {
csv {
separator => ","
columns => [ "Number","Short description","Priority","State","Assignment Group","Assigned to","Closure Reason","Atos Primary Category","Comments and Work notes","Created","Description","Duration","Impact","Time worked","Updated","Made SLA","Closed","Additional comments","Created by","Escalation","Resolution Description","SLA due","Tags","Updated by","Updates","Work notes list","Work notes","Active","Activity due","Contact type","Reassignment count","Service offering","Resolution Code","Resolve time","Service Impact","Severity","Operating System","Incident Type","Environment","Closed Date","Closed by","Asset ID","Resolved by","Resolved","Team Name","On-Hold Reason","Longest Team Assignment","Incident State","Category","Incident Reassignment","Additional assignee list","Group list" ]
skip_header => "true"
convert => {
"Duration" => "integer"
"Time worked" => "integer"
"Longest Team Assignment" => "integer"
}
}
Date / time fields
@timestamp field
date {
match => [ "Created", "dd/MM/yyyy HH:mm" ]
timezone => "Europe/London"
}
date {
match => [ "Created", "dd/MM/yyyy HH:mm" ]
target => "Created"
timezone => "Europe/London"
}
if ![Closed] {
mutate {
update => { "Closed" => "1900-01-01T00:00:00Z" }
}
} else {
date {
match => [ "Closed", "dd/MM/yyyy HH:mm" ]
target => "Closed"
timezone => "Europe/London"
}
}
if ![Resolved] {
mutate {
update => { "Resolved" => "1900-01-01T00:00:00Z" }
}
} else {
date {
match => [ "Resolved", "dd/MM/yyyy HH:mm" ]
target => "Resolved"
timezone => "Europe/London"
}
}
if ![Updated] {
mutate {
update => { "Updated" => "1900-01-01T00:00:00Z" }
}
} else {
date {
match => [ "Updated", "dd/MM/yyyy HH:mm" ]
target => "Updated"
timezone => "Europe/London"
}
}
}
output {
elasticsearch {
hosts => ["http://localhost:9200"]
action => "index"
index => ["test"]
# Ensures updates overwrite existing rather than duplicating
document_id => "%{[Number]}"
}
stdout {
# To debug use rubydebug, or dots for limited output
codec => rubydebug
}
}
The logstash log output is below
[2019-03-19T16:38:19,152][WARN ][logstash.codecs.plain ] Received an event that has a different character encoding than you configured. {:text=>"IF FTF \x96 DELETE ALL QUESTIONS BELOW (ENSURE YOU ASSIGN ALL FTF TICKETS TO YOURSELF & INCLUDE RESOLUTION)", :expected_charset=>"UTF-8"}
[2019-03-19T16:38:19,158][WARN ][logstash.codecs.plain ] Received an event that has a different character encoding than you configured. {:text=>"if no \x96 ensure you obtain alternative contact/mobile", :expected_charset=>"UTF-8"}
[2019-03-19T16:38:19,585][WARN ][logstash.filters.csv ] Error parsing csv {:field=>"message", :source=>"INC001009178,Windows 10 - Access to eForms (not authorized),3 - Medium,Closed,Accenture.PortedApps.eForms,name,Resolved,Application,"2018-06-23 19:14:46 BST - (Additional comments)", :exception=>#<CSV::MalformedCSVError: Unclosed quoted field on line 1.>}
[2019-03-19T16:38:19,750][WARN ][logstash.filters.csv ] Error parsing csv {:field=>"message", :source=>"",14/06/2018 23:58,"1. Full description of incident and troubleshooting attempted: User and 15 colleague are all experiencing the same issue with being unable to use eForms. They all have access. ", :exception=>#<CSV::MalformedCSVError: Unclosed quoted field on line 1.>}
[2019-03-19T16:38:19,765][WARN ][logstash.filters.csv ] Error parsing csv {:field=>"message", :source=>"", :exception=>#<NoMethodError: undefined method `each_index' for nil:NilClass>}