CSV parsed data showing different date than received

[motts]

Hello,

I am looking at some data in Kibana. I am importing a CSV, but parsed data is showing a different date than the date in the message.

Input data

Data in the Date field

Here is my logstash filter conf:

csv {
                separator => " "
                columns => ["date", "time", "s-ip", "cs-method", "cs-uri-stem", "cs-uri-query", "s-port", "cs-username", "c-ip", "cs(User-Agent)", "sc-status", "sc-substatus", "sc-win32-status", "time-taken"]
        }

I am assuming there is some kind of issue with mapping maybe, but I am not quite sure how to check that. Also with this conf, the "date" and "time" field should be separated, which they are, but all the date fields are also showing the date (minus 1 date) and a time of 2000. The "time" field is correct though.

I am not sure where this problem should go. Please help when you can.

[jakelandis]

We would need to see an example of the CSV data and any other Logstash filters that may be manipulating the date or time. (and what you see vs. what you expect)

I doubt it is an issue with the mapping or Elaticsearch, and you may have better luck on the #logstash forum.

[motts]

Here is a sample log entry
2018-10-08 07:25:30 1.1.1.1 GET /assets/front/test.png - 443 - 1.1.1.2 Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/69.0.3497.100+Safari/537.36 304 0 0 82

Here's the Logstash conf file. This is the only filter on this server.

Input {
tcp {
type => "iis"
port => 5001
codec => "line"
}
filter {
if [type] =='iis' {

    csv {
            separator => " "
            columns => ["date", "time", "s-ip", "cs-method", "cs-uri-stem", "cs-uri-query", "s-port", "cs-username", "c-ip", "cs(User-Agent)", "sc-status", "sc-substatus", "sc-win32-status", "time-taken"]
    }

}
}
Output {
elasticsearch {

                    hosts => ["localhost:9200"]
                    index => "websense-%{+YYYY-MM-dd}"

}
}

From this configuration, my "date" field would show as the 7th instead of the 8th for some reason. All the "date" fields are 1 day behind the actual date in the log.

The log shipper doesn't alter the data in any way. It just ships the file. I can send other types of logs, like Windows Events, and the date field displays as it should.

[jakelandis]

I'm assuming this a timezone issue. Assuming that date is mapped as Date datatype then the issue is probably in the display. You can validate this by looking at the results directly from the Elasticsearch API (not Kibana)

By default Kibana uses your browser timezone to relative the timezone's to your own. I am not sure the exact rules when Kibana uses this, but I assume it is for any Date data mappings. If this is the case you may try to change your browser timezone (there are plugins that can do this) and if you admin the Kibana instance, there is an advanced option to hard code the timezone.

If you only care about the display of the date field, you can try to map it is a keyword (or add a keyword mapping) to ensure it does not change.

I also assume that the other date/times you see, such as the one in message are mapped as string or keywords and thus don't localize.

Lots of assumptions up there so let me know if that helps.

[motts]

I see where you are coming from with time zone reference, but these logs are actually from a time zone that -6 hours from mine. The logs were copied to a server here, so it should be set to local time. I send all other kinds of logs from that same server, even other IIS logs, but only these logs seem to have issues for some reason. I don't know if some of the other data in the log is causing Kibana to think the time is off or if a date-like field is standard set to central time?

I added this to my logstash config and it sort of helped. Now it shows the @timestamp field as the date in the date field, but the correct date lol really strange

date {
match => ["date", "yyyy-MM-dd HH:mm:ss"]
}

and this is now shown (this was imported on 10/26/18, but the timestamp is from the log creation date)